RE: Always return at least one value
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 19 Mar 2004 11:34:46 -0500
How about this:
select field from table where field='LITERAL'
union all
select '-' from dual
where not exists(select field from table where field='LITERAL');
I think that ought to do it....
-Mark
-----Original Message-----
From: Stephen.Lee@xxxxxxxx [mailto:Stephen.Lee@xxxxxxxx]
Sent: Friday, March 19, 2004 11:27 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Always return at least one value
Is there is short and sweet way to do:
Select field from table where field = 'LITERAL'.
If no rows then return a '-'.
If there are matches then return all the matches.
I trying to avoid multiple select statements, loops, conditional statements,
etc. because it is expected that this could possibly run a couple thousand
times per minute.
Something like:
select nvl(field,'-') from (select field from TABLE where character =
'VALUE') A, sys.dual B where A.field(+) = B.dummy;
will work if there is no match in the table. But if there are multiple
matches, it will only return one of them because there is only one row in
DUAL. Is there something in version 9 that can do this in a single slick
and cool query?
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: