Re: where JOB_ID IN (:JOB_ID)

Thanks for shedding some light on this issue.

So :JOB_ID was treated as one value not a comma separated values. (I wish there was a support for touple)

I am getting this parameter from an application and the application may select any number of values. Looks like JOB_ID IN (:JOB_ID) is not going to work.

Since :JOB_ID is a single value, this might work too:

select * from HR.EMPLOYEES where instr(:JOB_ID,JOB_ID)>0

Thanks

Brian Peasland wrote:
> probashi wrote:
> > Hi,
> >
> > When I run q1 I get back 10 rows but when I run q2 (whit supplying
> > "'IT_PROG', 'FI_ACCOUNT'" in the parameter) I got zero records
> > back.
> >
> > Can any body explain this!!!.
> >
> >
> > --q1:
> > select * from HR.EMPLOYEES where JOB_ID IN ('IT_PROG', 'FI_ACCOUNT')
> >
> > --q2
> > select * from HR.EMPLOYEES where JOB_ID IN (:JOB_ID)
> >
> > Thanks
> >
>
> In Q2, ":JOB_ID" is a bind variable. This bind variable can only be used
> to return one value in the IN clause. If you need two values in the IN
> clause (as shown in Q1), then you need to have 2 bind variables, holding
> the two different values you are searching for.
>
> HTH,
> Brian
>
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_nospam.peasland.net
> http://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown
Received on Tue Sep 05 2006 - 15:50:33 CDT