Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 How to write Rexx program to size and... sreejeshcs CLIST & REXX 14 Thu Oct 12, 2017 7:26 am
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts SORT Trailer Count - LRECL Output co... amorante DFSORT/ICETOOL 5 Tue Aug 29, 2017 8:57 pm
No new posts CICS Transaction attach count of supp... lind sh CICS 1 Wed Jun 21, 2017 1:33 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us