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 -> Re: Need help for IN operator in Oracle

Re: Need help for IN operator in Oracle

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 14 Dec 2006 08:39:38 -0800
Message-ID: <1166114377.354191@bubbleator.drizzle.com>


sangu_rao_at_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_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Dec 14 2006 - 10:39:38 CST

Original text of this message

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