Search code examples
sqlsasenterprise-guide

SQL Case Statement NULL column

proc SQL;


CREATE TABLE DATA.DUMMY AS
SELECT *,  
  CASE 
  WHEN (Discount IS NOT NULL) 
    THEN (Total_Retail_Price - (Total_Retail_Price * Discount)) * Quantity AS Rev
  ELSE (Total_Retail_Price * Quantity) AS Rev
  END

FROM DATA.Cumulative_Profit_2013 AS P

;

I am trying to factor in a potentially NULL column as part of the expression for Revenue. But my case statement throws up issues. I've checked other examples, but I can't see a why that would help

Solution

  • It looks like you can use COALESCE to achieve your goal without an explicit conditional:

    SELECT *,  
  (Total_Retail_Price - (Total_Retail_Price * COALESCE(Discount, 0))) * Quantity AS Rev
FROM DATA.Cumulative_Profit_2013 AS P