Re: Oracle report 3.0 problem

Howdy,

I had a report that filters records between user selected start (:p_stdt) and end (:p_endt) dates.

    (Parameters are user defined :p_where_clause default "1=1", dates default to include 1982 - 2020)

    The query in the Data Layout used a lexical: WHERE &p_where_clause

In theAFTER_PARAMETER_FORM trigger, I added this code :p_where_clause := '(Trunc(a.ONSETDT) BETWEEN TO_DATE(''' || TO_CHAR(:p_stdt,'DD-MON-YYYY') ||
  ''',''DD-MON-YYYY'') AND TO_DATE(''' ||TO_CHAR(:p_endt,'DD-MON-YYYY') || ''',''DD-MON-YYYY'')) '; I also added a one-liner to this trigger to set the :DESTYPE of the report.

    By adding a Parm that only included the Destinations I wanted,     I could hide other destinations from the user.   :DESTYPE := :p_Destin;
  --srw.message(100, :DESTYPE);

Hth
Rich

Ken Halsted wrote:

> Munish,
>
> I hope I understand correctly what your problem is but it sounds like you
> are trying to use BIND variables in your Parameters LIST OF VALUES? Is that
> it?
>
> If so, you can NOT use bind variable there. I know it stinks but you can't
> do it. I'm sure there are work-arounds like using a stored procedure to
> return the value. Notice you can use things like SELECT SYSDATE FROM DUAL
> for Date parameters or you can even use the RTRIM function, etc. If you
> must, you can even create a form using FORMS 4/5 and then call the Report
> using Lexical References. Search for Lexical in Reports help.
>
> Maybe in the next version of Reports we can use Bind variables! Are you
> listening Oracle?
>
> I hope this helps.
>
> Ken Halsted
> kenman_at_mail.snider.net
>
> <amitabh12_at_my-dejanews.com> wrote in message
> news:7eav1s$4vl$1_at_nnrp1.dejanews.com...
> > Hello, I am using Oracle Reports 3.x. The situation is that I have no
> query
> > build. I am just using a formula column. I want to create two parameters.
> > First one is called year (select year from this_year); Second one is where
> > the problem is.. When I do (select ssno from emp_data where emp_year =
> :year)
> > oracle reports return the following error..
> >
> > REP-0781 :Bind variables are not allowed in Select Statement.
> >
> > Please Help.
> >
> > Thanks
> > Munish
> >
> > -----------== Posted via Deja News, The Discussion Network ==----------
> > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Apr 08 1999 - 15:27:53 CEST