Solved

NULLIF display 0 instead of NULL SQL 2012

Posted on 2016-07-22
13
46 Views
Last Modified: 2016-07-22
I have the need to display a 0 instead of NULL for the following calculation however I cannot seem to format it properly.? I know I'm missing something easy.

SUM(DATA.Style_Retail_Price - DATA.Style_StdCost)
        / NULLIF(DATA.Style_Retail_Price, 0) AS [TY MU] ,

Open in new window

0
Comment
Question by:mburk1968
  • 5
  • 4
  • 4
13 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 250 total points
ID: 41724694
nullif will display a NULL if the value (0 in your case) is met.

what you want is:
isnull(SUM(DATA.Style_Retail_Price - DATA.Style_StdCost) / DATA.Style_Retail_Price, 0) AS [TY MU] ,

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41724721
You'll also want to think about avoiding any division by zero errors if your denominator is NULL, which is then converted to zero, which would throw the error.  

Since Eric already posted code I'll defer to his answer..
0
 

Author Comment

by:mburk1968
ID: 41724816
Since you mentioned it I receive the following

Msg 8134, Level 16, State 1, Line 10
Divide by zero error encountered.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 41724832
Ok.  In that case, the basic way to handle this is in a CASE block that goes like this...

CASE WHEN denominator = 0 THEN 0 ELSE numerator/denominator END

So in your case, building on Eric's code

SUM (
   CASE
      WHEN ISNULL(Data.Style_Retail_Price,0) = 0 THEN 0
      ELSE (DATA.Style_Retail_Price - DATA.Style_StdCost) / DATA.Style_Retail_Price, 0 )
      END as [TY MU] ,
0
 

Author Comment

by:mburk1968
ID: 41724885
Incorrect syntax error...
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41724889
Looks like there was an orphan , 0

SUM (
   CASE
      WHEN ISNULL(Data.Style_Retail_Price,0) = 0 THEN 0
      ELSE (DATA.Style_Retail_Price - DATA.Style_StdCost) / DATA.Style_Retail_Price)
      END as [TY MU] ,
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41724893
can you show your full query?
0
 

Author Comment

by:mburk1968
ID: 41724902
DECLARE @Division CHAR(3);
DECLARE @MthStartDate DATETIME;
DECLARE @MthEndDate DATETIME;
DECLARE @YrStartDate DATETIME;
SET @Division = 'KLL';
SET @MthStartDate = '2016-07-01 00:00:00.000';
SET @MthEndDate = '2016-07-31 00:00:00.000';
SET @YrStartDate = '2016-01-01 00:00:00.000';

SELECT  DATA.customer ,
        zzxcustr.cust_name ,
        DATA.CLASS ,
        DATA.style_name ,
        DATA.style ,
        DATA.SCALEFIN AS [Size] ,
        DATA.color_code AS [Color] ,
        SUM(DATA.Style_StdCost) AS LDP ,
        SUM(DATA.Style_Retail_Price) AS MSRP ,
        SUM (
    CASE 
       WHEN ISNULL(Data.Style_Retail_Price,0) = 0 THEN 0 
       ELSE (DATA.Style_Retail_Price - DATA.Style_StdCost) / DATA.Style_Retail_Price) 
       END as [TY MU] , 
        Style_Season ,
        SUM(DATA.total_qty * DATA.price * DATA.co_rate) AS YTD_Ext_Gross_Amt ,
        SUM(DATA.total_qty) AS YTD_Total_Qty ,
        SUM(DATA.total_qty * DATA.price * DATA.co_rate - ( DATA.total_qty
                                                           * DATA.price
                                                           * DATA.co_rate )
            * ( ISNULL(zzxdiscr.disc_perc, 0) / 100 )) AS YTD_Ext_Net_Amt ,
        SUM(DATA.total_qty * DATA.Style_StdCost) AS YTD_Ext_Cost ,
        SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
                                                          AND @MthEndDate )
                                THEN ( DATA.total_qty * DATA.price
                                       * DATA.co_rate )
                                ELSE ( 0 )
                           END)) AS MTD_Ext_Gross_Amt ,
        SUM(CONVERT(MONEY, ( CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
                                                            AND
                                                              @MthEndDate )
                                  THEN ( DATA.total_qty * DATA.price
                                         - ( DATA.total_qty * DATA.price )
                                         * ( ISNULL(zzxdiscr.disc_perc, 0)
                                             / 100 ) )
                                  ELSE ( 0 )
                             END ) * DATA.co_rate)) AS MTD_Ext_Net_Amt ,
        SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
                                                          AND @MthEndDate )
                                THEN ( DATA.total_qty * DATA.Style_StdCost )
                                ELSE ( 0 )
                           END)) AS MTD_Ext_Cost ,
        SUM(( DATA.total_qty * DATA.price * DATA.co_rate )
            * ( ISNULL(zzxdiscr.disc_perc, 0) / 100 )) AS YTD_Ext_Discount ,
        SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
                                                          AND @MthEndDate )
                                THEN ( ( DATA.total_qty * DATA.price
                                         * DATA.co_rate )
                                       * ( ISNULL(zzxdiscr.disc_perc, 0) / 100 ) )
                                ELSE ( 0 )
                           END)) AS MTD_Ext_Discount ,
        SUM(CONVERT(MONEY, CASE WHEN ( ship_date_cascaded BETWEEN @MthStartDate
                                                          AND @MthEndDate
                                       AND DATA.slsperson1 LIKE 'JZ%'
                                     )
                                THEN ( DATA.total_qty * DATA.price
                                       * DATA.co_rate )
                                ELSE ( 0 )
                           END)) AS MTD_Ext_Intercompany ,
        SUM(CONVERT(MONEY, CASE WHEN ( DATA.slsperson1 LIKE 'JZ%' )
                                THEN ( DATA.total_qty * DATA.price
                                       * DATA.co_rate )
                                ELSE ( 0 )
                           END)) AS YTD_Ext_Intercompany
