• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 64
  • Last Modified:

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

0
mburk1968
Asked:
mburk1968
  • 5
  • 4
  • 4
2 Solutions
 
Éric MoreauSenior .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
 
mburk1968Author Commented:
Since you mentioned it I receive the following

Msg 8134, Level 16, State 1, Line 10
Divide by zero error encountered.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Jim HornMicrosoft 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
 
mburk1968Author Commented:
Thank you both. Worked perfectly
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now