Re: PASSING MULTI-VALUE IN REPORT.

HI..
[Quoted] 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 Mon Aug 14 2000 - 13:10:19 CEST