Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/13/2024 02:49 AM
Hi,
I'm using an Enum dynamic attribute so it stores value in comma separated list like 'AV,EP,DR'
I need to check if a value is inside this list like 'AV' is in 'AV,EP,DR' ?
I try some sql code in data analyzer but it doesn't work, some functions are not authorized like STRING_SPLIT or STRING_AGG
So, how can i do that ?
SQL Query :
SELECT a.customproperty12 as VALUE,(SELECT attribute1 AS VALUE FROM dataset_values WHERE datasetname="XXX" and attribute2 in (a.customproperty12)) AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=XX
Result :
Thanks
Solved! Go to Solution.
04/13/2024 06:30 AM
you can use it like commnd right
SELECT a.customproperty12 as VALUE,(SELECT attribute1 AS VALUE FROM dataset_values WHERE datasetname="XXX" and attribute2 in (a.customproperty12)) AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=XX and a.customproperty12 in like '%AV%'
04/13/2024 07:37 AM
I try this using '%' :
SELECT a.customproperty12 as VALUE,STUFF((SELECT ';'+attribute1 AS VALUE FROM dataset_values WHERE datasetname="XXX" and a.customproperty12 like concat('%',attribute2,'%')),1,1,'') AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=27 and u.userkey=1000
However the STUFF function is not allowed, as the STRING_AGG function
Any Idea ? I tried these functions because without I have this error "Subquery returns more than one row"
Thanks
04/13/2024 07:43 AM
I found a solution :
SELECT a.customproperty12 as VALUE,(SELECT GROUP_CONCAT(attribute1) AS VALUE FROM dataset_values WHERE datasetname="XXX" and a.customproperty12 like concat('%',attribute2,'%')) AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=27
Thanks !