Home » SQL & PL/SQL » SQL & PL/SQL » The IN function (8i)
The IN function [message #440631] Tue, 26 January 2010 06:40 Go to next message
Derek N
Messages: 80
Registered: September 2002
Member
hello

I have the following SQL query:
select * from table_a
where cat_type in (select a_col from table_b where rownum =1);

The value in a_col (which is a varchar2 field) is ('1,'2','3',4'). The brackets are also stored in this field.

Field cat_type from table_a varies from 1 to 4. I want to select from table_a where cat_type in 1,2,3,4.

The select does not return any values. Any way around this?
Re: The IN function [message #440632 is a reply to message #440631] Tue, 26 January 2010 06:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to split the value in a_col.
You will find a query for it at
http://www.orafaq.com/forum/m/285231/102589/?#msg_285231

Regards
Michel
Re: The IN function [message #440633 is a reply to message #440631] Tue, 26 January 2010 06:47 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:210612357425

Though I would question the wisdom of storing data this way. Variable in list is really for when you get stuff from outside the database and you don't want to strip it down to it's components before using it. If you're storing the data in the db you should probably break it down before storing it - it'll likely be easier in the long run.
Re: The IN function [message #440637 is a reply to message #440631] Tue, 26 January 2010 06:57 Go to previous message
Derek N
Messages: 80
Registered: September 2002
Member
Thanks for the info guys. Much appreciated.
Previous Topic: How can I restrict a table For only One Transaction
Next Topic: Pivot Style Query
Goto Forum:
  


Current Time: Sat Dec 03 18:03:31 CST 2016

Total time taken to generate the page: 0.15217 seconds