Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    25

    Unanswered: ORA - where clause using multiple values

    Hi I am using a stored procedure and selecting some values based on a parameter as below

    VAR_AA VARCHAR2(10);

    VAR_AA := 'AAA'

    I then use this variable in a where clause as below

    select * from table_name where ext_id in (VAR_AA)

    I want to give VAR_AA multiple values however not use multiple variables something like below

    VAR_AA := ' 'AAA', 'BBB', 'CCC' '

    How can I achieve this? thanks

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    recent post on this:

    http://www.dbforums.com/oracle/16373...statement.html

    I like the idea of a GTT (Global Temporary Table) just for stuff like this, but the AskTom method get's a lot of internet press.

    --=cf

  3. #3
    Join Date
    Feb 2009
    Posts
    36
    u can make use of IN function instead of stored procdure
    syntax of IN function is as follow:

    SELECT columns
    FROM tables
    WHERE column1 in (value1, value2, .... value_n);

    e.g
    SELECT *
    FROM suppliers
    WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');


    hope it will help u.do reply me if it works...

  4. #4
    Join Date
    Feb 2009
    Posts
    62
    If you're on 10g or higher, you can use regular expressions. Simply pipe delimit your string instead of comma delimiting them, and use regexp_like:
    Code:
    create table test_121 (col_1  varchar2(30));
    
    insert into test_121 values ('CORSA');
    insert into test_121 values ('ASTRA');
    insert into test_121 values ('VECTRA');
    insert into test_121 values ('SIGMA');
    insert into test_121 values ('ZAPHIRA');
    
    select * from test_121
    where regexp_like(col_1,'^'||'CORSA|VECTRA|SIGMA'||'$');

  5. #5
    Join Date
    Feb 2009
    Posts
    25
    The regular expressions was the easiest to implement thanks JRowbottom

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    "Easiest to implement" and "best" aren't necessarily the same ...

    Code:
    create index ix1 on test_121(col_1);
    -----------------------------------------------
    select * from test_121
    where regexp_like(col_1,'^'||'CORSA|VECTRA|SIGMA'||'$');
    
    SELECT STATEMENT  ALL_ROWSCost: 3  Bytes: 51  Cardinality: 3  	
    	1 TABLE ACCESS FULL TABLE FORBESC.TEST_121 Cost: 3  Bytes: 51  Cardinality: 3  
    -----------------------------------------------
    select * from test_121
    where col_1 in ('CORSA','VECTRA','SIGMA');
    
    SELECT STATEMENT  ALL_ROWSCost: 1  Bytes: 51  Cardinality: 3  		
    	2 INLIST ITERATOR  	
    		1 INDEX RANGE SCAN INDEX FORBESC.IX1 Cost: 1  Bytes: 51  Cardinality: 3 
    -----------------------------------------------
    create global temporary table hold_vals (val varchar2(10)) on commit delete rows;
    
    insert into hold_vals values ('CORSA');
    insert into hold_vals values ('VECTRA');
    insert into hold_vals values ('SIGMA');
    
    select * from test_121
    where col_1 in (select val from hold_vals);
    
    SELECT STATEMENT  ALL_ROWSCost: 3  Bytes: 72  Cardinality: 3  			
    	4 NESTED LOOPS  Cost: 3  Bytes: 72  Cardinality: 3  		
    		2 SORT UNIQUE  Cost: 2  Bytes: 21  Cardinality: 3  	
    			1 TABLE ACCESS FULL TABLE (TEMP) FORBESC.HOLD_VALS Cost: 2  Bytes: 21  Cardinality: 3  
    		3 INDEX RANGE SCAN INDEX FORBESC.IX1 Cost: 0  Bytes: 17  Cardinality: 1
    The REGEXP approach doesn't use an index, if you've got an index on that column.

    BTW, that use of REGEXP is pretty dang cool ... never thought of using it like that before.
    --=cf
    Last edited by chuck_forbes; 02-26-09 at 16:44.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by JRowbottom
    Code:
    select * from test_121
    where regexp_like(col_1,'^'||'CORSA|VECTRA|SIGMA'||'$');
    Or for "the rest of us" :

    Code:
    select * from test_121
    where instr('|CORSA|VECTRA|SIGMA|', '|'||col_1||'|') > 0;
    or
    Code:
    select * from test_121
    where '|CORSA|VECTRA|SIGMA|' like '%|'||col_1||'|%';
    Again, neither of these will use an index on col_1 (most likely).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •