Re: if-then-else in SELECT

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Wed, 03 Mar 2004 12:24:53 -0700
Message-id: <40463105.B3A3A2AA@sun.com>

Elain,

This code is ugly, but should work with 8.1.6+. Be aware that the code may perform horribly and the best solution is a pl/sql block.

This is the return_name.sql script referenced **

SQL> l
1 select distinct decode ((select count(*) from test_name where name = 'AIMEE GORE'),

  2                 0, x.name,
  3                 y.name) return_name
  4  from (select name from test_name where name = 'AIMEE GORE') y,
  5       (select name from test_name where name like 'AIMEE%' or name

like '%GORE') x
6* where y.name (+) = x.name

SQL> select name from test_name;

NAME

AIMEE MANN
AL GORE
GEORGE W. BUSH
AIMEE GORE SQL> @return_name

RETURN_NAME

AIMEE GORE SQL> delete from test_name where name = 'AIMEE GORE';

1 row deleted.

SQL> select name from test_name;

NAME

AIMEE MANN
AL GORE
GEORGE W. BUSH SQL> @return_name

RETURN_NAME

AIMEE MANN
AL GORE elain he wrote:

> Hi,
> I would like to retrieve records based on the following if-then-else
> criteria.
>
> for eg.
> retrieve all empolyee names='AIMEE GORE'
> if there is no exact match,
> retrieve employee names='AIMEE' or employee_name='GORE'
>
> How can I write the SQL?
>
> DB - 8i
>
> thanks.
>
> elain
>
> _________________________________________________________________
> Learn how to help protect your privacy and prevent fraud online at Tech
> Hacks & Scams. http://special.msn.com/msnbc/techsafety.armx
>
> ----------------------------------------------------------------
> 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 Wed Mar 03 2004 - 13:26:00 CST