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