Solved

Using a variable in select IN statement ORALCE 11 G

Posted on 2013-05-15
9
321 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
  • 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 76

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
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 76

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 76

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 76

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

896 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now