[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

oracle sql -- "IN" statement ?

DOING BELOW HARDCODED value WORKS, BUT DOING VIA :STATUS PARAMETER and PASSING 'Initialized','Started','Approved','Completed' FAILS.
 
  How can I fix so users can manually enter 'Initialized','Started','Approved','Completed' ?
 
  select *
  from project
  where STATE in --(:Status)
  ('Initialized','Started','Approved','Completed')
0
finance_teacher
Asked:
finance_teacher
5 Solutions
 
slightwv (䄆 Netminder) Commented:
Hardcoded is a 'list'.  With a single parameter it is a comma seperated string.  Two different things.

You will need to make the parameter an array of some type.

Check out this thread:
https://forums.oracle.com/forums/thread.jspa?messageID=10446398

What programming language are you using?
0
 
sdstuberCommented:
it would be easier if they entered
'Initialized,Started,Approved,Completed'

select *
  from project
  where STATE in (select * from str2tbl(:status))


where str2tbl function and supporting type can be found here

http://www.experts-exchange.com/Database/Oracle/Q_26552023.html
0
 
Geert GruwezOracle dbaCommented:
using instr is also a way

select *
  from project
  where instr(';'||:state||';', ';'||STATE||';') > 0;


in the parameter you don't need the single quotes
:state = 'Initialized;Started;Approved'

or use , instead of the ;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
HainKurtSr. System AnalystCommented:
or use dynamic sql

execute immediate someSQLString;
0
 
slightwv (䄆 Netminder) Commented:
If you can change the input string, here is one similar to sdstuber's post but without creating a type and function.

with mydata as (
select :status mystring from dual
)
select *
  from project
  where STATE in (
	select rtrim(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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now