Re: if-then-else in SELECT
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 03 Mar 2004 12:24:53 -0700
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@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:
