Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: New member / outer join problem
Hi Stephane,
Review your logic. A row full of NULLs has no signification. If your statement is embedded into either some PL/SQL code or a 3rd generation language, Oracle will generate a 'no data found' error, and this is what you need to trap.
I Understand, what you mean - one of my collague asked me to solve this problem (the environment, in which he wants to embed this code requires some rows). Of course, the "full NULL" row has no meaning. But our DB is very-very dirty (no comment), so in normal case where I want to use this statement, there is always a row.
Finally I solved this problem with a "workaround":
ON ((b.m1=dummy or 1=1 )and b.m1='c')
So I realized, if there is no column from the "left" side in the join-expression, the select will return no rows if there is no matching row(s) in the "right" side. (If there are matching rows, the select will provide them). With this trick it will provide the required result... I don't know, if this is a bug, or this is the normal operation....
Thanks -
Attila
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <attila.mosolygo_at_GARANCIA.HU
INET: attila.mosolygo_at_GARANCIA.HU
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Nov 06 2003 - 07:39:26 CST