Set zero default value for null rows in SQL Server

Question

I have this query

SELECT  
    LineId, [LineNumber], 
    TotalNeed, TotalMRC, TotalMIV, TotalIMIV,
    (TotalMRC - TotalMIV) as Shortage
FROM  
    (SELECT  
         LineId,[LineNumber],
         ROUND(SUM(Quantity), 3) AS TotalNeed,
         SPMS2.dbo.ReturnTotalMRCByLineId(LineId) AS TotalMRC, 
         SPMS2.dbo.ReturnTotalMIVByLineId(LineId) AS TotalMIV,
         SPMS2.dbo.ReturnTotalIMIVByLineId(LineId) AS TotalIMIV
     FROM  
         [SPMS2].[dbo].[ViewMTO]
     GROUP BY 
         lineid, [LineNumber]) a

And these are the results :

As you can see some of my columns value are null. How can I set it to 0 if the value of that is null?

@GordonLinoff which part of query i should use it dear friend ? — Ehsan Akbar
– Ehsan Akbar, Commented Sep 3, 2016 at 17:26

Gordon Linoff · Accepted Answer · 2016-09-03 17:27:45Z

1

Here is a simple way:

  SELECT mto.LineId, mto.[LineNumber], round(sum(mto.Quantity), 3) as TotalNeed
         v.*, (v.TotalMRC - v.TotalMIV ) as Shortage
  FROM [SPMS2].[dbo].[ViewMTO] mto CROSS APPLY
       (VALUES (ISNULL(SPMS2.dbo.ReturnTotalMRCByLineId(mto.LineId), 0),
                ISNULL(SPMS2.dbo.ReturnTotalMIVByLineId(mto.LineId), 0),
                ISNULL(SPMS2.dbo.ReturnTotalIMIVByLineId(mto.LineId), 0)
               )
       ) v(TotalMRC, TotalMIV, TotalIMIV)
  GROUP BY mto.lineid, mto.[LineNumber];

Normally, I prefer COALESCE() to ISNULL() (because the former is standard). However, COALESCE() evaluates the first argument twice. So for an expensive operation (such as a user-defined function), ISNULL() is more efficient.

answered Sep 3, 2016 at 17:27

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2016-09-03 17:28:29Z

1

You can use the coalesce to replace a null with a value:

SELECT  LineId,
        [LineNumber],
        TotalNeed,
        TotalMRC,
        TotalMIV,
        TotalIMIV,
        (TotalMRC - TotalMIV) as Shortage
  FROM  (
        SELECT  LineId,[LineNumber],
                round(sum(Quantity),3) as TotalNeed
                ,COALESCE(SPMS2.dbo.ReturnTotalMRCByLineId(LineId), 0) as TotalMRC
                ,COALESCE(SPMS2.dbo.ReturnTotalMIVByLineId(LineId), 0) as TotalMIV
                ,COALESCE(SPMS2.dbo.ReturnTotalIMIVByLineId(LineId), 0)  as TotalIMIV
          FROM  [SPMS2].[dbo].[ViewMTO]
          GROUP BY lineid,[LineNumber]
        ) a

answered Sep 3, 2016 at 17:28

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Collectives™ on Stack Overflow

Set zero default value for null rows in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related