It seems that the basic problem comes down to being able to add a row and then remove it if needed. The addition is easy, it was the conditional subtraction that I could not figure out...until now... t1 is a table made up of the OBJECT_NAMES from DBA_OBJECTS. There are multiple PLAN_TABLES. SQL> -- Select PLAN_TABLE and return 2 rows SQL> SQL> select o_name 2 from (select object_name o_name, rownum row_num 3 from t1 4 where object_name = 'PLAN_TABLE' 5 union 6 select '-', 0 row_num 7 from dual 8 order by row_num desc) 9 where (o_name = 'PLAN_TABLE') 10 or (o_name = '-' and 11 rownum = 1); O_NAME -------------------------------------------------------------------------------- PLAN_TABLE PLAN_TABLE SQL> SQL> -- Select PLAN_TABLE_NOT and return 2 rows SQL> SQL> select o_name 2 from (select object_name o_name, rownum row_num 3 from t1 4 where object_name = 'PLAN_TABLE_NOT' 5 union 6 select '-', 0 row_num 7 from dual 8 order by row_num desc) 9 where (o_name = 'PLAN_TABLE_NOT') 10 or (o_name = '-' and 11 rownum = 1); O_NAME -------------------------------------------------------------------------------- - Replace the code with your specific needs and bingo! It's Friday, It's 80 degrees and Sunny in Denver...I AM OUTA HERE! Daniel Stephen.Lee@xxxxxxxx wrote: > 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@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 > ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------