Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: if-then-else in SELECT
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.
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 namelike '%GORE') x
SQL> select name from test_name;
NAME
RETURN_NAME
1 row deleted.
SQL> select name from test_name;
NAME
RETURN_NAME
> 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
> -----------------------------------------------------------------
-- 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