Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

Using a variable in select IN statement ORALCE 11 G

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
cookiejar
Asked:
cookiejar
  • 4
  • 2
  • 2
  • +1
1 Solution
 
mohammadzahidCommented:
Define variables at the session level and your SQL Statement should work.
0
 
mohammadzahidCommented:
If not then define them in PL/SQL Block, assign values - that's the easiest way.
0
 
slightwv (䄆 Netminder) Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sdstuberCommented:
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
 
cookiejarAuthor Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
>>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
 
cookiejarAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now