FROM    [KLL All Order Detail With Style Data] DATA
        INNER JOIN zzxcustr ON DATA.customer = zzxcustr.customer
        LEFT OUTER JOIN zzxdiscr ON DATA.discount = zzxdiscr.discount
        LEFT OUTER JOIN ( SELECT    division ,
                                    style ,
                                    color_code ,
                                    lbl_code ,
                                    dimension ,
                                    MAX(trans_date) AS MaxDate
                          FROM      zzcordrd
                          WHERE     stage = 'RECV'
                          GROUP BY  division ,
                                    style ,
                                    color_code ,
                                    lbl_code ,
                                    dimension
                        ) ORD ON DATA.division = ORD.division
                                 AND DATA.style = ORD.style
                                 AND DATA.color_code = ORD.color_code
                                 AND DATA.lbl_code = ORD.lbl_code
                                 AND DATA.SCALEFIN = ORD.dimension
WHERE   ( DATA.line_status = 'I' )
        AND ( DATA.ship_date_cascaded BETWEEN @YrStartDate
                                      AND     @MthEndDate )
        AND ( DATA.division = @Division )
        --AND ( DATA.customer IN ( @Customers ) )
        AND ( DATA.customer IN ( 'DEC0010', 'MAD9060', 'SEV0010' ) )
GROUP BY DATA.CLASS ,
        DATA.style_name ,
        DATA.style ,
        DATA.SCALEFIN ,
        DATA.color_code ,
        DATA.Style_StdCost ,
        DATA.Style_Retail_Price ,
        DATA.customer ,
        zzxcustr.cust_name ,
        Style_Season
ORDER BY DATA.customer ,
        Style_Season;

Open in new window

0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41724910
can you help us a bit saying where is the error!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 41724916
you are least missing a ) here:

        SUM (
    CASE 
       WHEN ISNULL(Data.Style_Retail_Price,0) = 0 THEN 0 
       ELSE (DATA.Style_Retail_Price - DATA.Style_StdCost) / DATA.Style_Retail_Price) 
       END) as [TY MU] ,

Open in new window

0
 

Author Comment

by:mburk1968
ID: 41724929
Same error when I paste that code into the query

Msg 102, Level 15, State 1, Line 22
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 92
Incorrect syntax near 'ORD'.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 41724947
Correction in Eric's code above, extra ) I believe

       SUM (
    CASE
       WHEN ISNULL(Data.Style_Retail_Price,0) = 0 THEN 0
       ELSE (DATA.Style_Retail_Price - DATA.Style_StdCost) / DATA.Style_Retail_Price
       END) as [TY MU] ,

To a certain extent you'll have to troubleshoot syntax on your own, as experts here cannot connect to your data source(s) and parse/run queries, so we're limited to eyeballing T-SQL and making guesses.
0
 

Author Closing Comment

by:mburk1968
ID: 41724954
Thank you both. Worked perfectly
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question