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

Home -> Community -> Mailing Lists -> Oracle-L -> Always return at least one value

Always return at least one value

From: <Stephen.Lee_at_DTAG.Com>
Date: Fri, 19 Mar 2004 10:27:17 -0600
Message-ID: <D6339830FC73944E889CC3CEADDB205B0790929D@bu-dtagpo1.tracs.com>

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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Fri Mar 19 2004 - 10:23:52 CST

Original text of this message

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