| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Always return at least one value
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_at_DTAG.Com [mailto:Stephen.Lee_at_DTAG.Com]
Sent: Friday, March 19, 2004 11:27 AM
To: oracle-l_at_freelists.org
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?
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |