locked
Null must not display in result ,Display Null as 0. RRS feed

  • Question

  • User-367318540 posted

    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

    Output

    https://ibb.co/RPDp6YC

    Thursday, January 21, 2021 7:27 AM

Answers

  • User-939850651 posted

    Hi akhterr,

    According to your description and the query statement you provided. If you don't want to display null in column, you could use IsNull() function in sql query.

    Something like this:

    ;cte1 as ...
    ,cte2 as ...
    ......
    select c2.CodeItem,c2.Artical,c2.Short,
           c32.Opening_Qty,c32.Opening_Balance,
           c2.IN_QTY ,c2.IN_Weight, 
           IsNull(c1.OUT_QTY,0) OUT_QTY,IsNull(c1.OUT_weight,0) 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

    Or you could filter the data which DelID is not equal to null ( modify cte1):

    ;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'
                     and M.Del <> null                                   
              where D.Delidd is null and M.Del is null
                 group by i.Descriptionitem,i.ALID,i.CodeItem)

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 22, 2021 5:22 AM