Null must not display in result ,Display Null as 0.

Analyst_SQL 3,551 Reputation points
2021-01-21T06:51:23.69+00:00

When I insert in table #DispatchSM Column Del value 1,then it display Null,i want Display 0 on Null.

 Create Table #itemmasterfile(CodeItem int,ALID varchar(50),Descriptionitem varchar(50),Packsize varchar(50))   
  CREATE TABLE #Probale (BID INT,CodeItem int,Weigth int,prdqty int,EntryDate date,DelID int)   
  Create table #DispatchSM (SMID int,date date,Del int)  
  Create Table #Dispatch_SD(ID int,codeitem int,SMID int,BID int,qty int ,Weight int ,Delidd int)  
         
  INSERT INTO #itemmasterfile VALUES(1,'AS','Adult Sweater','Small')   
  INSERT INTO #itemmasterfile VALUES(2,'HV','HAEAVY SS','Small')    
  INSERT INTO #itemmasterfile VALUES(3,'LMB','LADIES MIX BLOUSES','Small')   
  INSERT INTO #itemmasterfile VALUES(4,'LP','LAPU','Small')    
  INSERT INTO #itemmasterfile VALUES(5,'LT','LAPU TROUSER','Small')    
  INSERT INTO #itemmasterfile VALUES(6,'L2','LL #2','Small')    
  INSERT INTO #itemmasterfile VALUES(7,'NS','N4S MXT','Small')    
  INSERT INTO #itemmasterfile VALUES(8,'OC','Over Coat','Small')    
  INSERT INTO #itemmasterfile VALUES(9,'SS','ST MIX T SHIRTS','Small')   
  INSERT INTO #itemmasterfile VALUES(10,'WW','W / WIPER ','Small')    
  INSERT INTO #itemmasterfile VALUES(11,'WP','WHITE PANTS','Small')    
          
      
   INSERT INTO #Probale VALUES(10006,4,270,1,'2020-10-20',null)    
  INSERT INTO #Probale VALUES(10007,5,270,1,'2020-10-20',null)    
  INSERT INTO #Probale VALUES(10008,6,270,1,'2020-10-20',null)    
          
           
  INSERT INTO #Probale VALUES(10000,1,270,1,'2020-10-21',null)   
  INSERT INTO #Probale VALUES(10001,2,270,1,'2020-10-21',null)    
  INSERT INTO #Probale VALUES(10002,3,270,1,'2020-10-21',null)   
  INSERT INTO #Probale VALUES(10003,4,270,1,'2020-10-21',null)    
  INSERT INTO #Probale VALUES(10004,5,270,1,'2020-10-21',null)    
  INSERT INTO #Probale VALUES(10005,6,270,1,'2020-10-21',null)  
         
      
  Insert into #DispatchSM values(1001,'2020-10-20',null);  
  insert into #Dispatch_SD values(11,4,1001,10008,1,270,null)  
  insert into #Dispatch_SD values(12,5,1001,10006,1,270,null);  
      
      
      
          
  Insert into #DispatchSM values(1002,'2020-10-21',1);  
  insert into #Dispatch_SD values(11,4,1002,10003,1,270,null)  
  insert into #Dispatch_SD values(12,5,1002,10004,1,270,null);  
    ;with cte1 as  
             (SELECT    i.CodeItem, i.Descriptionitem as Artical,i.ALID as Short,Isnull(Sum(D.qty),0) OUT_QTY,Isnull(Sum(D.Weight),0) OUT_weight  
            FROM         #Dispatch_SD D  
            inner  join #DispatchSM M on M.SMID=D.SMID  
            right  join #itemmasterfile i on i.CodeItem=D.codeitem  
                 
           and  M.date   between '2020-10-21' and   '2020-10-21'  
                                                
         where D.Delidd is null and M.Del is null  
            group by i.Descriptionitem,i.ALID,i.CodeItem)  
              
            ,cte2 as     
           (SELECT    Distinct i.CodeItem, i.Descriptionitem AS Artical,i.ALID as Short , ISNull(SUM(P.Weigth),0) AS IN_Weight, ISNull(SUM(P.prdqty),0)   
                               IN_QTY  
            FROM         #itemmasterfile i LEFT outer JOIN  
                                  #Probale P ON i.CodeItem = P.CodeItem   
                                  and  P.EntryDate between  '2020-10-21' and   '2020-10-21'  
                                                
                      
                                  where   (i.Packsize = 'Small') and delID is null  
            GROUP BY i.CodeItem,i.Descriptionitem, P.prdqty,i.ALID)   
            ,cte3 as  
             (select f.Code,f.Name,f.Short_Name,(f.Probale_QTY-f.Dispatch_QTY) as [Balance],(f.Probale_weight-f.Dispatch_Weight) as [W_Balance] from (  
            select e.Code,e.Name,e.Short_Name,isnull(min(e.[Bigbale_QTY]),0) as [Probale_QTY],isnull(min(e.[Probale_Weight]),0) as [Probale_weight],  
            isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Weight),0) as [Dispatch_Weight] from (  
            select a.Descriptionitem as Name,a.ALID as Short_Name, (a.CodeItem) as Code,isnull(sum(P.prdqty),0) as [Bigbale_QTY],  
            isnull(sum(P.Weigth),0) as [Probale_Weight]  
            from #itemmasterfile a  
                          
            left join #Probale P on a.CodeItem=P.CodeItem  
              
              where  
              a.Packsize ='Small' and P.delID is null    
              group by a.Descriptionitem,a.ALID,a.CodeItem) e  
            left join #Dispatch_SD c on e.Code=c.CodeItem   
          and c.Delidd is null   
            left join #DispatchSM M on M.SMID=c.SMID  
                           
             
                     
            group by e.Name ,e.Short_Name,e.Code )  
         f)  
         , cte12 as  
             (SELECT    i.CodeItem, i.Descriptionitem as Artical,i.ALID as Short,Isnull(Sum(D.qty),0) OUT_QTY,Isnull(Sum(D.Weight),0) OUT_weight  
            FROM         #Dispatch_SD D  
            inner  join #DispatchSM M on M.SMID=D.SMID  
            right  join #itemmasterfile i on i.CodeItem=D.codeitem  
                 
           and  M.date <'2020-10-21'  
                                                
         where D.Delidd is null and M.Del is null  
            group by i.Descriptionitem,i.ALID,i.CodeItem)  
              
            ,cte22 as     
           (SELECT    Distinct i.CodeItem, i.Descriptionitem AS Artical,i.ALID as Short , ISNull(SUM(P.Weigth),0) AS IN_Weight, ISNull(SUM(P.prdqty),0)   
                               IN_QTY  
            FROM         #ItemMasterFile i LEFT outer JOIN  
                                 #Probale P ON i.CodeItem = P.CodeItem   
                                  and  P.EntryDate <'2020-10-21'  
                                                
                      
                                  where   (i.Packsize = 'Small') and delID is null  
            GROUP BY i.CodeItem,i.Descriptionitem, P.prdqty,i.ALID)   
     ,cte32 as(select c22.CodeItem,(c22.IN_QTY-c12.OUT_QTY )Opening_Qty,  
      (c22.IN_Weight-c12.OUT_weight )Opening_Balance  
           from cte12 c12  
        right join cte22 c22 on c12.CodeItem=c22.CodeItem)  
             
  
             select c2.CodeItem,c2.Artical,c2.Short,c32.Opening_Qty,c32.Opening_Balance,c2.IN_QTY ,c2.IN_Weight, c1.OUT_QTY,c1.OUT_weight,c3.Balance,c3.W_Balance   
           from cte1 c1  
         
        right join cte2 c2 on c1.CodeItem=c2.CodeItem  
        right join cte3 c3 on c2.CodeItem=c3.Code  
        right join cte32 c32 on c2.CodeItem=c32.CodeItem  

