SQL CASE returning wrong values

Question

I wrote the following query but the CASE statements are returning incorrect values. When soaddr has a value instead of returning the value from soaddr it will return the incorrect value from arcust. But if I change the else statment to pull the value from soaddr then it will return a NULL value. What am I doing wrong?

SELECT DISTINCT a.custno, b.company,
    CASE WHEN c.address1 = NULL THEN b.address1
    ELSE b.address1
    END as address,
    CASE WHEN c.city = NULL THEN b.city
    ELSE b.city
    END as city,
    CASE WHEN c.addrstate = NULL THEN b.addrstate
    ELSE b.addrstate
    END as addrstate,
    CASE WHEN c.zip = NULL THEN b.zip
    ELSE b.zip
    END as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno

don't you want else c.address1 when soaddr is not null? or you could just use coalesce(c.address1,b.address1) using soaddr when present else using arcust.address1. — xQbert
– xQbert, Commented Aug 26, 2016 at 19:56
I do a lot of ETL work and a lot of times I am checking not only is a text value null I need to check if it is a empty string so I wrap these columns using ISNULL(<ColName>, '') IS NULL. Please see msdn.microsoft.com/en-us/library/ms184325.aspx — Mark Kram
– Mark Kram, Commented Aug 26, 2016 at 19:57

serverSentinel · Accepted Answer · 2016-08-26 19:57:48Z

3

You can't compare a value to NULL with =. Use is null instead.

CASE WHEN c.address1 IS NULL THEN b.address1
 ELSE b.address1

edited Aug 26, 2016 at 19:57

answered Aug 26, 2016 at 19:53

serverSentinel

9946 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

xQbert Over a year ago

less the ** around is.. :P... It's returning b.address1 on both evaluations... seems odd to me I would think the else should be c.address1.

serverSentinel Over a year ago

also, consider using isnull( c.address1, b.address1 ) or coalesce( c.address1, b.address1). coalesce has the added benefit of looking at as many fields as you want or returing a default value. coalesce( c.address1, b.address1, 'No Address').

Matt · Accepted Answer · 2016-08-26 20:10:40Z

The reason it is returning the wrong value is that your case statement is setup to always return the arcust value because all parts of the THEN & ELSE reference the b table alias which points to arcust. Between that and comparing the null incorrectly as @ServerSentinel appropriately points out you are not getting your desired results. Modify your query as follows to point to the c table alias and compare the null as IS NULL

SELECT DISTINCT a.custno, b.company,
    CASE WHEN c.address1 IS NULL THEN b.address1
    ELSE c.address1
    END as address,
    CASE WHEN c.city IS NULL THEN b.city
    ELSE c.city
    END as city,
    CASE WHEN c.addrstate IS NULL THEN b.addrstate
    ELSE c.addrstate
    END as addrstate,
    CASE WHEN c.zip IS NULL THEN b.zip
    ELSE c.zip
    END as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno

Next learning COALESCE() is a huge help to you here because it basically writes the case statement for you and returns the first non null value. So you could simply write:

SELECT DISTINCT a.custno, b.company,
    COALESCE(c.address1,b.address1) as address,
    COALESCE(c.city,b.city) as city,
    COALESCE(c.addrstate,b.addrstate) as addrstate,
    COALESCE(c.zip,b.zip) as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno

Which will give you the soaddr column if it is not null and if it is then you will get the arcust address.

However because address data should probably be kept together meaning you should select and entire address from 1 table instead of potentially merging you should stick with your case statement but always test 1 field to determine if there is a soaddr that field should be the unique id for that table if one exists if not use another column such as Address1.

SELECT DISTINCT a.custno, b.company,
    CASE WHEN c.UniqueId IS NULL THEN b.address1
    ELSE c.address1
    END as address,
    CASE WHEN c.UniqueId IS NULL THEN b.city
    ELSE c.city
    END as city,
    CASE WHEN c.UniqueId IS NULL THEN b.addrstate
    ELSE c.addrstate
    END as addrstate,
    CASE WHEN c.UniqueId IS NULL THEN b.zip
    ELSE c.zip
    END as zip,
    invno, descrip, qtyshp, price, extprice, b.tax, invdte 
FROM artran a
LEFT JOIN arcust b ON a.custno = b.custno
LEFT JOIN soaddr c ON a.custno = c.custno

Collectives™ on Stack Overflow

SQL CASE returning wrong values

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related