oracle sql -- "IN" statement ?

Posted on 2012-08-30
Last Modified: 2012-09-02
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)
Question by:finance_teacher
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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:

    What programming language are you using?
    LVL 73

    Assisted Solution

    it would be easier if they entered

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

    where str2tbl function and supporting type can be found here
    LVL 36

    Assisted Solution

    by:Geert Gruwez
    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 ;
    LVL 51

    Expert Comment

    or use dynamic sql

    execute immediate someSQLString;
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    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,
      	multiset(select level from dual connect by level <= ((length(mystring)-length(replace(mystring,','))))+1)
      	as sys.odcivarchar2list

    Open in new window

    LVL 142

    Accepted Solution


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    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…

    731 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

    15 Experts available now in Live!

    Get 1:1 Help Now