Using SQL to conditionally check one column or the other?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Steve - 09 Apr 2008 02:53 GMT

    I have a strange need to address on account of some issues with dirty
data. To present a simplified version of the issue, I need to find a
sum of certain values that pass a filter criteria. However, some of the
rows will have NULL values in the column to be filtered... and when
that's the case, I need to check an alternate column instead.

    For example, here's a normal query that assumes good data:

SELECT SUM(AMOUNT)
FROM PURCHASES
WHERE ACCOUNT_CODE = 'foo';

    However, sometimes the "ACCOUNT_CODE" column will be NULL... so when
that's the case I need to join and look at another table for the value:

SELECT SUM(p.AMOUNT)
FROM PURCHASES p, ACCOUNT a
WHERE p.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER
AND a.ACCOUNT_CODE = 'foo';

    Is there some way that I can look at PURCHASE.ACCOUNT_CODE first... and
ONLY if that value is NULL look at ACCOUNT.ACCOUNT_CODE instead? I've
thought about a couple of different approaches I could take (some kind
of user-defined function, maybe?), but I'm not sure what would be the
cleanest. Thanks in advance!

Reply to this Message

Tonkuma - 09 Apr 2008 07:51 GMT

Although the relationship of ACCOUNT_CODE and ACCOUNT_NUMBER is not
clear
and relationship of PURCHASES table and ACCOUNT table is also not
clear,
(It will be better to show DDL and sample data)
my thought was as followings.

You wrote.....

>> However, sometimes the "ACCOUNT_CODE" column will be NULL... so when

that's the case I need to join and look at another table for the
value:

SELECT SUM(p.AMOUNT)
FROM PURCHASES p, ACCOUNT a
WHERE p.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER
AND a.ACCOUNT_CODE = 'foo'; <<

Is that "SELECT SUM(a.AMOUNT) ..."?
If so, you can try this.

SELECT SUM(COALESCE(A.amount, P.amount) )
FROM (SELECT *
FROM Purchases
WHERE account_code = 'A01'
OR account_code IS NULL
) P
LEFT OUTER JOIN
Account A
ON P.account_code IS NULL
AND A.account_number = P.account_number
AND A.account_code = 'A01'
WHERE
COALESCE(P.account_code, A.account_code) IS NOT NULL
;

Reply to this Message

mirof007 - 09 Apr 2008 14:39 GMT

> I have a strange need to address on account of some issues with dirty
> data. To present a simplified version of the issue, I need to find a
[quoted text clipped - 21 lines]
> of user-defined function, maybe?), but I'm not sure what would be the
> cleanest. Thanks in advance!

SELECT SUM(p.AMOUNT)
FROM PURCHASES p LEFT OUTER JOIN ACCOUNT a
ON (p.ACCOUNT_NUMBER = a.ACCOUNT_NUMBER)
WHERE COALESCE(p.ACCOUNT_CODE, a.ACCOUNT_CODE) = 'foo';

You could replace the left outer join with a regular (inner) join if
you know that each purchase will always have a matching account
present.

Hope this helps,
Miro

Reply to this Message