Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: New member / outer join problem

RE: New member / outer join problem

From: <attila.mosolygo_at_GARANCIA.HU>
Date: Thu, 06 Nov 2003 05:39:26 -0800
Message-ID: <F001.005D5D43.20031106053926@fatcity.com>


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 want to run it in SQL, not in PL/SQL.

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":



SELECT
b.*
FROM
  dual left outer join ATTILA_1 b

             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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US