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