Re: Conditional Where clause in stored proc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 29 Jul 1999 15:15:56 GMT
Message-ID: <37a76f8c.101660259@newshost.us.oracle.com>

On Thu, 29 Jul 1999 15:12:02 GMT, you wrote:

>use the DBMS_SQL package to define your SQL at runtime.
>

another way would be ref cursors.

is

   type refCur is ref cursor;

   l_rec        t%rowtype;
   l_cursor     refCur;

begin

   if ( parameter = value1 ) then

       open l_cursor for select * from T where complete_by_date <= sysdate;    elsif ( parameter = value2 ) then

       open l_cursor for select * from T where complete_by_ate <= sysdate+7;    else

       open l_cursor for select * from T 
                          where months_between(complete_by_date,sysdate) <= 1;

   end if;

   loop

       fetch l_cursor into l_rec;   
       exit when l_cursor%notfound; 
       ...

   end loop;
   close l_cursor;
end;

>bgeake_at_my-deja.com wrote:
>
>> How can a select procedure have its Where clause set conditionally
>> depending on a paramater value?
>>
>> Depending on the value, I need to use the clause "And complete_by_date
>> <= Sysdate" or "And complete_by_date <= Sysdate + 7" or "And
>> Months_Between(complete_by_date,Sysdate) <= 1".
>>
>> I've tried:
>> If parameter = value1 Then And complete_by_date <= Sysdate;
>> Elsif parameter = value2 Then And complete_by_date <= Sysdate + 7;
>> Elsif parameter = value3 Then And Months_Between
>> complete_by_date,Sysdate) <= 1;
>> End If;
>>
>> which gives lots of PLS-00103 errors. So what's the real way to do this?
>>
>> TIA,
>>
>> Bill.
>>
>> Sent via Deja.com http://www.deja.com/
>> Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 29 1999 - 10:15:56 CDT