Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Need help for IN operator in Oracle

Need help for IN operator in Oracle

From: <sangu_rao_at_yahoo.co.in>
Date: 14 Dec 2006 05:09:38 -0800
Message-ID: <1166101778.695012.80520@t46g2000cwa.googlegroups.com>


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 Received on Thu Dec 14 2006 - 07:09:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US