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

Case Statement Subquery return more than 1 row

Shubhamjain27
Regular Contributor
Regular Contributor

Hi,

I am working on a case statement, below is the query:

Select Case when (select count(jobcode) from users where systemusername = 'dunderwoo' and jobcode in (select distinct attribute1 from dataset_values where datasetname = 'VP_SVPJobCodes')) > 0 then (select displayname as ID from users where systemusername = 'dunderwoo') else (Select distinct a.displayname as ID from dataset_values dv left join users a on dv.attribute1=a.jobcode and dv.datasetname = 'VP_SVPJobCodes' and a.displayname is not null) end as ID

It gives subquery return more than 1 row

Shubhamjain27_0-1718955406478.png

If I run the first part of the query, it works good:

Shubhamjain27_1-1718955469862.png

The issue is with the second query:

Select Case when 1=1 then (Select distinct a.displayname as ID from dataset_values dv left join users a on dv.attribute1=a.jobcode and dv.datasetname = 'VP_SVPJobCodes' and a.displayname is not null) end as ID

Shubhamjain27_2-1718955616712.png

And if I run the last part of the query, standalone it works good:

Shubhamjain27_3-1718955658816.png

 

 

 

6 REPLIES 6

pmahalle
All-Star
All-Star

@Shubhamjain27  Your subquery "Select distinct a.displayname as ID from dataset_values dv left join users a on dv.attribute1=a.jobcode and dv.datasetname = 'VP_SVPJobCodes' and a.displayname is not null" should return single row since you are using the result of the query as single column in select statement.


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂

Yeah with case statement, it should return only 1 value.

Any alternate to the query I am using I can use in dynamic attribute to multiple?

@Shubhamjain27 What's the actual requirement?


Pandharinath Mahalle(Paddy)
If this reply helps your question, please consider selecting Accept As Solution and hit Kudos 🙂

Shubhamjain27
Regular Contributor
Regular Contributor

Usecase is to auto populate VP_SVP(DA)  if the manager(DA) selected is having the VP_SVP job code. If doesnt match, show all the all the VP_SVP with the job codes matching in the dataset.

I got it working with having Union for the query returning multiple rows. - Select distinct a.displayname as ID from dataset_values dv left join users a on dv.attribute1=a.jobcode and dv.datasetname = 'VP_SVPJobCodes' and a.displayname is not null

In the DA values I added the query which will return all the SVP_VP based on the Job codes

In Default values I added the query which will return only one value. - select m.displayname as ID from users u, users m where u.manager=m.userkey and u.systemusername = ${manager} and m.jobcode in (select distinct attribute1 from dataset_values where datasetname = 'VP_SVPJobCodes')

 

 

NM
Valued Contributor
Valued Contributor

Hi @Shubhamjain27 , is the issue resolved??

Shubhamjain27
Regular Contributor
Regular Contributor

Yes it is resolved.