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: 59
Location: Atlanta

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: 1278
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 abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm
No new posts Getting list of tables defined under ... kishpra DB2 2 Wed Aug 24, 2016 10:36 am
No new posts ISPF Tables Creation newsysprg TSO/ISPF 6 Wed Jul 13, 2016 2:21 pm
No new posts Update the Sortout file with record c... karthik_sripal SYNCSORT 8 Tue May 17, 2016 8:52 pm


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