Joining to a different field if one is blank

[knacath]

Hi folks,

I've run into an interesting problem. I've 2 tables, Table1 & Table2. Table1.A needs a join to Table2.C. However, if Table1.A is blank (empty string, not null), it is Table1.B that needs to go out to Table2.C I'm using DB2/AS400. Any ideas?? Thanks.

 

[tonedef]

You could try
[tt]
SELECT
*
FROM
Table1,
Table2
WHERE
Table1.A = Table2.C AND
Table1.A != ''
UNION SELECT
*
FROM
Table1,
Table2
WHERE
Table1.B = Table2.C AND
Table1.A = '';
[/tt]
That should give you the results you are looking for.


Tone