Re: sql/plsql query question

My question is if this a useful/valid/correct/efficient
> approach to handling cases where there are various optional
> query parameters that may or may not be specified?
>

Nope, try native dynamic sql.

.e. is SQL smart enough to detect that there is no need
> to do the sub select on t2 based on the runtime value of
> [tthe PL/SQL variable?

No.

"roger" <xrsr_at_rogerware.com> wrote in message news:Xns94295B328E850rsrrogerwarecom_at_216.148.227.77...
>
> Please consider the following psuedo procedure:
>
> procedure foo (p_target_value int)
> is
> begin
> select bla from table1 t1
> where
> (p_target_value is null or
> t1.some_column in
> (select t2.some_column from some_other_table t2
> where some_other_column = p_target_value))
> order by whatever;
> end;
>
> Consider that there could be several such parameters,
> each with a similar clause anded to the query.
>
> My question is if this a useful/valid/correct/efficient
> approach to handling cases where there are various optional
> query parameters that may or may not be specified?
>
> i.e. is SQL smart enough to detect that there is no need
> to do the sub select on t2 based on the runtime value of
> [tthe PL/SQL variable?
>
> thanks
>
>
>
>
>
>
Received on Tue Nov 04 2003 - 12:31:13 CST