Case Statement Subquery return more than 1 row

[Shubhamjain27]

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

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

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

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

[pmahalle]

@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.

[Shubhamjain27]

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?

[pmahalle]

@Shubhamjain27 What's the actual requirement?

[Shubhamjain27 · Accepted solution]

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]

Hi @Shubhamjain27 , is the issue resolved??

[Shubhamjain27]

Yes it is resolved.