Sql query needs to handle both NULL and INTEGER value.

Question

Hi Team, I have a query which returns COUNT.

Need help and inputs to correct the query

• per the Scenario 2, suppose if ALL the ANumbers provided in the query (i.e. when i say ALL ANumbers, it can be one or more than one provided in the query ) DOES NOT exist in the database then i want the final result to be returned as NULL instead of 0.
• per the Scenario 1, i want the final result to be returned 0.

Scenario 1:

    SELECT IFNULL ( (SELECT MAX(COUNT) AS COUNT FROM (SELECT COUNT (DISTINCT WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('MLSR12677','S448904')         
        AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')   
   GROUP BY WN.DEPTKEY, WN.ANUMBER HAVING COUNT(DISTINCT WN.APPKEY) = 1) X ), 0) AS COUNT  

The ANumbers provided in the query (scenario 1) exists in the database but due to the condition HAVING COUNT(DISTINCT WN.APPKEY) = 1 these ANumbers Count value does not qualify per the application requirement. So the result returned from the database is NULL but since i used IFNULL condition the final result is returned as 0.

Scenario 2:

   SELECT IFNULL ( (SELECT MAX(COUNT) AS COUNT FROM (SELECT COUNT (DISTINCT WN.APPKEY) AS COUNT FROM DEV.DBO.VW_NUMBER WN WHERE WN.ANUMBER IN ('A75DFWQ','TY68900')         
        AND WN.UNITKEY IN (SELECT IC.UNITKEY FROM DEV.DBO.VW_DATA IC WHERE IC.UNITNAME = 'MAS')   
   GROUP BY WN.DEPTKEY, WN.ANUMBER HAVING COUNT(DISTINCT WN.APPKEY) = 1) X ), 0) AS COUNT   


   The ANumbers provided in the query (scenario 2) DOES NOT exist in the database.  The result returned from the database is NULL but since i used  IFNULL condition the final result is returned as  0.   


     

Thank you.

Answer 1

I'm probably still missing something, but my interpretation is that the rules are:

1) No number exist in VWNUMBERS => return NULL.
2) At least one number exist in the table, but it is Conflicting => return 0.
3) At least one number exist in the table and is Valid => return 1.

This leads to small modification of my query above, replace MAX with MIN:

   CREATE OR ALTER PROCEDURE Vamshi_sp @list nvarchar(MAX) AS  
       ; WITH CTE AS (  
          SELECT COUNT (DISTINCT WN.APPKEY) AS appkeycnt  
          FROM   dbo.VW_NUMBER WN   
          WHERE  WN.ANUMBER IN (SELECT value FROM string_split(@list, ','))  
            AND  WN.UNITKEY IN (SELECT IC.UNITKEY FROM dbo.VW_DATA IC WHERE IC.UNITNAME = 'MAS')   
          GROUP BY  WN.DEPTKEY, WN.ANUMBER  
       ), CTE2 AS (  
          SELECT MIN(appkeycnt) AS mincnt FROM CTE  
       )  
       SELECT CASE WHEN mincnt IS NULL THEN NULL  
                   WHEN mincnt = 1     THEN 1  
                   ELSE                     0  
              END, mincnt  
       FROM CTE2  
   go  
   EXEC Vamshi_sp 'GNW0060077,0456618'  
   EXEC Vamshi_sp 'NGL0300008,235493'  
   EXEC Vamshi_sp 'UY1233,VAMPQ34'  
   EXEC Vamshi_sp 'GNW0060077,0456618,NGL0300008,235493,UY1233,VAMPQ34'  
   EXEC Vamshi_sp 'GNW0060077,UY1233,NGL0300008'  

(I packaged this in a stored procedure to make it easier to run all test cases. Just extract the query and replace the call to string_split with your list of values.)

Answer 2

@Erland Sommarskog

Thank you for pointing the mistake in my sample data in VW_DATA. I have corrected it. Please delete VW_DATA and recreate it.

--create script

CREATE TABLE [dbo].VW_DATA ON [PRIMARY]
GO

--insert script

INSERT INTO [dbo].VW_DATAVALUES('1234','APP1','1','DEPT1','1','MAS')
GO
INSERT INTO [dbo].VW_DATAVALUES('1234','APP1','2','DEPT2','1','MAS')
GO
INSERT INTO [dbo].VW_DATAVALUES('5678','APP2','2','DEPT2','1','MAS')
GO
INSERT INTO [dbo].VW_DATAVALUES('9012','APP3','3','DEPT3','1','MAS')
GO
INSERT INTO [dbo].VW_DATAVALUES('9012','APP3','4','DEPT4','1','MAS')
GO
INSERT INTO [dbo].VW_DATAVALUES('3456','APP4','4','DEPT4','1','MAS')
GO
INSERT INTO [dbo].VW_DATAVALUES('7890','APP5','3','DEPT3','1','MAS')
GO
INSERT INTO [dbo].VW_DATAVALUES('7890','APP5','4','DEPT4','1','MAS')
GO
INSERT INTO [dbo].[VW_DATA] ([APPKEY],[APPNAME],[DEPTKEY],[DEPTNAME],[UNITKEY],[UNITNAME])VALUES('4334','APP6','6','DEPT6','1','MAS')
GO

-->conflicting ANUMBER meaning the “the same Dept and ANumber combination” are matched with other APPKEY in the same Unit.

Response:

Example:

a) APPKEYs 9012 and 7890 has the same Dept and ANumber combination (i.e. 3 and NGL0300008 ) in the same Unit is 1 which is MAS.
b) APPKEYs 9012 and 7890 has the same Dept and ANumber combination (i.e. 4 and 235493 ) in the same Unit is 1 which is MAS.

-->Non conflicting ANUMBER

Example:

a) APPKEY 1234 has the Dept and ANumber combination (i.e. 1 and GNW0060077 ) which is not matched with other APPKEY in the same Unit which is MAS

b) APPKEY 1234 has the Dept and ANumber combination (i.e. 2 and 0456618 ) which is not matched with other APPKEY in the same Unit which is MAS.

please let me know if any questions.