Oracle

RSS

how do I nest a CASE statement in an INNER JOIN

Please support our Oracle advertiser: Programming Forums

•

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 10:44 am.

Thread Tools Display Modes

Quick Reply to Thread

•

Only community members can participate in forum threads. You must register or log in to contribute.

Other Threads in the Oracle Forum

Previous Thread: Help! Needed to create Oracle abstract data types and collections using SQL*Plus
Next Thread: Problem with pl/sql report

•

Views: 1937 | Replies: 0 | Currently Viewing: 1 (0 members and 1 guests)

•

Thread Tools	Display Modes
Show Printable Version Email this Page	Linear Mode Switch to Hybrid Mode Switch to Threaded Mode