Solved

Using a variable in select IN statement ORALCE 11 G

Posted on 2013-05-15
9
328 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

707 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