Re: PASSING MULTI-VALUE IN REPORT.

From: peter <misybh_at_tpts7.seed.net.tw>
Date: Tue, 15 Aug 2000 22:35:52 +0800
Message-ID: <8nbkss$gfm_at_netnews.hinet.net>

HI..

[Quoted]        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 Tue Aug 15 2000 - 16:35:52 CEST