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]

You can allways ask tom at:

http://asktom.oracle.com/pls/ask/f?p...950_P8_DISPLA-
YID,F4950_P8_CR ITERIA:11061234 8061,
--
Posted via http://dbforums.com

[Jim Kennedy]

"Lisa S." <lj******@vac-acc.gc.ca> wrote in message
news:ba******** *************** ***@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.]

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

"Lisa S." <lj******@vac-acc.gc.ca> wrote in message
news:ba******** *************** ***@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]

"Lisa S." <lj******@vac-acc.gc.ca> wrote in message
news:ba******** *************** ***@posting.goo gle.com...

"Jim Kennedy" <kennedy-down_with_spamm ers@no_spam.com cast.net> wrote in

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

"Lisa S." <lj******@vac-acc.gc.ca> wrote in message
news:ba******** *************** ***@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