IF-ELSE not allowed in Data Analyser

[tanvi_gaikwad]

Hello, 

We have a usecase where we need to execute 2 queries in a dynamic attribute with conditional logic for loggedInUser attributes.

IF (loggedInUser's employeeType = 'Internal')

(select query)

ELSE

(select query)

Currently IF-ELSE is throwing 'Operation not allowed' on Data Analyzer. Could you please give access to use transaction based SQL statements in our Saviynt instance. Please suggest resolutions.

[AmitM]

Hi @tanvi_gaikwad ,

Try this :

SELECT (CASE WHEN a.employeetype = 'Internal'
THEN (SELECT userkey from users where userkey=a.userkey)


ELSE (
SELECT username from users where userkey=a.userkey
)
END) from (select userkey,employeetype from users where userkey=${requestor}) a

This is just a sample which you can use to your needs. I have tested it and it works in data analyzer, after replacing requestor to hard coded value for data analyzer.

Br - Amit

If helped, Please ACCEPT SOLUTION.

[tanvi_gaikwad]

Hello Amit,

The select queries which we want to use return more than 1 values. CASE functionality does not allow more than 1 rows returned by a subquery. We are looking at something like this.

IF (loggedInUser's employeeType = 'Internal')

(select select attribute1 as ID from dataset_values where dataset_values.datasetname ='DATSETNAME')

ELSE

(select customproperty5 as id from users where userkey=$loggedInUser.id union select customproperty10 as id from users where userkey=$loggedInUser.id)

[rushikeshvartak]

Please share full query

[tanvi_gaikwad]

This is the query we are trying-

select CASE WHEN 'Internal User'!=(select employeeType from users where userkey=$loggedInUser.userkey) THEN (select customproperty5 as id from users where userkey=$loggedInUser.userkey UNION select customproperty10 as id from users where userkey=$loggedInUser.userkey) ELSE (select attribute1 as ID from dataset_values where dataset_values.datasetname ='HONDA_CENTER_MAPPING') END AS ID

The subqueries return more than 1 row. Therefore we were trying to handle it using IF-ELSE. But IF-ELSE operation is not allowed on Data Analyzer

[rushikeshvartak]

SELECT
CASE
WHEN 'Internal User' != (SELECT employeeType FROM users WHERE userkey = $loggedInUser.userkey)
THEN (SELECT customproperty5 AS id FROM users WHERE userkey = $loggedInUser.userkey
UNION
SELECT customproperty10 AS id FROM users WHERE userkey = $loggedInUser.userkey)
ELSE (SELECT attribute1 AS ID FROM dataset_values WHERE dataset_values.datasetname = 'HONDA_CENTER_MAPPING')
END AS ID;

update actual users userkey

[tanvi_gaikwad]

SELECT
CASE
WHEN 'Internal User' != (SELECT employeeType FROM users WHERE userkey = 9)
THEN (SELECT customproperty5 AS id FROM users WHERE userkey = 9
UNION
SELECT customproperty10 AS id FROM users WHERE userkey = 9)
ELSE (SELECT attribute1 AS ID FROM dataset_values WHERE dataset_values.datasetname = 'HONDA_CENTER_MAPPING')
END AS ID;

[rushikeshvartak]

Query works for me

[tanvi_gaikwad]

How many entries does your dataset have?