Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2005
    Location
    Atlanta, GA USA
    Posts
    20

    Question Unanswered: Elegant any(...) = any(...) solution?

    Hi folks,

    I need to write a query that compares three different columns to the same list of values. Unfortunately the values being compared may not exist in the database so a subquery is out of the question.

    Here's an example:
    Code:
    select * from the_table where
        col_1 in (1,2,3) or
        col_2 in (1,2,3) or
        col_3 in (1,2,3)
    This accomplishes what I want, but it's kinda ugly, and the framework I'm working in would require me to bind each value 3 separate times.

    Does anyone have a simple solution that would require me to only specify the list once? I was hoping for something like this, although it obviously doesn't work.
    Code:
    select * from the_table where any(col_1,col_2,col_3) = any(1,2,3)
    Thanks,
    Philip

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    How about a temporary table where you can store those values session or transaction especific ? Have you thought about that ?

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Or you can have the list pipelined back to you as rows from a collection and then you just join to it. Here I used this guy technique to show you how. You can find other ways on doing this over AskTom (search for 'variable in list').
    Code:
    SQL> select * from t;
    
            ID      COL_1      COL_2      COL_3
    ---------- ---------- ---------- ----------
             1          1          0          0
             2          0          2          0
             3          0          0          3
             4          0          0          0
    
    SQL> select *
      2    from t
      3   where col_1 in ( 1, 2, 3 )
      4      or col_2 in ( 1, 2, 3 )
      5      or col_3 in ( 1, 2, 3 )
      6  /
    
            ID      COL_1      COL_2      COL_3
    ---------- ---------- ---------- ----------
             1          1          0          0
             2          0          2          0
             3          0          0          3
    
    SQL>
    SQL> variable in_list varchar2(100)
    SQL>
    SQL> exec :in_list := '1,2,3'
    
    PL/SQL procedure successfully completed.
    
    SQL> select t.*
      2    from t inner join ( select items.extract('/l/text()').getStringVal() item
      3                          from table( xmlSequence( extract( XMLType( '<all><l>' || replace( :in_list,
      4                                                                                            ',',
      5                                                                                            '</l><l>'
      6                                                                                          ) || '</l></all>' ), '/all/l' ) ) ) items ) x
      7      on ( t.col_1 = x.item
      8            or t.col_2 = x.item
      9             or t.col_3 = x.item )
     10  /
    
            ID      COL_1      COL_2      COL_3
    ---------- ---------- ---------- ----------
             3          0          0          3
             2          0          2          0
             1          1          0          0
    
    SQL> exec :in_list := '1,2'
    
    PL/SQL procedure successfully completed.
    
    SQL> /
    
            ID      COL_1      COL_2      COL_3
    ---------- ---------- ---------- ----------
             2          0          2          0
             1          1          0          0
    
    SQL> select *
      2    from t
      3   where col_1 in ( 1, 2 )
      4      or col_2 in ( 1, 2 )
      5      or col_3 in ( 1, 2 )
      6  /
    
            ID      COL_1      COL_2      COL_3
    ---------- ---------- ---------- ----------
             1          1          0          0
             2          0          2          0
    
    SQL>

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Try
    Code:
    select * 
    from t, (select 1 as refval from dual union all select 2 from dual union all select 3 from dual) iv
    where t.col1 = iv.refval or         
          t.col2 = iv.refval or
          t.col3 = iv.refval
    
    
            ID       COL1       COL2       COL3     REFVAL
    ---------- ---------- ---------- ---------- ----------
             1          1          0          0          1
             2          0          2          0          2
             3          0          0          3          3
    
    3 rows selected.
    -cf

Posting Permissions

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