RE: Always return at least one value
- From: <sfaroult@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 19 Mar 2004 12:16:56 EST5EDT
Stephen,
Something like that ? (sorry, it uses no fancy new function, just good ol'
stuff) :
SQL> set echo on
SQL> @x
SQL> select table_name
2 from user_tables
3 where table_name like 'O%'
4 /
TABLE_NAME
------------------------------
ORA_ESSAI_CONNEXION
ORIOLE$RAW_STATS
ORIOLE$STATS
ORIOLE$STMT
SQL> select table_name
2 from user_tables
3 where table_name like 'A%'
4 /
no rows selected
SQL> select decode(z.table_name, chr(255), 'Nix', z.table_name) table_name
2 from (select table_name,
3 rownum rn
4 from user_tables
5 where table_name like 'O%'
6 union all
7 select chr(255), 99999999999999999999
8 from dual) z
9 where decode(rownum, 1, z.rn, rownum) = z.rn
10 /
TABLE_NAME
------------------------------
ORA_ESSAI_CONNEXION
ORIOLE$RAW_STATS
ORIOLE$STATS
ORIOLE$STMT
SQL> select decode(z.table_name, chr(255), 'Nix', z.table_name) table_name
2 from (select table_name,
3 rownum rn
4 from user_tables
5 where table_name like 'A%'
6 union all
7 select chr(255), 99999999999999999999
8 from dual) z
9 where decode(rownum, 1, z.rn, rownum) = z.rn
10 /
TABLE_NAME
------------------------------
Nix
>
> Yeah. I was hoping I could avoid that additional select to check the
> existence of the character in the table. With all the new functions that
> keep popping up in Oracle SQL, I was hoping maybe there was one that was
> like: If no rows then return this thing, otherwise give me the rows.
>
> > -----Original Message-----
> >
> > 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
> >
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
-- Oracle scripts, papers and DBA Tools --
-- Oriole Corporation --
-- http://www.oriole.com/ --
----------------------------------------------------------------
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:
