Re: PASSING MULTI-VALUE IN REPORT.

the value passed from the form must be in the in clause format for the lexical sql in clause to work
ie: 'D100','D200' not 'D100' and 'D200'
The first way will work within reports just fine

I create the list of elements in forms using whatever method required and pass that value (exactly as above) to reports

"peter" <misybh_at_tpts7.seed.net.tw> wrote in message news:8nbkss$gfm_at_netnews.hinet.net...
> HI..
> MY SQL IS AS FOLLOWING
> Select col1 ,col2 from table1
> where eno='23%' & where_string
> I set both variable 1. where_string 2. A in user-parameter (char type 100)
> before report trigger
> :where_string:='AND DEPNO IN (' || :A ||')'.
> :A inital value is 'D1200'
> I pass parameter A from form into preort...
> so :A may be 'D1200' and 'D1300'...
> a strang thing happened..
> I just only can pass such string D1200 and D1300 (I want 'D1200' and
> 'D1300')
> the single qoutes was gone.. and the report cant not parse the SQL...
> why?
> so I try this way ... ''''''||D1200||''''''||','||''''''||D1300||'''''',
> I see A from parameter window I get :A is 'D1200' and 'D1300' and report
> runs ok..
> why should I add single qoutes 6 times in front and back of the D1200
> ???????
>
>
>
>
>
> Jerry Alan Braga <jabraga_at_golden.net> wrote in message
> news:8nbgk1$ott$1_at_cougar.golden.net...
> > when you create the select statement the parser will see the & and
 create
> > the variable to the right of the &
> >
> > This variable should be of varchar2 type with a length long enough for
 the
> > clause
> >
> > 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 Wed Aug 16 2000 - 23:06:36 CEST