From oracle-l-bounce@freelists.org Wed Mar 3 13:26:00 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i23JQ0D17806 for ; Wed, 3 Mar 2004 13:26:00 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i23JPwo17798 for ; Wed, 3 Mar 2004 13:25:58 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 436F6394C6B; Wed, 3 Mar 2004 14:22:29 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 03 Mar 2004 14:21:11 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from nwkea-mail-1.sun.com (nwkea-mail-1.sun.com [192.18.42.13]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E79D4394911 for ; Wed, 3 Mar 2004 14:21:03 -0500 (EST) Received: from phys-giza-1 ([129.147.4.102]) by nwkea-mail-1.sun.com (8.12.10/8.12.9) with ESMTP id i23JOr0N019747 for ; Wed, 3 Mar 2004 11:24:54 -0800 (PST) Received: from sun.com (sr1-ubrm-16.Central.Sun.COM [129.147.4.20]) by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HU000FACLXHET@giza-mail1.Central.Sun.COM> for oracle-l@freelists.org; Wed, 03 Mar 2004 12:24:53 -0700 (MST) Date: Wed, 03 Mar 2004 12:24:53 -0700 From: Daniel Fink Subject: Re: if-then-else in SELECT To: oracle-l@freelists.org Message-id: <40463105.B3A3A2AA@sun.com> MIME-version: 1.0 X-Mailer: Mozilla 4.79C-CCK-MCD [en] (X11; U; SunOS 5.9 sun4u) Content-Type: multipart/alternative; boundary=------------468F9533E2E7698B48AAB5B8 X-Accept-Language: en References: X-archive-position: 223 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Daniel.Fink@Sun.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l --------------468F9533E2E7698B48AAB5B8 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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@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 > ----------------------------------------------------------------- --------------468F9533E2E7698B48AAB5B8 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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@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
-----------------------------------------------------------------

--------------468F9533E2E7698B48AAB5B8-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------