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: <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>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: <[EMAIL PROTECTED]
> INET: [EMAIL PROTECTED]
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