58985-null.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,021 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,657 questions
0 comments No comments
{count} votes

Accepted answer
  1. EchoLiu-MSFT 14,581 Reputation points
    2021-01-21T08:38:14.593+00:00

    Hi @Analyst_SQL ,

    I modified the last select statement,please refer to:

         ;with cte1 as  
                  (SELECT    i.CodeItem, i.Descriptionitem as Artical,i.ALID as Short,Isnull(Sum(D.qty),0) OUT_QTY,Isnull(Sum(D.Weight),0) OUT_weight  
                 FROM         #Dispatch_SD D  
                 inner  join #DispatchSM M on M.SMID=D.SMID  
                 right  join #itemmasterfile i on i.CodeItem=D.codeitem  
                         
                and  M.date   between '2020-10-21' and   '2020-10-21'  
                                                        
              where D.Delidd is null and M.Del is null  
                 group by i.Descriptionitem,i.ALID,i.CodeItem)  
                      
                 ,cte2 as     
                (SELECT    Distinct i.CodeItem, i.Descriptionitem AS Artical,i.ALID as Short , ISNull(SUM(P.Weigth),0) AS IN_Weight, ISNull(SUM(P.prdqty),0)   
                                    IN_QTY  
                 FROM         #itemmasterfile i LEFT outer JOIN  
                                       #Probale P ON i.CodeItem = P.CodeItem   
                                       and  P.EntryDate between  '2020-10-21' and   '2020-10-21'  
                                                        
                              
                                       where   (i.Packsize = 'Small') and delID is null  
                 GROUP BY i.CodeItem,i.Descriptionitem, P.prdqty,i.ALID)   
                 ,cte3 as  
                  (select f.Code,f.Name,f.Short_Name,(f.Probale_QTY-f.Dispatch_QTY) as [Balance],(f.Probale_weight-f.Dispatch_Weight) as [W_Balance] from (  
                 select e.Code,e.Name,e.Short_Name,isnull(min(e.[Bigbale_QTY]),0) as [Probale_QTY],isnull(min(e.[Probale_Weight]),0) as [Probale_weight],  
                 isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Weight),0) as [Dispatch_Weight] from (  
                 select a.Descriptionitem as Name,a.ALID as Short_Name, (a.CodeItem) as Code,isnull(sum(P.prdqty),0) as [Bigbale_QTY],  
                 isnull(sum(P.Weigth),0) as [Probale_Weight]  
                 from #itemmasterfile a  
                                  
                 left join #Probale P on a.CodeItem=P.CodeItem  
                      
                   where  
                   a.Packsize ='Small' and P.delID is null    
                   group by a.Descriptionitem,a.ALID,a.CodeItem) e  
                 left join #Dispatch_SD c on e.Code=c.CodeItem   
               and c.Delidd is null   
                 left join #DispatchSM M on M.SMID=c.SMID  
                                   
                     
                             
                 group by e.Name ,e.Short_Name,e.Code )  
              f)  
              , cte12 as  
                  (SELECT    i.CodeItem, i.Descriptionitem as Artical,i.ALID as Short,Isnull(Sum(D.qty),0) OUT_QTY,Isnull(Sum(D.Weight),0) OUT_weight  
                 FROM         #Dispatch_SD D  
                 inner  join #DispatchSM M on M.SMID=D.SMID  
                 right  join #itemmasterfile i on i.CodeItem=D.codeitem  
                         
                and  M.date <'2020-10-21'  
                                                        
              where D.Delidd is null and M.Del is null  
                 group by i.Descriptionitem,i.ALID,i.CodeItem)  
                      
                 ,cte22 as     
                (SELECT    Distinct i.CodeItem, i.Descriptionitem AS Artical,i.ALID as Short , ISNull(SUM(P.Weigth),0) AS IN_Weight, ISNull(SUM(P.prdqty),0)   
                                    IN_QTY  
                 FROM         #ItemMasterFile i LEFT outer JOIN  
                                      #Probale P ON i.CodeItem = P.CodeItem   
                                       and  P.EntryDate <'2020-10-21'  
                                                        
                              
                                       where   (i.Packsize = 'Small') and delID is null  
                 GROUP BY i.CodeItem,i.Descriptionitem, P.prdqty,i.ALID)   
          ,cte32 as(select c22.CodeItem,(c22.IN_QTY-c12.OUT_QTY )Opening_Qty,  
           (c22.IN_Weight-c12.OUT_weight )Opening_Balance  
                from cte12 c12  
             right join cte22 c22 on c12.CodeItem=c22.CodeItem)  
                     
          
                  select c2.CodeItem,c2.Artical,c2.Short,c32.Opening_Qty,c32.Opening_Balance,c2.IN_QTY ,c2.IN_Weight, IIF(c1.OUT_QTY IS NULL,0,c1.OUT_QTY) OUT_QTY,  
    			  IIF(c1.OUT_weight IS NULL,0,c1.OUT_weight) OUT_weight  
    			  ,c3.Balance,c3.W_Balance   
                from cte1 c1  
                 
             right join cte2 c2 on c1.CodeItem=c2.CodeItem  
             right join cte3 c3 on c2.CodeItem=c3.Code  
             right join cte32 c32 on c2.CodeItem=c32.CodeItem  
    

    Modified part:

                  select c2.CodeItem,c2.Artical,c2.Short,c32.Opening_Qty,c32.Opening_Balance,c2.IN_QTY ,c2.IN_Weight, IIF(c1.OUT_QTY IS NULL,0,c1.OUT_QTY) OUT_QTY,  
    			  IIF(c1.OUT_weight IS NULL,0,c1.OUT_weight) OUT_weight  
    			  ,c3.Balance,c3.W_Balance   
                from cte1 c1  
                 
             right join cte2 c2 on c1.CodeItem=c2.CodeItem  
             right join cte3 c3 on c2.CodeItem=c3.Code  
             right join cte32 c32 on c2.CodeItem=c32.CodeItem  
    

    Output:
    58978-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Olaf Helper 45,121 Reputation points
    2021-01-21T08:22:53.897+00:00

    then it display Null,i want Display 0 on Null.

    For this you can use the ISNULL function, like

    select ISNULL(null, 0) as result  
    
    1 person found this answer helpful.

  2. EchoLiu-MSFT 14,581 Reputation points
    2021-01-21T07:31:28.23+00:00
       Insert into #DispatchSM values(1001,'2020-10-20',0);  
    

    58908-image.png

    Do you want this result or all nulls in the output must be 0?

    Echo


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.