Hi Support team, I need help in correcting my mistake in the query.
Scenario 1:
The below query is working as expected based on the provided ANUMBER MLSR12677 and it is returning COUNT as 1 which is right result. Please refer the attached screenshot.
SELECT IFNULL((SELECT DISTINCT COUNT(WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN
WHERE WN.ANUMBER IN ('MLSR12677')
AND WN.UNITKEY IN (SELECT DISTINCT IC.UNITKEY FROM DEV.DBO.VW_DATA IC
WHERE IC.UNITNAME = 'MAS')
GROUP BY WN.ANUMBER, WN.DEPTKEY), 0) AS COUNT
Scenario 2:
The below query is working as expected based on the provided ANUMBER S448904 and it is returning COUNT as 2 which is right result. Please refer the attached screenshot.
SELECT IFNULL((SELECT DISTINCT COUNT(WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN
WHERE WN.ANUMBER IN ('S448904')
AND WN.UNITKEY IN (SELECT DISTINCT IC.UNITKEY FROM DEV.DBO.VW_DATA IC
WHERE IC.UNITNAME = 'MAS')
GROUP BY WN.ANUMBER, WN.DEPTKEY), 0) AS COUNT
Issue in the Scenario 3:
When i provide both ANUMBERS in the below query, i see a error message returned 'Single-row subquery returns more than one row.' Please refer the attached screenshot.
I WANT the result to be returned as COUNT as 2. Please provide inputs to solve it. Thank you.
SELECT IFNULL((SELECT DISTINCT COUNT(WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN
WHERE WN.ANUMBER IN ('MLSR12677','S448904')
AND WN.UNITKEY IN (SELECT DISTINCT IC.UNITKEY FROM DEV.DBO.VW_DATA IC
WHERE IC.UNITNAME = 'MAS')
GROUP BY WN.ANUMBER, WN.DEPTKEY), 0) AS COUNT