Re: Parameter Passing from Forms to Reports

From: RichardGrant <RichardGrant_at_Capgemini.co.uk>
Date: Fri, 19 Mar 1999 12:04:49 +0000
Message-ID: <36F23D61.592D_at_Capgemini.co.uk>


dperry wrote:
>
> Can someone help me out with this problem? I am using Oracle Forms 5.0 and
> Oracle Reports 3.0. I have had success in passing parameters from forms to
> reports, using the user parameters in my where clause for the default query
> in Reports.. However, now I need to allow the user to select multiple values
> and pass them as a string into one parameter, thus using that parameter in
> the where clause (in an IN statement).. For example, my default query looks
> like this:
>
> select * from a_acgo
> where ac_podada >= :beg_date and ac_podada <= :end_date
> and ac_msn in (select chm_msn
> from a_chnl_msn
> where chm_chnl in (:chnl));
>
> :chnl is a text string that I have concatenated from the values in an LOV that
> is populated by the user at runtime... So the data that is passed looks like
> this:
>
> NGUBAH','BAHRAN','NGUSIZ
>
> I purposely left off the first and last quotes, because reports will put
> quotes around the parameter when I use it in the query. So that the query
> SHOULD look like this when populated with the parameters:
>
> select * from a_acgo
> where ac_podada >= '01-JAN-99' and ac_podada <= '01-MAR-99'
> and ac_msn in (select chm_msn
> from a_chnl_msn
> where chm_chnl in ('NGUBAH','BAHRAN','NGUSIZ'));
>
> This doesn't work... I ran the same query at the SQL prompt and it works
> with no problems, but the report doesn't return any values... I have tried
> using the (' ') in the parameter, but that would add quotes around the ()...
> I have exhausted every approach that I knew of... Does anybody know if you
> can use the IN clause with passed parameters? I could use a parameter for
> each channel, but that would mean that i would have to limit the number of
> channels that they can choose, and i'd rather not do that... In forms, there
> are system variables that I can access with the debugger that tell me what
> the last query looked like when it ran, but i don't see any system variables
> in the debugger with reports... If I could see the query after it is
> populated, I could isolate the problem... Can somebody help me with this....
>
> Thanks for your help.. I have spent way too much time trying to figure this
> one out.....
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
You could use a lexical parameter rather than a bind parameter and pass [Quoted] in the whole subquery part of the select statement EG Select * from emp where emp_no in (&parameter)

I haven't tried this, but it should work. Received on Fri Mar 19 1999 - 13:04:49 CET

Original text of this message