Re: Report performance

["O'riordan, Paul (EI05)" <Paul.O'riordan@HONEYWELL.COM>]

You don't have any Brackets around the Locations
let $locations='''4632'''
This should read
let $locations='(' ||'"'||'4632'||'"'|| ')'
Hope this helps
        Paul


Honeywell

Paul O'Riordan                  Honeywell Application Services
Application Developer           Industrial Park, Cork Road
PeopleSoft Applications,                Waterford Ireland

                                                        +353-51-309856
Direct
                                +353-51-309821 Fax
                                +353-87-9083244 Mobile
mailto:Paul.O'Riordan@honeywell.com


-----Original Message-----
From: Brian Fitzpatrick [mailto:FITZPBM@KELLYSERVICES.COM]
Sent: 10 July 2002 15:56
To: SQR-USERS@list.iex.net
Subject: Report performance


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.