CODEWITHSUNDEEP
sqlsql-servercase
user3394606
user3394606

Reputation: 13

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

Upvotes: 0

Views: 1692

Answers (2)

Matt
Matt

Reputation: 14341

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

Upvotes: 2

serverSentinel
serverSentinel

Reputation: 994

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

Upvotes: 3

Related Questions