Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2008
    Posts
    62

    Unanswered: Pattern matching with subquery

    Hi,

    We are using db2 9.5 on AIX 6.1.
    Have some query for matching a subquery string from one table with the other table.

    Examle:
    tab1:
    user query
    ---- -----------------------
    a qqqq XYZ ppp
    a nnnXYZ qqq
    b hhhh PWD ccc
    c MNP ddde fffff

    tab2:
    tabname
    ---------
    XYZ
    EFG
    MNP
    PWD
    ABC

    The need is to display user names from tab1 and the tablenames on which he queried, along with the count.
    Result should be:

    user tabname count
    ---- ---------- -------
    a XYZ 2
    b PWD 1
    c MNP 1

    At present I am using a shell script. In for loop i am passing table names from tab2 and greping it in query field. its taking huge time.
    Is there any better option so i can do it in a single query?


    Thanks!
    Prashant
    Last edited by prashant44; 09-07-12 at 00:29.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try this example.

    Note: "user" is a name of special register, it shouldn't be used as column names nor variable names.

    Example 1:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      tab1
    ( user_ , query ) AS (
    VALUES
      ( 'a' , 'qqqq XYZ ppp'   )
    , ( 'a' , 'nnnXYZ qqq'     )
    , ( 'b' , 'hhhh PWD ccc'   ) 
    , ( 'c' , 'MNP ddde fffff' )
    )
    , tab2
    ( tabname ) AS ( 
    VALUES
      'XYZ'
    , 'EFG'
    , 'MNP'
    , 'PWD'
    , 'ABC'
    )
    SELECT MIN(user_) AS user_
         , tabname
         , COUNT(*)   AS count
     FROM  tab1
     INNER JOIN
           tab2
      ON   SYSFUN.LOCATE(tabname , query) > 0
     GROUP BY
           tabname
     ORDER BY
           user_
    ;
    ------------------------------------------------------------------------------
    
    USER_ TABNAME COUNT      
    ----- ------- -----------
    a     XYZ               2
    b     PWD               1
    c     MNP               1
    
      3 record(s) selected.
    If you used DB2 9.7(may be Fixpack 4), qualifier "SUSFUN." may be not necessary.
    Because, SYSIBM.LOCATE supports column names for search-string(parameter 1) from DB2 9.7(may be Fixpack 4) for LUW.
    Last edited by tonkuma; 09-07-12 at 04:10. Reason: Add Fixpack in last descripions.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    DB2 9.7 Fix Pack 4 or later for LUW also supports column names for pattern-expression, like...

    Example 2:
    Code:
    SELECT MIN(user_) AS user_
         , tabname
         , COUNT(*)   AS count
     FROM  tab1
     INNER JOIN
           tab2
      ON   query LIKE '%' || tabname || '%'
     GROUP BY
           tabname
     ORDER BY
           user_
    ;

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    hi, prashant44
    if tab1 contains a extra row :
    b mmmXYZ qqq

    what is the expected result ?

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by fengsun2 View Post
    if tab1 contains a extra row :
    b mmmXYZ qqq
    what is the expected result ?
    user tabname count
    ---- ---------- -------
    a XYZ 2
    b PWD 1
    b XYZ 1
    c MNP 1
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Feb 2008
    Posts
    62
    Thank you All.

    @tonkuma: that is exactly what i was looking for. Thanks!

  7. #7
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by Peter.Vanroose View Post
    user tabname count
    ---- ---------- -------
    a XYZ 2
    b PWD 1
    b XYZ 1
    c MNP 1
    but if use tonkuma's query
    the result is :
    Code:
    WITH
      tab1
    ( user_ , query ) AS (
    VALUES
      ( 'a' , 'qqqq XYZ ppp'   )
    , ( 'a' , 'nnnXYZ qqq'     )
    , ( 'b', 'mmmXYZ qqq'     )
    , ( 'b' , 'hhhh PWD ccc'   ) 
    , ( 'c' , 'MNP ddde fffff' )
    )
    , tab2
    ( tabname ) AS ( 
    VALUES
      'XYZ'
    , 'EFG'
    , 'MNP'
    , 'PWD'
    , 'ABC'
    )
    SELECT MIN(user_) AS user_
         , tabname
         , COUNT(*)   AS count
     FROM  tab1
     INNER JOIN
           tab2
      ON   SYSFUN.LOCATE(tabname , query) > 0
     GROUP BY
           tabname
     ORDER BY
           user_
    ;
    user_ tabname count
     a     XYZ         3
     b     PWD         1
     c     MNP         1
    So maybe op want to group the user_,also。
    Code:
    WITH
      tab1
    ( user_ , query ) AS (
    VALUES
      ( 'a' , 'qqqq XYZ ppp'   )
    , ( 'a' , 'nnnXYZ qqq'     )
    , ( 'b', 'mmmXYZ qqq'     )
    , ( 'b' , 'hhhh PWD ccc'   ) 
    , ( 'c' , 'MNP ddde fffff' )
    )
    , tab2
    ( tabname ) AS ( 
    VALUES
      'XYZ'
    , 'EFG'
    , 'MNP'
    , 'PWD'
    , 'ABC'
    )
    SELECT user_ AS user_
         , tabname
         , COUNT(*)   AS count
     FROM  tab1
     INNER JOIN
           tab2
      ON   SYSFUN.LOCATE(tabname , query) > 0
     GROUP BY
           user_,tabname
     ORDER BY
           user_
    ;

Posting Permissions

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