Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using a variable in select IN statement ORALCE 11 G

Posted on 2013-05-15
9
Medium Priority
?
334 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

604 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