Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

count from 2 tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Thu Nov 25, 2010 7:30 pm    Post subject: count from 2 tables
Reply with quote

Hi ,
I have 2 tables from which i need to get the counts of debits,credits for a list of account numbers. These 2 tables are related by a column called deposit number.
table a
Code:

type            accountno               deposit no.
cr                000110                1234
db                000321                1234
db                31200                 1234
cr                000220                5678
db                789000                5678
cr                23452                 4324
db                48623                 4324


table b
Code:

type            accountno               deposit no.
db1               5435345                1234
db1               89076                  1234
db1               789789                 1234
db1               9875576                5678
db1               534534534              5678

when my inputs to the query is 000110 ,000220 the output should be

Code:

account no   type count
000110        cr   1
000110        db   5
000220        cr   1
000220        db   3

P.s the debits have a value of db in table a and value of db1 in table b

tried my best, so far no luck icon_sad.gif

Thanks in advance for any tips or hints

Rakesh
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 25, 2010 7:55 pm    Post subject:
Reply with quote

this works for cr=> db, I don't know what you want as output when you enter a db account like 789789 as input.
Code:
select type,accountno,count(*)
from
(select accountno,'cr' as type
  from tabA where type = 'cr'
union all
select A.accountno,'db' as type
  from tabA a1 , taba a2
 where a2.depositno = a1.depositno
    and a1.type = 'cr' and a2.type = 'db'
union all
select A.accountno,'db' as type
 from tabA a1 , tabb b2
 where b2.depositno = a1.depositno
    and a1.type = 'cr' and b2.type = 'db1'

) X
group by type,accountno
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts CICS Transaction attach count of supp... lind sh CICS 1 Wed Jun 21, 2017 1:33 pm
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us