CODEWITHSUNDEEP
sqloracle-databasejoin
LinkiLinki
LinkiLinki

Reputation: 11

SQL JOIN ON(with two conditions)

i want to know if is possible to write a SQL ORACLE command joining two tables with two CASE WHEN in the on() condition; I tried this way; but it didnt work

SELECT *
FROM A  a
   LEFT OUTER JOIN B b
       ON ((CASE
                WHEN LENGTH (a.xxx) = 3
                THEN a.xxx
                WHEN LENGTH (a.yyy) = 2
                THEN TO_CHAR (a.xxx || TRUNC (a.zzz, 'YY'))
            END) **=**
           (CASE
                WHEN LENGTH (a.xxx) = 3 THEN b.wert
                WHEN LENGTH (a.xxx) = 2 THEN b.bez
            END));

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Is this the logic that you want?

on (length(a.xxx) = 3 and a.xxx = b.wert) or
   (length(a.xxx) = 2 and (a.xxx || TRUNC(a.zzz, 'YY')) = b.bez)

case expressions are not normally needed for boolean expressions.

Upvotes: 1

Related Questions