Re: Conditional Where clause in stored proc

This doesn't really solve the problem - each possible version of the SQL statement is still coded explicitly. There are other parts of my app where the query is based on 5 or more user-entered criteria in combination - hence I would need LOTS of seperate queries.

Thanks for your time though... I'm off to look at the DBMS_SQL package.

In article <37a76f8c.101660259_at_newshost.us.oracle.com>,   tkyte_at_us.oracle.com wrote:
>
> 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
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 02 1999 - 06:19:14 CDT