Pattern patching with subquery

DB2 database administration questions.

Pattern patching with subquery

Postby alex » Tue Sep 11, 2012 1:19 am

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,
Alex
alex
 
Posts: 6
Joined: Tue Jul 14, 2009 9:43 am

Invitations sent: 0
Successful invitations: 0

Re: Pattern patching with subquery

Postby marian » Tue Sep 11, 2012 1:20 am

I think this should do it.

select user,tabname,count(*) from mytab group by user,tabnam
marian
Site Admin
 
Posts: 16
Joined: Tue Jul 14, 2009 9:13 am

Invitations sent: 15
Successful invitations: 2


Return to DB2 Administration

Who is online

Users browsing this forum: No registered users and 1 guest

cron