Re: sql/plsql query question

From: roger <xrsr_at_rogerware.com>
Date: Tue, 04 Nov 2003 20:45:51 GMT
Message-ID: <Xns942981BBA21A1rsrrogerwarecom@204.127.199.17>

I've experimented, thusly:

     (p_target_value is null or
       t1.some_column in
         (select t2.some_column from some_other_table t2
            where some_other_column = log_access(p_target_value)))

The only change being the introduction of the function log_access to access the p_target_value parameter.
The log_access function simply writes a dbms_output message, and returns it's argument.

With this in place, it appears to me that the function is never called when p_target_value is null, which strongly suggests to me that the SQL is "doing the right thing" and not executing that portion of the query which is conditional on the runtime value of p_target_value.

As far as native dynamic SQL goes - my first reaction to this problem would have been to compose the query with dynamic SQL, either in the application itself, or with PL/SQL.

However, it seems to me that if this approach works as it appears to do, this is a better solution since there's no need to parse the dynamic query each time.

"nobody" <nobody_at_nowhere.com> wrote in news:RfSpb.12160$YN6.7969 @news02.bloor.is.net.cable.rogers.com:

> 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 - 14:45:51 CST