Selecting records by substring using list of strings

db2hack · #1 (**permalink**) 12-03-11, 03:10

Need a method to select records based on finding a sub-string in a column in one table using a list of strings from another sub-select. Sub-select produces a vector(single column) of values that will be used to locate matching sub-string in another column in a different table. My question is basically what is the approach to solving this query? I don't see a join being possible since the two tables have no exact matching fields. Want to output only those records from table 1 where one of the strings in the sub-select or table 2...anyway my brain is fried looking at this....thanks for any info.

Example:
Table 1
Col1 Col2
abcd Now is the time....
xyz Hotfix KB974352
qrs Windows Update KB2423219

Table 2 (or sub-select produces single column of values)
KB974352
KB2423219
KB100234

Desired output:
xyz Hotfix KB974352
qrs Windows Update KB2423219

tonkuma · #2 (**permalink**) 12-03-11, 05:22

join conditions are more flexible.

Please follow the underlined and red colored way in syntx diagrams.

subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

Code:

joined-table

                      .-INNER-----.                                                
>>-+-table-reference--+-----------+--JOIN--table-reference--ON--join-condition-+-><
   |                  '-| outer |-'                                            |   
   +-table-reference--CROSS JOIN--table-reference------------------------------+   
   '-(--joined-table--)--------------------------------------------------------'   

outer

              .-OUTER-.   
|--+-LEFT--+--+-------+-----------------------------------------|
   +-RIGHT-+              
   '-FULL--'

Note: I'll omit table-reference to simplify the references.

Code:

A join-condition is a search-condition, except that: 
•It cannot contain any subqueries, scalar or otherwise
•It cannot include any dereference operations or the DEREF function, where the reference value is other than the object identifier column
•It cannot include an SQL function
•Any column referenced in an expression of the join-condition must be a column of one of the operand tables of the associated join (in the scope of the same joined-table clause)
•Any function referenced in an expression of the join-condition of a full outer join must be deterministic and have no external action
•It cannot include an XMLQUERY or XMLEXISTS expression
An error occurs if the join condition does not comply with these rules (SQLSTATE 42972).

Search conditions - IBM DB2 9.7 for Linux, UNIX, and Windows

Code:

search-condition

|--+-----+--+-predicate--+-------------------------------+-+---->
   '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |   
            '-(search-condition)---------------------------'   

   .------------------------------------------------------------------------.   
   V                                                                        |   
>----+--------------------------------------------------------------------+-+--|
     '-+-AND-+--+-----+--+-predicate--+-------------------------------+-+-'     
       '-OR--'  '-NOT-'  |            '-SELECTIVITY--numeric-constant-' |       
                         '-(search-condition)---------------------------'

Basic predicate - IBM DB2 9.7 for Linux, UNIX, and Windows

Code:

Basic predicate

>>-expression--+- = ------+----expression----------------------><
               |      (1) |                   
               +- <> -----+                   
               +- < ------+                   
               +- > ------+                   
               |      (1) |                   
               +- <= -----+                   
               |      (1) |                   
               '- >= -----'

Expressions - IBM DB2 9.7 for Linux, UNIX, and Windows

Code:

expression

     .-operator--------------------------------------.     
     V                                (1)            |     
|------+-----+--+-function-invocation--------------+-+----------|
       +- + -+  +-(expression)---------------------+       
       '- - -'  |          (2)                     |       
                +-constant-------------------------+       
                +-column-name----------------------+       
                |          (3)                     |       
                +-variable-------------------------+       
                |                  (4)             |       
                +-special-register-----------------+       
                |                   (5)            |       
                +-scalar-fullselect----------------+       
                |                  (6)             |       
                +-labeled-duration-----------------+       
                |                 (7)              |       
                +-case-expression------------------+       
                |                    (8)           |       
                +-cast-specification---------------+       
                |                 (9)              |       
                +-field-reference------------------+       
                |                       (10)       |       
                +-xmlcast-specification------------+       
                |                             (11) |       
                +-array-element-specification------+       
                |                   (12)           |       
                +-array-constructor----------------+       
                |                       (13)       |       
                +-dereference-operation------------+       
                |                   (14)           |       
                +-method-invocation----------------+       
                |                    (15)          |       
                +-OLAP-specification---------------+       
                |                       (16)       |       
                +-row-change-expression------------+       
                |                    (17)          |       
                +-sequence-reference---------------+       
                |                   (18)           |       
                '-subtype-treatment----------------'

As a consequence, try...

Table1 AS t1 INNER JOIN Table2 AS t2 ON LOCATE(t2.col , t1.col2) > 0

If you are using older version of DB2 and got error, try...

Table1 AS t1 INNER JOIN Table2 AS t2 ON SYSFUN.LOCATE(t2.col , t1.col2) > 0

tonkuma · #3 (**permalink**) 12-03-11, 11:55

This diagram may give you some hints.

