CODEWITHSUNDEEP
sqlsql-server
megabytes
megabytes

Reputation: 185

checking multiple SQL tables for null values

I have a SQL script that has queries multiple tables searching for a value but its only to check the next table if the first table returns a null value. I'm using the case statements, checking IS NULL THEN etc - see below. There has to be a simpler way to do this?

CASE
    WHEN
        CASE
            WHEN
                CASE    
                    WHEN
                        SELECT accountcode from a238 where reference='xyz'
                    IS NULL THEN
                        SELECT accountcode from a241 where reference='xyz'
                    ELSE
                        SELECT accountcode from a238 where reference='xyz'
                END
            IS NULL THEN
                SELECT accountcode from a240 where reference='xyz'
            ELSE
                CASE    
                    WHEN
                        SELECT accountcode from a238 where reference='xyz'
                    IS NULL THEN
                        SELECT accountcode from a241 where reference='xyz'
                    ELSE
                        SELECT accountcode from a238 where reference='xyz'
                END
        END
    IS NULL THEN
        SELECT accountcode from a239 where reference='xyz'
    ELSE
        CASE
            WHEN
                CASE    
                    WHEN
                        SELECT accountcode from a238 where reference='xyz'
                    IS NULL THEN
                        SELECT accountcode from a241 where reference='xyz'
                    ELSE
                        SELECT accountcode from a238 where reference='xyz'
                END
            IS NULL THEN
                SELECT accountcode from a240 where reference='xyz'
            ELSE
                CASE    
                    WHEN
                        SELECT accountcode from a238 where reference='xyz'
                    IS NULL THEN
                        SELECT accountcode from a241 where reference='xyz'
                    ELSE
                        SELECT accountcode from a238 where reference='xyz'
                END
        END
END

This code works, but its a prick to work with and read. Is there a better solution that someone could direct me too?

cheers

Upvotes: 0

Views: 1440

Answers (1)

pkatsourakis
pkatsourakis

Reputation: 1082

That query would be a huge pain to deal with if you need to debug or modify something. Credit to Parfait in the comments for his suggestion on the full outer join. In addition you can use COALESCE() to replace all of the CASE statements.

With the nesting and similar table names there is a chance I missed something, but this is the idea. 

declare @referenceCode varchar(3) = 'xyz'

select 
    COALESCE(a238.accountcode, a241.accountcode, a240.accountcode, a239.accountcode) as account_code
from a238 
full outer join a241 on a241.reference = @referenceCode
full outer join a240 on a240.reference = @referenceCode
full outer join a239 on a239.reference = @referenceCode
where a238.reference = @referenceCode

Upvotes: 3

Related Questions