Re: PASSING MULTI-VALUE IN REPORT.

[Quoted] when you create the select statement the parser will see the & and create [Quoted] the variable to the right of the &

This variable should be of varchar2 type with a length long enough for the clause

[Quoted] Look at the help for lexical references

"peter" <misybh_at_tpts7.seed.net.tw> wrote in message news:8n8kf8$pob_at_netnews.hinet.net...
> HI..
> WHERE TO CREATE THIS VARIABLE dept_clause?????
>
> Jerry Alan Braga <jabraga_at_golden.net> wrote in message
> news:8n7g7l$kpt$1_at_cougar.golden.net...
> > use the lexical operator in the query
> > ie:
> >
> > select empno, ename from emp
> > where empno <> 0 &dept_clause
> >
> > dept_clause in turn is a varchar2 variable that you create inside report
> > builder
> > its structure will be 'and depit in (' || p_deptid || ')'. The and must
 be
> > part of the lexical variable you cannot say
> > and &dept_clause, report builder will return a query error.
> >
> > p_deptid is a text parameter that you pass from lets say from builder.
 Its
> > structure will be the actual values separated by the comman eg 1,2,3
> >
> > this variable is then placed inside the dept_clause variable and
 dept_clause
> > is then translated at runtime with the values required. The reason I do
 it
> > this way is that I pass on the data and let the report build the actual
> > command string. This is better is you start to alias the tables and
 want
 to
> > make your report independant of any calling mechanism.
> >
> > "peter" <misybh_at_tpts7.seed.net.tw> wrote in message
> > news:8n5nt1$l39_at_netnews.hinet.net...
> > > What I want to do is Accept a parameter p_parm1 with multiplecomma
 delimited
> > > entries i.e p_parm1 = value1,value2,value3 . then pass it to the
 report
> > > query i.e
> > >
> > > select col1 ,col2,col3 from table1 where col1 in
> > > ('value1','value2','value3');
> > >
> > > Remember col1 is datatype varchar2 . So I need to add the single
 qoutes
 and
> > > the brackets before the execution of the report .Now I already have
 managed
> > > to achieve that . But still the query does not seem to execute
 properly
 .
> > > i.e does not return any rows.
> > >
> > > Q. Is there any way I can get the whole Query into a variable and
 display
 it
> > > at runtime before the query gets executed ?
> > > Q. Is there any Return variable which will help me in identifying the
 eror
> > > after the report gets executed . ex sqlcode
> > >
> > > Any other suggestions to the problem . I have already got a workaround
 of
> > > adding the where clause dynamically bfore excuting the query . But
 that
> > > seems to be a very tedious and stupid solution to a fairly simple
 problem
 .
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Tue Aug 15 2000 - 15:29:44 CEST