Hi,
I need some help in using the IN operator with Oracle 9i. The issue is
I will get a data in variable (Lets say the variable is "A") with this
below foramt

'var1','var2','var3'


The word is a combination of 3 words where each word enclosed in a
single quotes and seperated by a comma operator. This is how i will get
the string to my SP. Now i have to seach a table to see whether the
column content of a table matches with the any of the words ( i.e.
var1, var2 or var3) in the above string. Then my query looks like

SELECT * FROM TEMP WEHRE Col IN A;

The above query will treat the string 'var1','var2','var3' as a one
single string not as a combination of words. So my requirement won't be
solved with the above query.

I am able to retireve the data correctly in SQL Server by using the
late binding concept. The code for that

declare @b varchar(20)
declare @sql nvarchar(200)
set @b = '''var1'',''var2'',''var3'''
set @sql = 'select * from temp where a in ('+ @b +')'
exec sp_executesql @sql

My temp table contains 2 values : var1 and var2

The above query returns me var1 and var2 from Temp table.

Any idea how can get the same effect in Oracle 9i. I am very much
thankfull for your suggestion as we are nearing to release it is very
urgent for me.

Thanks in advance
Rao

On Dec 14, 2:09 pm, sangu_...@yahoo.co.in wrote:
> Hi,
> I need some help in using the IN operator with Oracle 9i. The issue is
> I will get a data in variable (Lets say the variable is "A") with this
> below foramt
>
> 'var1','var2','var3'
>
> The word is a combination of 3 words where each word enclosed in a
> single quotes and seperated by a comma operator. This is how i will get
> the string to my SP. Now i have to seach a table to see whether the
> column content of a table matches with the any of the words ( i.e.
> var1, var2 or var3) in the above string. Then my query looks like
>
> SELECT * FROM TEMP WEHRE Col IN A;
>
> The above query will treat the string 'var1','var2','var3' as a one
> single string not as a combination of words. So my requirement won't be
> solved with the above query.
>
> I am able to retireve the data correctly in SQL Server by using the
> late binding concept. The code for that
>
> declare @b varchar(20)
> declare @sql nvarchar(200)
> set @b = '''var1'',''var2'',''var3'''
> set @sql = 'select * from temp where a in ('+ @b +')'
> exec sp_executesql @sql
>
> My temp table contains 2 values : var1 and var2
>
> The above query returns me var1 and var2 from Temp table.
>
> Any idea how can get the same effect in Oracle 9i. I am very much
> thankfull for your suggestion as we are nearing to release it is very
> urgent for me.
>
> Thanks in advance
> Rao

http://asktom.oracle.com, search for 'dynamic in list'.
The above code is just another example why sqlserver is utter crap, and
people brainwashed by sqlserver should stop 'porting' their bad habits
to Oracle.
The approach above is utterly unscalable in Oracle.

--
Sybrand Bakker
Senior Oracle DBA

sangu_rao@yahoo.co.in wrote:
> Hi,
> I need some help in using the IN operator with Oracle 9i. The issue is
> I will get a data in variable (Lets say the variable is "A") with this
> below foramt
>
> 'var1','var2','var3'
>
>
> The word is a combination of 3 words where each word enclosed in a
> single quotes and seperated by a comma operator. This is how i will get
> the string to my SP. Now i have to seach a table to see whether the
> column content of a table matches with the any of the words ( i.e.
> var1, var2 or var3) in the above string. Then my query looks like
>
> SELECT * FROM TEMP WEHRE Col IN A;
>
> The above query will treat the string 'var1','var2','var3' as a one
> single string not as a combination of words. So my requirement won't be
> solved with the above query.
>
> I am able to retireve the data correctly in SQL Server by using the
> late binding concept. The code for that
>
> declare @b varchar(20)
> declare @sql nvarchar(200)
> set @b = '''var1'',''var2'',''var3'''
> set @sql = 'select * from temp where a in ('+ @b +')'
> exec sp_executesql @sql
>
> My temp table contains 2 values : var1 and var2
>
> The above query returns me var1 and var2 from Temp table.
>
> Any idea how can get the same effect in Oracle 9i. I am very much
> thankfull for your suggestion as we are nearing to release it is very
> urgent for me.
>
> Thanks in advance
> Rao

Go to Morgan's Library at www.psoug.org
click on Conditions
scroll down to COMPLEX IN DEMO.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

sybrandb wrote:
>
> http://asktom.oracle.com, search for 'dynamic in list'.
> The above code is just another example why sqlserver is utter crap

No. It's an example of a lame piece of code written by a SQL Server
developer who probably didn't know any better. The same poor solution
is no better or worse in Oracle than in SQL Server.

> people brainwashed by sqlserver should stop 'porting' their bad habits
> to Oracle.

I agree.

--
David Portas

The IN clause tells you if a value is in a list of elements.
'var1','var2','var3' is not a list of elements, but a string. So what
you actually want to know is if a value is a substring of that string:

SELECT * FROM TEMP
WHERE instr(:A,''''||Col||'''') > 0

-----------------------------------------------------

sangu_rao@yahoo.co.in wrote:

> Hi,
> I need some help in using the IN operator with Oracle 9i. The issue is
> I will get a data in variable (Lets say the variable is "A") with this
> below foramt
>
> 'var1','var2','var3'
>
>
> The word is a combination of 3 words where each word enclosed in a
> single quotes and seperated by a comma operator. This is how i will get
> the string to my SP. Now i have to seach a table to see whether the
> column content of a table matches with the any of the words ( i.e.
> var1, var2 or var3) in the above string. Then my query looks like
>
> SELECT * FROM TEMP WEHRE Col IN A;
>
> The above query will treat the string 'var1','var2','var3' as a one
> single string not as a combination of words. So my requirement won't be
> solved with the above query.
[snip]