7 Replies Latest reply: Jul 12, 2013 3:03 PM by Mike Kutz RSS

    dynamic select for in list

    user12845793

      Hi there,

       

        I have 2 tables:

       

      select * from table1: 

      FIELD1

      -----

      V1

      V2

      V3

       

      select * from table2: 

      FIELD2

      -----

      V1,V2,V3

       

      I want the following query has same return as select * from table1:

       

      select * from table1 where field1 in (select field2 from table2)

       

      But, I got 0 row return.

       

      Can you help me?

       

      Thanks,

        • 1. Re: dynamic select for in list
          Mike Kutz

          '0 rows' makes sense.

          The value in FIELD2 is treated as a single entry.  It would be the same as FIELD1='V1,V2,V3'.

          Reevaluate your schema design to prevent that in TABLE2.FIELD2.

           

          Beyond that, you'll have to 'expand' the list.

          There are multiple ways to do that.  Just do a search on 'dynamic IN list'.

          The more robust ones involve a pipelined function.

          • 2. Re: dynamic select for in list
            YoungDBA

            SQL> create table table1 (field1 varchar2(5)) tablespace users;

             

             

            Table created.

             

             

            SQL> create table table2 (field2 varchar2(5)) tablespace users;

             

             

            Table created.

             

             

            SQL> insert into table1 values('V1');

             

             

            1 row created.

             

             

            SQL> insert into table1 values('V2');

             

             

            1 row created.

             

             

            SQL> insert into table1 values('V3');

             

             

            1 row created.

             

             

            SQL> commit;

             

             

            Commit complete.

             

             

            SQL> insert into table2 values('V1');

             

             

            1 row created.

             

             

            SQL> insert into table2 values('V2');

             

             

            1 row created.

             

             

            SQL> insert into table2 values('V3');

             

             

            1 row created.

             

             

            SQL> commit;

             

             

            Commit complete.

             

             

            SQL> select * from table1;

             

             

            FIELD

            -----

            V1

            V2

            V3

             

             

            SQL> select * from table2;

             

             

            FIELD

            -----

            V1

            V2

            V3

             

             

            SQL> desc table1

                       Name                            Null?    Type

                       ------------------------------- -------- ----------------------------

                1      FIELD1                                   VARCHAR2(5)

             

             

            SQL> desc table2

                       Name                            Null?    Type

                       ------------------------------- -------- ----------------------------

                1      FIELD2                                   VARCHAR2(5)

             

             

            SQL> select * from table1 where field1 in (select field2 from table2);

             

             

            FIELD

            -----

            V1

            V2

            V3

             

             

            SQL>

            • 3. Re: dynamic select for in list
              YoungDBA

              Sorry misunderstood the question.  My apologies.

              • 4. Re: dynamic select for in list
                user12845793

                I understand this doesn't work, but I want to keep the same design and wondering if there is a way by adding '(', '' or change the value in the table from V1,V2,V3 to 'V1','V2','V3' or using some oracle functions to make it work.

                 

                Of course, if nothing work, I will use tradition design to fix this problem.

                 

                Thanks

                • 5. Re: dynamic select for in list
                  Brian Bontrager

                  You would have to use a function that returns a table, and inside that function use Dynamic SQL to build the query with the value from table2.  Setup, maintenance, and how you query the table function is more work.  I would consider whether It is less effort in the long run to correct the design.

                  • 6. Re: dynamic select for in list
                    pollywog

                    Like This?

                    WITH table1
                         AS (SELECT 'V1' field_one FROM DUAL
                             UNION ALL
                             SELECT 'V2' FROM DUAL
                             UNION ALL
                             SELECT 'V3' FROM DUAL
                             UNION ALL
                             SELECT 'V4' FROM DUAL
                             UNION ALL
                             SELECT 'V5' FROM DUAL),
                         table2 AS (SELECT 'V1,V2,V3,V5' field2 FROM DUAL),
                         table2_transform
                         AS (    SELECT REGEXP_SUBSTR (field2,
                                                       '[^\,]+',
                                                       1,
                                                       LEVEL)
                                           Field_one
                                   FROM table2
                             CONNECT BY LEVEL <= REGEXP_COUNT (field2, ',')+1
                            SELECT table1.*
                      FROM table1, table2_transform
                     WHERE table1.field_one = table2_transform.field_one
                    
                    FIELD_ONE
                    V1
                    V2
                    V3
                    V5
                    • 7. Re: dynamic select for in list
                      Mike Kutz

                      No need for dynamic SQL.

                      This is a job for a pipeline function that takes in a strongly typed reference cursor!!!

                       

                      example function could look like this:

                      function expand_many_csv( P_cursor in expandcur_t )
                          return expand_tab pipeline;
                      

                       

                      Now, to help fill in the meat of that function, you can use APEX_UTIL.string_to_table()...

                      But, that returns a PL/SQL table, not an actual table.  see the documentation on how to use it.

                       

                      However, beyond that: THE SCHEMA DESIGN NEEDS TO BE FIXED!!

                       

                      But, if you want to see what the SQL will look like afterwards (if you still choose that route):

                      with table1 as (
                       select 'V' || level field1 from dual connect by level <= 3
                      ), table2 as (
                       select 'V1,V2,V3' field2 from dual
                      )
                      select *
                      from table1 T1
                        join table( expand_api.expand_many_csv(
                          cursor( select field2 from table2 ) ) ) CSV
                        on (T1.field1 = CSV.field2);
                      
                      FIELD1 FIELD2
                      --------- -----------
                      V1    V1
                      V2     V2
                      V3    V3