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:[EMAIL PROTECTED]
> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
