NULLIF display 0 instead of NULL SQL 2012

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

mburk1968Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mburk1968Author Commented:
Since you mentioned it I receive the following

Msg 8134, Level 16, State 1, Line 10
Divide by zero error encountered.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
mburk1968Author Commented:
Incorrect syntax error...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
can you show your full query?
0
 
mburk1968Author Commented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
can you help us a bit saying where is the error!
0
 
Éric MoreauSenior .Net ConsultantCommented:
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
 
mburk1968Author Commented:
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
 
mburk1968Author Commented:
Thank you both. Worked perfectly
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.