Problems with "IN"

[Lisa S.]

Can someeone help! This works:

SELECT * FROM sometable
WHERE 1 IN (1, 2, 3) ;

But if I want something like this:

s_mylist VARCHAR2(20) := '1, 2, 3';

SELECT * FROM sometable
WHERE 1 IN ( s_mylist ) ;

It doesn't work. Can't I do this?

Thanks
Lisa

[LKBrwn_DBA]

Re: Problems with "IN&quo t;


You can allways ask tom at:

http://asktom.oracle.com/pls/ask/f?p=4950:8:839356::NO::F4950_P8_DISPLA-

YID,F4950_P8_CR ITERIA:11061234 8061,


--
Posted via http://dbforums.com

[Jim Kennedy]

Re: Problems with "IN&quo t;

"Lisa S." <ljstewar@vac-acc.gc.cawrote in message
news:ba0b4810.0 310150916.67bf4 2e9@posting.goo gle.com...

Can someeone help! This works:
>
SELECT * FROM sometable
WHERE 1 IN (1, 2, 3) ;
>
But if I want something like this:
>
s_mylist VARCHAR2(20) := '1, 2, 3';
>
SELECT * FROM sometable
WHERE 1 IN ( s_mylist ) ;
>
It doesn't work. Can't I do this?
>
Thanks
Lisa

It does not work because s_mylist is one thing the string '1, 2, 3' instead
of
where 1 in (1,2,3)
Also you are comparing numbers and strings - not good. compare numbers and
numbers.
Jim

[Lisa S.]

Re: Problems with &quot;IN&quo t;

"Jim Kennedy" <kennedy-down_with_spamm ers@no_spam.com cast.netwrote in message news:<WKmjb.782 313$Ho3.208508@ sccrnsc03>...

"Lisa S." <ljstewar@vac-acc.gc.cawrote in message
news:ba0b4810.0 310150916.67bf4 2e9@posting.goo gle.com...

Can someeone help! This works:

SELECT * FROM sometable
WHERE 1 IN (1, 2, 3) ;

But if I want something like this:

s_mylist VARCHAR2(20) := '1, 2, 3';

SELECT * FROM sometable
WHERE 1 IN ( s_mylist ) ;

It doesn't work. Can't I do this?

Thanks
Lisa

>
It does not work because s_mylist is one thing the string '1, 2, 3' instead
of
where 1 in (1,2,3)
Also you are comparing numbers and strings - not good. compare numbers and
numbers.
Jim

Hi Jim

Sorry, I guess I wasn't clear - What we are trying to do is to format
a string with the 'IN' items to check elsewhere and pass it into the
stored procedure. I guess my question is - can't I use a variable with
the 'IN' Operator ?
Thanks
Lisa

[Jim Kennedy]

Re: Problems with &quot;IN&quo t;


"Lisa S." <ljstewar@vac-acc.gc.cawrote in message
news:ba0b4810.0 310160441.71768 833@posting.goo gle.com...

"Jim Kennedy" <kennedy-down_with_spamm ers@no_spam.com cast.netwrote in

message news:<WKmjb.782 313$Ho3.208508@ sccrnsc03>...

"Lisa S." <ljstewar@vac-acc.gc.cawrote in message
news:ba0b4810.0 310150916.67bf4 2e9@posting.goo gle.com...

Can someeone help! This works:
>
SELECT * FROM sometable
WHERE 1 IN (1, 2, 3) ;
>
But if I want something like this:
>
s_mylist VARCHAR2(20) := '1, 2, 3';
>
SELECT * FROM sometable
WHERE 1 IN ( s_mylist ) ;
>
It doesn't work. Can't I do this?
>
Thanks
Lisa

It does not work because s_mylist is one thing the string '1, 2, 3'

instead

of
where 1 in (1,2,3)
Also you are comparing numbers and strings - not good. compare numbers

and

numbers.
Jim

>
Hi Jim
>
Sorry, I guess I wasn't clear - What we are trying to do is to format
a string with the 'IN' items to check elsewhere and pass it into the
stored procedure. I guess my question is - can't I use a variable with
the 'IN' Operator ?
Thanks
Lisa

Not that way. go to asktom.oracle.c om and do a search. You will find
something there that will help you.
Jim