Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Question Unanswered: Selecting records by substring using list of strings

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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   ----------------------------------------'
    Last edited by tonkuma; 12-03-11 at 22:41. Reason: Replace "+" to "'"

  4. #4
    Join Date
    Nov 2010
    Posts
    4

    Thanks

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

  5. #5
    Join Date
    Nov 2010
    Posts
    4

    Thumbs up Thanks...your suggestion worked.

    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.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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
    Last edited by tonkuma; 12-08-11 at 05:43.

Posting Permissions

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