?
Solved

Using a variable in select IN statement ORALCE 11 G

Posted on 2013-05-15
9
Medium Priority
?
330 Views
Last Modified: 2013-05-20
I am reading a value from a table that looks like this for example,

G_VALUES_TABLE
ID  CATEGORY
1   'apple','oranges','peaches'

SELECT CATEGORY INTO V_CATEGORY FROM G_VALUES_TABLES

   select col1, col2
        case    WILL HIS WORK
        when table1.  category in (V_CATEGORY ) THEN 1
        else
           0
        end CAT_FLG
      from table1
0
Comment
Question by:cookiejar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 39169877
Define variables at the session level and your SQL Statement should work.
0
 
LVL 11

Expert Comment

by:mohammadzahid
ID: 39169880
If not then define them in PL/SQL Block, assign values - that's the easiest way.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39169921
Just like your other question:
http://www.experts-exchange.com/Database/Oracle/Q_28121062.html

This is dynamic SQL.

Look up str2tbl.  It will take the string and convert it into something you can use.

If you can't find a good example check out:
http://tkyte.blogspot.com/2006/06/varying-in-lists.html
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 74

Expert Comment

by:sdstuber
ID: 39169926
I'm not entirely sure what you're trying to do.


is category column a nested table collection?

what is the bind variable supposed to be, a single value ? a collection?


what are the results you're trying to achieve?
0
 

Author Comment

by:cookiejar
ID: 39169985
>>If not then define them in PL/SQL Block, assign values - that's the easiest way.

I tried this and no data return for
select col1, col2
        case    WILL HIS WORK
        when table1.  category in (V_CATEGORY ) THEN 1
        else
           0
        end CAT_FLG
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39170001
>>I tried this and no data return for

Again, it is dynamic SQL.  You cannot do it that way.

You can define a cursor and use string concatenation but as sdtuber asked, we need to know what you are wanting to do.
0
 

Author Comment

by:cookiejar
ID: 39171759
There's a tablea

tablea
ValueList
'Apple','Oranges','Pears'

tableb
Types
Apple
Oranges
Peaches
Pears
I need to fetch the delimited comma string  from tablea 'Apple','Oranges','Pears'  and create a select statement
Select from tableb where types in tablea's valuelist
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39171766
>>and create a select statement

What are you wanting to do with the results of the select statement?
Will this be executed inside PL/SQL?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 39171823
Try this.  It is basically the same thing as the str2tbl function I mentioned above but does it all in a single statement

drop table tablea purge;
create table tablea (valuelist varchar2(30));

insert into tablea values('''Apple'',''Oranges'',''Pears''');

drop table tableb purge;
create table tableb (types varchar2(30));
insert into tableb values('Apple');
insert into tableb values('Oranges');
insert into tableb values('Peaches');
insert into tableb values('Pears');
commit;

with mydata as (
select valuelist mystring from tablea
)
select * from tableb where types in (
	select regexp_substr(mystring,'([^''][[:alnum:]]*[^''])',1,column_value) mystring
	from mydata,
 	table(
  	cast(
  	multiset(select level from dual connect by level <= ((length(mystring)-length(replace(mystring,','))))+1)
  	as sys.odcivarchar2list
  	)
  	)
)
/

Open in new window

0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question