Re: Parameter Passing from Forms to Reports

I have had a similar problem in the past When i passed a string like 'A','B','C' , when it got into reports it looked like A','B',C , missing off the first and last '. Also, usage of the IN operator with the parameter failed.

As a last resort, i had to do my processing in a 'Before-Report' trigger in the report, fill up a temp table and in the main report query, use that temp table to get your results.

Alternatively, store all your parameters in a temp table, one with your parameter and one with a unique identifier (uid for example?) and use this table in your report.

e.g
Create table temp_params

    (parameter        varchar2(100),
     unique_id         number)

and in the main body of your report
                    ..
                    ..
                and  table.field = temp_params.parameter
                and temp_params.unique_id = uid
                    ..

Mark

dperry wrote in message <7cr7p7$pmd$1_at_nnrp1.dejanews.com>...
>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
Received on Fri Mar 19 1999 - 10:38:43 CET