Re: Always return at least one value

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 19 Mar 2004 14:04:23 -0700
Message-id: <405B6057.D83626EF@sun.com>

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_at_DTAG.Com 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_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
> -----------------------------------------------------------------

---

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 - 15:00:59 CST