Solved

NULLIF display 0 instead of NULL SQL 2012

Posted on 2016-07-22
13
29 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 69

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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 69

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 69

Expert Comment

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now