RE: Always return at least one value

From: <sfaroult_at_oriole.com>
Date: Fri, 19 Mar 2004 12:16:50 -0500 (EST)
Message-Id: <20040319171034.501AF390496@turing.freelists.org>

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

• 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_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 - 11:13:23 CST