Re: Always return at least one value

  • From: Daniel Fink <Daniel.Fink@xxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 19 Mar 2004 14:04:23 -0700

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

Other related posts: