RE: New member / outer join problem

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Thu, 06 Nov 2003 04:44:27 -0800
Message-ID: <F001.005D5D3F.20031106044427@fatcity.com>

Attila,

  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.   For instance in PL/SQL

   begin

     select c1, c2, c3
     into var1, var2, var3
     from some_table
     where some_condition;
   exception
     when no_data_found yhen
         var1 := null;
         var2 := null;
         var3 := null;

   end;

   At this point, you have what you want into var1 ... varn.  Assuming that the query may return several rows, you can do a BULK COLLECT into arrays.

  HTH,   SF

>----- ------- Original Message ------- -----
>From: <attila.mosolygo_at_GARANCIA.HU>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Thu, 06 Nov 2003 04:04:38
>
>Hi All,
>
>I'm a new member on this list. I'm not too familiar
>with ORA, but I have some experience in other
>RDBMS's.
>
>I had run into the following problem.
>My task is to write a query, which returns with an
>empty (full of NULLs) row, if the condition is
>false.
>
>I'm thinking in outer joins.
>
>I made a test. It returns all the matching rows (if
>there are such rows), but unfortunately returns
>with no row, if the condition is false.
>Could someone help me, what could be the problem?
>My oppinion is that it must be work fine... :-)
>
>I use Oracle9i Enterprise Edition Release 9.2.0.1.0
>.
>
>My test is here:
>--------------
>create table ATTILA_1 (m1 varchar2(10));
>insert into attila_1 values('a');
>insert into attila_1 values('a');
>insert into attila_1 values('b');
>
>SELECT
>b.*
>FROM
> dual left outer JOIN ATTILA_1 b
> ON (b.m1='c');
>
>-----------
>
>It returns correctly (with 2 or 1 rows) if the
>string in the condition is 'a' or 'b', but returns
>with no row (I'd like to see one row, with full of
>NULLs) if I call it with 'c'... :-(
>
>Could you tell me, why is this? Or any idea to
>solve this task?
>
>Thanks in advance:
>Attila
>--
>Please see the official ORACLE-L FAQ:
>http://www.orafaq.net
>--
>Author: <attila.mosolygo_at_GARANCIA.HU
> INET: attila.mosolygo_at_GARANCIA.HU
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

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 - 06:44:27 CST