Code:

 
Table1 AS t1         INNER JOIN  Table2 AS t2         ON  LOCATE(t2.col , t1.col2)  >  0

A          A              |      A          A         |   A                      A     A
|          |              |      |          |         |   |                      |     |
|          |              |      |          |         |   |                      |     |
|          |              |      |          |         |   '-function-invocation -'  >  '-- constant
|          |              |      |          |         |
|          |              |      |          |         |   A                                       A
|          |              |      |          |         |   |                                       |
|          '------.       |      |          '------.  |   |                                       |
|                 |       |      |                 |  |   '-----  (basic) predicate          -----'
|                 |       |      |                 |  |                      ||
|                 |       |      |                 |  |   <-----      search-condition       ----->
|                 |       V      |                 |  V                      ||
'-table-reference-'  INNER JOIN  '-table-reference-'  ON  <-----       join-condition        ----->

A                                                                                                 A
|                                                                                                 |
|                                                                                                 |
'----------------------------------------  joined-table   ----------------------------------------'

db2hack · #4 (**permalink**) 12-03-11, 12:09

Thank you very much for your suggestions....I will work on implementing your idea today...thanks.

db2hack · #5 (**permalink**) 12-04-11, 12:45

Thanks tonkuma...your suggestion really simplified the problem. I'm basically an admin forced into DBA work with only OJT experience...thanks again for your help. Take care.

tonkuma · #6 (**permalink**) 12-08-11, 04:37

Just for my fun.

Code:

from-clause

         .-,---------------.   
         V                 |   
>>-FROM----table-reference-+-----------------------------------><

Code:

table-reference

>>-+-table-name--+------------------------+--+------------------------+---------------+-><
   |             '-| correlation-clause |-'  '-| tablesample-clause |-'               |   
   +-+-+-nickname--+-------------------+--+------------------------+------------------+   
   | | '-view-name-'                   |  '-| correlation-clause |-'                  |   
   | '-+-ONLY--+--(--+-table-name-+--)-'                                              |   
   |   '-OUTER-'     '-view-name--'                                                   |   
   +-TABLE--(--function-name--(--+----------------+--)--)--+------------------------+-+   
   |                             | .-,----------. |        '-| correlation-clause |-' |   
   |                             | V            | |                                   |   
   |                             '---expression-+-'                                   |   
   |                     (1)                                                          |   
   +-xmltable-expression------+------------------------+------------------------------+   
   |                          '-| correlation-clause |-'                              |   
   +-| nested-table-expression |--+------------------------+--------------------------+   
   |                              '-| correlation-clause |-'                          |   
   +-| data-change-table-reference |--+------------------------+----------------------+   
   |                                  '-| correlation-clause |-'                      |   
   +-| collection-derived-table |--+------------------------+-------------------------+   
   |                               '-| correlation-clause |-'                         |   
   '-joined-table---------------------------------------------------------------------'   

correlation-clause

   .-AS-.                                                
|--+----+--correlation-name--+-----------------------+----------|
                             |    .-,-----------.    |   
                             |    V             |    |   
                             '-(----column-name-+--)-'

More detailed diagram: table-reference appeared three times.

Code:

Table1           AS  t1          INNER  JOIN  Table2        AS t2  ON  LOCATE(t2.col , t1.col2)  >  0

A    A               A                 |      A                 A  |   A                      A     A
|    |               |                 |      |                 |  |   |                      |     |
|    |               |                 |      |                 |  |   |                      |     |
|    '-------.  [AS] '-corre           |      |                 |  |   '-function-invocation -'     '-- constant
|            |          lation         |      |                 |  |
|            |            -name        |      |                 |  |             ||                       ||
|            |                         |      |                 |  |
|            |  A             A        |      |                 |  |   <---  expression  ----->  >  <expression>
|            |  |             |        |      |                 |  |
|            |  |             |        |      |                 |  |   A                                       A
'-table-name-'  '-correlation-'        |      |(same as Table1) |  |   |                                       |
                    -clause            |      |                 |  |   |                                       |
                                       |      |                 |  |   '-----  (basic) predicate          -----'
A                             A        |      |                 |  |
|                             |        |      |                 |  |                      ||
|                             |        |      |                 |  |
|                             |        |      |                 |  |   <-----      search-condition       ----->
|                             |        |      |                 |  |
|                             |        |      |                 |  |                      ||
|                             |        V      |                 |  V
'-----  table-reference  -----' [INNER] JOIN  '-table-reference-'  ON  <-----       join-condition        ----->

A                                                                                                              A
|                                                                                                              |
|                                                                                                              |
'---------------------------------------------    joined-table    ---------------------------------------------+

                                                       ||

<---------------------------------------------   table-reference  --------------------------------------------->

As a consequence, you can write:
FROM Table1 AS t1 INNER JOIN Table2 AS t2 ON LOCATE(t2.col , t1.col2) > 0

Internet Services

Web Development

Digital Marketing

Consumer Tech