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 -----------------------------------------------------------------