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

    Unanswered: Pattern patching 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: tab2:

    user query tabname
    ---- --------------- ---------
    a qqqq XYZ ppp XYZ
    a nnnXYZ qqq EFG
    b hhhh PWD ccc MNP
    c MNP ddde fffff 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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    select user,tabname,count(*) from mytab group by user,tabname
    Andy

Posting Permissions

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