Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

SQL in comma separated enum list

afauquem
New Contributor III
New Contributor III

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 :

afauquem_1-1713001544137.png

 

 

Thanks

 

3 REPLIES 3

Raghu
Valued Contributor III
Valued Contributor III

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%'


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

afauquem
New Contributor III
New Contributor III

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

afauquem
New Contributor III
New Contributor III

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 !