Using CASE Expression in SQL Server to create a Computed Column – Avoiding Nulls

I had recently posted on Using CASE Expression in SQL Server to create a Computed Column. Burt C commented asking if it was possible to avoid NULLS for the rows that did not match a condition. The answer is that it is possible to avoid NULLS and can be done easily using COALESCE

Here’s the modified query:

DECLARE @tmp TABLE(Quantity integer,
Price decimal,
DiscountedPrice AS CAST (
 COALESCE(
        CASE
            WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10
            WHEN Quantity BETWEEN 20 and 30  THEN Price - Price * .20
            WHEN Quantity >=30 THEN Price -  Price * .40
        END
  ,0)
    AS Money)
)
   
   
INSERT INTO @tmp values(4, 4000)
INSERT INTO @tmp values(16, 3500)
INSERT INTO @tmp values(12, 5000)
INSERT INTO @tmp values(22, 6620)
INSERT INTO @tmp values(5, 6400)
INSERT INTO @tmp values(21, 1000)
INSERT INTO @tmp values(37, 7000)

SELECT * FROM @tmp

OUTPUT

Computer Columns COALESCE

As you can see, all the NULLS got replaced with 0

at

2 comments:

  1. tripyApril 10, 2010 at 5:49 AM

    Or you could use another statement in your switch...

    DECLARE @tmp TABLE(Quantity integer,
    Price decimal,
    DiscountedPrice AS CAST (
    CASE
    WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10
    WHEN Quantity BETWEEN 20 and 30 THEN Price - Price * .20
    WHEN Quantity >=30 THEN Price - Price * .40
    WHEN Quantity IS NULL THEN 0
    END
    AS Money)
    )

    ReplyDelete
  2. LaquedasApril 12, 2010 at 6:17 AM

    Or you can use the isNull function:

    DECLARE @tmp TABLE(Quantity integer,
    Price decimal,
    DiscountedPrice AS CAST (
    isnull(
    CASE
    WHEN Quantity BETWEEN 10 and 20 THEN Price - Price * .10
    WHEN Quantity BETWEEN 20 and 30 THEN Price - Price * .20
    WHEN Quantity >=30 THEN Price - Price * .40
    END
    ,0)
    AS Money)
    )


    INSERT INTO @tmp values(4, 4000)
    INSERT INTO @tmp values(16, 3500)
    INSERT INTO @tmp values(12, 5000)
    INSERT INTO @tmp values(22, 6620)
    INSERT INTO @tmp values(5, 6400)
    INSERT INTO @tmp values(21, 1000)
    INSERT INTO @tmp values(37, 7000)

    SELECT * FROM @tmp

    ReplyDelete