Re: where JOB_ID IN (:JOB_ID)

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

Your second syntax is invalid. What Oracle will look for is a single value in the column that looks like the full string including the comma and the space.

If you want to use this type of construct you must cast the variable as a table. See the example in Morgan's Library (www.psoug.org) under CONDITIONS titled "COMPLEX IN DEMO."

Be forwarned that one part of the demo, duplicates what you did and is intended to fail. The next example shows how to do it correctly.

-- 
Daniel Morgan
Puget Sound Oracle Users Group