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

Please bear in mind that I don't have your tables to test with. Also, I'm uncertain which engine you are using. You have labeled you query for SQL Server, but there is no IFNULL function in SQL Server. (The name is ISNULL.)

With those disclaimers, here is query you can try:

   ; WITH CTE AS (  
      SELECT COUNT (DISTINCT WN.APPKEY) AS appkeycnt  
      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  
   ), CTE2 AS (  
      SELECT MAX(appkeycnt) AS maxcnt FROM CTE  
   )  
   SELECT CASE WHEN maxcnt IS NULL THEN NULL  
               WHEN maxcnt = 1     THEN 1  
               ELSE                     0  
          END  
   FROM CTE2  

Answer 3

Hi，@Vamshi

Welcome to Microsoft T-SQL Q&A Forum!

First of all, your function is not used correctly . When you want to determine whether a field exists , when the database retrieves null , it will of course return 0 because of your operation . The ifnull function only takes effect in mysql . We do not get your simple data . Maybe you You can try this：

 SELECT coalesce( (SELECT MAX(COUNT) AS COUNT   
 FROM (  
      SELECT count=case when COUNT (DISTINCT WN.APPKEY) >=1 then 1   
                        when COUNT (DISTINCT WN.APPKEY) is null then null   
						else -1 end   
 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 ) X ), 0) AS COUNT  

If this doesn't work, please tell me what the system reports when WN.APPKEY is empty.

Best regards,
Bert Zhou

Answer 4

@Erland Sommarskog

I agree with your point. I missed to include the sample table scripts earlier. Sorry about that. I need help with SQL query. Please refer the attached .txt file in the previous post for the scenarios.

Below is the sample create and insert scripts:

--create script table 1

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

--insert script table 1

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

--create script table 2

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

--insert script table 2

INSERT INTO [dbo].VW_NUMBER VALUES ('1234','GNW0060077','1','1')
GO
INSERT INTO [dbo].VW_NUMBER VALUES ('1234','0456618','2','1')
GO
INSERT INTO [dbo].VW_NUMBER VALUES ('9012','NGL0300008','3','1')
GO
INSERT INTO [dbo].VW_NUMBER VALUES ('9012','235493','4','1')
GO
INSERT INTO [dbo].VW_NUMBER VALUES ('7890','NGL0300008','3','1')
GO
INSERT INTO [dbo].VW_NUMBER VALUES ('7890','235493','4','1')
GO

Answer 5

@Erland Sommarskog
Thank you for your questions. Below is my response.

-->For scenario two, you have two conflicting ANUMBERS and the expected result is 0. I assume that "conflicting" here means that they have different values for APPKEY?

Response:

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

-->For Scenario five looks very similar to me, but you have also thrown in an ANUMBER is not in VWNUMBER at all. And for some reason I don't get, the expected result is 1. Could you explain this a little further?

Response:

Intention is if one or more than one conflicting ANUMBER is among the list of ANUMBERS in the query then skip only those conflicting ANUMBERS.

If there is one or more than one ANUMBER which is not in VWNUMBER is also among the list of ANUMBERS in the query then skip those ANUMBERS as well.

So the remaining is only valid ANUMBER that is why expected result should be 1.

-->for the combination of valid and conflicting ANUMBERS, the expected result is 1.

Response:

Valid ANUMBER means it exists in the VWNUMBER and is not conflicting ANUMBER.

if one or more than one conflicting ANUMBER is among the list of ANUMBERS in the query then skip only those conflicting ANUMBERS. The remaining will be the valid ANUMBER That is why the expected result is 1.

Please let me know if any questions.