RE: ** SQL WHERE clause order

From: Naveen, Nahata (IE10) <Naveen.Nahata_at_honeywell.com>
Date: Wed, 05 Nov 2003 22:14:27 -0800
Message-ID: <F001.005D5B3D.20031105221427@fatcity.com>

I think its still pretty simple. Since in either of the conditions the All you need to do is to define 2 cursors....

<CODE>

DECLARE
    CUSROR c1
    IS
    SELECT emp_id

      FROM  emp
     WHERE  dept = :dept
       AND  salary > :min_sal;

    CUSROR c2
    IS
    SELECT emp_id

      FROM  emp
     WHERE  dept != :dept
       AND  salary < :min_sal;

BEGIN
    IF select_sen_emp_chk_first = 'Y' THEN

        FOR c IN c1 LOOP
            -- Do you stuff here
        END LOOP;
    ELSIF select_sen_emp_chk_first = 'N' THEN
        FOR c IN c2 LOOP
            -- Do you stuff here
        END LOOP;
    ELSE -- If select_sen_emp_chk_first IS NULL
        -- Do you stuff here

    END IF;
END; </CODE>

Regards
Naveen

> -----Original Message-----
> From: A Joshi [mailto:ajoshi977_at_yahoo.com]
> Sent: Thursday, November 06, 2003 1:39 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: ** SQL WHERE clause order
>
>
>
> Thanks Raj and Naveen for your input. However my SQL has a
> union clause
> and I want it to be executed whether select_sen_emp_chk_first
> is Y/N. I
> tried the ORDER_PREDICATES hint suggested by Yong but do not
> know how to
> get it to work. Basically from the explain plan how can we
> tell when the
> variables are being checked. :
>
>
>
> SELECT emp_id FROM emp
> WHERE :select_sen_emp_chk_first = 'Y'
> AND dept = :dept
> AND salary > :min_sal
> UNION
> SELECT emp_id FROM emp
> WHERE :select_sen_emp_chk_first = 'N'
> AND dept != :dept
> AND salary < :min_sal
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Naveen, Nahata (IE10)
  INET: Naveen.Nahata_at_honeywell.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Nov 06 2003 - 00:14:27 CST