RE: if-then-else in SELECT
- From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Mar 2004 11:56:58 -0700
You can do something like:
SELECT NAME, CASE WHEN NAME = 'AIMEE GORE' THEN 1 ELSE 0 END EXACT_MATCH
FROM AG_CLIENT
WHERE NAME = 'AIMEE GORE' OR NAME IN ('AIMEE','AIMEE GORE')
ORDER BY 2 DESC
Your calling program can check the value in exact_match. If 1, then no
reason to fetch any more records (unless you expect duplicates), otherwise
fetch all remaining records.
HTH,
-----------------------------------------
Alan Davey
Senior Analyst/Project Leader
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458
-----Original Message-----
From: elain he [mailto:elainhe@xxxxxxxxxxx]
Sent: Wednesday, March 03, 2004 1:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: if-then-else in SELECT
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
-----------------------------------------------------------------
"This information in this e-mail is intended solely for the addressee and
may contain information which is confidential or privileged. Access to this
e-mail by anyone else is unauthorized. If you are not the intended
recipient, or believe that you have received this communication in error,
please do not print, copy, retransmit, disseminate, or otherwise use the
information. Also, please notify the sender that you have received this
e-mail in error, and delete the copy you received."
----------------------------------------------------------------
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:
