Oracle

how do I nest a CASE statement in an INNER JOIN

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2008

Posts: 1

Reputation: bohil is an unknown quantity at this point

Solved Threads: 0

bohil

Offline

Newbie Poster

how do I nest a CASE statement in an INNER JOIN

Nov 17th, 2008

Hello,

I am trying to nest some CASE statements into INNER JOINs (one CASE per INNER JOIN).

The current code is:

Help with Code Tags

sql Syntax (Toggle Plain Text)

SELECT 
a.zone, a.ifield, a.iwhse, a.idesc, a.iposition
FROM schema.itable a

<---This is where I would like to place the INNER JOIN w/ CASE

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
LEFT OUTER JOIN
(
SELECT
x.icode, x.ikey, x.idesc, x.icurrent
FROM schema.itablex
) b
ON a.iwhse = b.icode
AND a.ikey = b.ikey
AND icurrent = 'true'
LEFT OUTER JOIN
etc. . . . .
LEFT OUTER JOIN
(
SELECT
x.icode, x.ikey, x.idesc, x.icurrent
from schema.itablex
) b
ON a.iwhse = b.icode
AND a.ikey = b.ikey
AND icurrent = 'true'
LEFT OUTER JOIN
etc. . . . .

Anyway, I really don't even know where to begin with the syntax (assuming it can be done). I've tried to do the implied INNER JOIN by leaving the INNER JOIN w/ case in this form:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT iitem, ikey
CASE WHEN iitem IS NULL THEN 'Y'
WHEN iitem = ' ' THEN 'Y'
ELSE 'N"
END
FROM schema.itable
 
SELECT iitem, ikey
CASE WHEN iitem is NULL then 'Y'
WHEN iitem = ' ' then 'Y'
ELSE 'N"
END
FROM schema.itable

And, I've also tried to spell out the INNER JOIN and put the CASE statement into the ON clause like this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
 INNER JOIN
(
SELECT iitem, ikey
FROM schemai.table
WHERE icurrent = 'true'
)
ON 
CASE WHEN iitem IS NULL THEN 'Y'
WHEN iitem = ' ' THEN 'Y'
ELSE 'N"
END
 
 INNER JOIN
(
SELECT iitem, ikey
FROM schemai.table
WHERE icurrent = 'true'
)
ON 
CASE WHEN iitem is NULL then 'Y'
WHEN iitem = ' ' then 'Y'
ELSE 'N"
END

. . . also tried with parens around the CASE statement.

I'm not only new to databases and SQL, but am also new to posting on forums for assistance. So, if there's any other info that would be helpful, please let me know.

Thanks!

Last edited by bohil; Nov 17th, 2008 at 11:44 am.