Solved

NULLIF display 0 instead of NULL SQL 2012

Posted on 2016-07-22
13
34 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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

919 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

15 Experts available now in Live!

Get 1:1 Help Now