Create User form - Attribute SQL Query

[Flohy]

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,

[rushikeshvartak]

• Share logs and data analyzer screenshot

[Flohy]

Hi,

Screenshots and logs are attached here. 

Best regards,

[Flohy]

Hi,

I was able to find a solution to my SQL query problem.

Best regards,

[Dave]

@Flohy - To help other users, what was the solution to your problem? 

[Flohy · Accepted solution]

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,