Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
07/25/2024 02:54 AM
Hello,
In the create user form, I would like to set up an attribute containing the following SQL query :
select CASE when ${type}='Guest' then (select owner as ID from users where username = (select username as ID from users where userkey = ${requestor})) when ${type}='DEV' then (select distinct username as ID, concat(firstname,' ',lastname) as inlinedescription from users where statuskey=1 and (employeetype='Collab. VA nominatif' or employeetype='Prestataire externe')) when {type}='Prestataire externe' then (select distinct username as ID, concat(firstname,' ',lastname) as inlinedescription from users where statuskey=1 and (employeetype='Collab. VA nominatif')) end as ID
However, the request does not seem to be working as expected. When type is equal to 'Guest' in the form, the query works correctly. But, when type is equal to 'DEV' or 'Prestataire externe', the query returns nothing. Moreover, in each case, the SQL query works correctly via the Data Analyzer.
Thanks,
Solved! Go to Solution.
07/25/2024 05:50 AM
07/25/2024 06:15 AM
07/25/2024 08:59 AM
Hi,
I was able to find a solution to my SQL query problem.
Best regards,
07/25/2024 09:36 AM
@Flohy - To help other users, what was the solution to your problem?
07/26/2024 12:33 AM
Hello,
The solution was to separate the queries so that they could be combined using conditional unions.
SELECT ID, inlinedescription FROM ( SELECT owner AS ID, NULL AS inlinedescription FROM users WHERE ${type} = 'Guest' AND username = (SELECT username FROM users WHERE userkey = ${requestor}) UNION ALL SELECT username AS ID, CONCAT(firstname, ' ', lastname) AS inlinedescription FROM users WHERE ${type} = 'DEV' AND statuskey = 1 AND (employeetype = 'Collab. VA nominatif' OR employeetype = 'Prestataire externe') UNION ALL SELECT username AS ID, CONCAT(firstname, ' ', lastname) AS inlinedescription FROM users WHERE ${type} = 'Prestataire externe' AND statuskey = 1 AND employeetype = 'Collab. VA nominatif' ) AS combined WHERE (${type} = 'Guest' AND inlinedescription IS NULL) OR (${type} = 'DEV' AND inlinedescription IS NOT NULL) OR (${type} = 'Prestataire externe' AND inlinedescription IS NOT NULL);
Best regards,