outer join issue

pts.
Tags:
Database
DB2
Oracle
Following occurs in a cursor is plsql block: I have 3 tables: Source (name) Lookupmap (name, lookupname) Lookup (lookupname) I have to: 1)outer join Source and LookupMap based on NAME in order to validate that NAME actually exists in Lookupmap. If NAME in LookupMap is null then I have to provide a message. This is okay so far. 2) I have to also validat that lookupname or name also exists in Lookup table. If the Lookup.Lookupname is null, again I need to provide a different message. The above would require me to do outer join Source table with Lookupmap table and Lookupmap table with Lookup table. This is a 3 way outer join which results in ORA-01417: a table may be outer joined to at most one other table. Any idea how this can be resolved?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Old Oracle syntax:

SELECT s.name,
______DECODE(lm.name,
_____________NULL, ‘The first hop failed’,
___________________DECODE(L.name,
__________________________NULL, ‘Second hop failed’,
________________________________’Success’)) status
___FROM Source s,
______LookupMap lm,
______Lookup L
___WHERE s.name=lm.name(+) AND
______lm.lookupName=L.lookupName(+);

New Oracle ANSI syntax:

SELECT s.name,
______CASE
_________WHEN L.name IS NOT NULL THEN ‘Success’
_________WHEN lm.name IS NOT NULL THEN ‘Second hop failed’
_________ELSE ‘First hop failed’ END status
___FROM Source s
___LEFT OUTER JOIN LookupMap lm ON
______s.name=lm.name
___LEFT OUTER JOIN Lookup L ON
______lm.lookupName=L.lookupName;

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following