Re: Report performance

[Daniel Vandenberg <vandberg@VAXA.CIS.UWOSH.EDU>]

The string does not work because the string includes the quotes, so it does
not match location in the database, which does not contain the quotes.  The
following statement would give the same results as the literal.

let $locations='4632'

If you are just selecting one location, your SQL should say location =
$locations instead of location in $locations

If you want $locations to be able to contain a list of several codes you
could have the following:

let $locations ='(''4632'', ''4633'', ''4634'')'

begin-select
...
from...
where...
and location in [$locations]

However, if you put $locations in brackets like this, the SQL will contain
the literals instead of a bind variable like :1, so it will be parsed every
time.

At 10:56 AM 7/10/2002 -0400, you wrote:
>This is a follow on to a previous message.  I have a large number of
>reports which are generated for which the query is identical except for a
>location, so I am trying to parameterize the query so the Oracle parser
>will only parse it once.  The skeleton of the query is like this:
>
>begin-select
>...
>from ...
>where....
>and location in $locations
>
>By doing it this way, the SQL statement ends up with a :1 where $locations
>is and each time the query is executed after the first time, it wont need
>to be reparsed since Oracle will see it as the same query.
>
>But I dont understand why this wont work.  I even tried hardcoding it like
>this:
>
>let $locations='''4632'''
>print $locations (+1,1)
>..
>..
>begin-select
>...
>from ...
>where....
>and location in $locations
>
>The print $locations confirms $locations does indeed contain  '4632'
>
>But what blows me away is that the following (which seems identical) DOES
>WORK - can anyone explain why???
>
>begin-select
>...
>from ...
>where....
>and location in '4632'
>
>Why does it work with the literal but not the string?  Thanks for any
>insight.


--------------------------------------------------------------------------------
Daniel Vandenberg                    |  Email :  vandberg@uwosh.edu
Applications Programming
University of Wisconsin Oshkosh