user3394606 user3394606 - 3 months ago 19
SQL Question

SQL CASE returning wrong values

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

Answer

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