UNION Problem on DB2 Ver 6

[Arayeshi]

Hi all,
I have a two tables with same structure in DB2 Ver 6 on OS/390
1- CurrPay(CustomerId, BnkId, BranchCode, PayDate, Amount)
for Current Period Customers Payments
2- PayHist(CustomerId, BnkId, BranchCode, PayDate, Amount)
for customer payments history

I need to find sum/count of All Payments for each bankId & PayDate &
BranchCode with one query.
I write query for each table like this:

A:
SELECT BnkId, BranchCode, PayDate, sum(Amount) as Amnt, count(*) as
Cnt
FROM CurrPay
GROUP BY BnkId, BranchCode, PayDate

B:
SELECT BnkId, BranchCode, PayDate, sum(Amount) as Amnt, count(*) as
Cnt
FROM PayHist
GROUP BY BnkId, BranchCode, PayDate

Each of these queries work correctly but i need grouping on UNION ALL
of these two table
because we have records with same bankId & PayDate & BranchCode in two
tables.

SELECT BnkId, BranchCode, PayDate, sum(Amount) as Amnt, count(*) as
Cnt
FROM
(
SELECT BnkId, BranchCode, PayDate, Amount
FROM CurrPay
UNION ALL
SELECT BnkId, BranchCode, PayDate, Amount
FROM PayHist
)
GROUP BY BnkId, BranchCode, PayDate

But DB2 Ver 6 not support this query.
please help me. thanks.

[Phil Sherman]

DB2 V6 for OS/390 supports a full outer join which is what you want.
This will give you all of the rows from both tables without requiring
matching rows.

Try:
SELECT COALESCE(a.BnkId,b.BnkId) AS Bank_Id
,COALESCE(a.BranchCode,b.BranchCode) AS Branch_Code
,COALESCE(a.PayDate,b.PayDate) AS Pay_Date
,SUM(COALESCE(a.Amount,0)+COALESCE(b.Amount,0)) AS Amnt
,count(*) AS Cnt
FROM CurrPay a
FULL OUTER JOIN PayHist b
ON a.BnkId = b.BnkId
AND a.BranchCode = b.BranchCode
AND a.PayDate = b.PayDate

GROUP BY COALESCE(a.BnkId,b.BnkId)
,COALESCE(a.BranchCode,b.BranchCode)
,COALESCE(a.PayDate,b.PayDate)

You may not need the COALESCE functions in the SUM but I don't have a
S/390 system available to verify this.

Phil Sherman
Arayeshi wrote:

Hi all,
I have a two tables with same structure in DB2 Ver 6 on OS/390
1- CurrPay(CustomerId, BnkId, BranchCode, PayDate, Amount)
for Current Period Customers Payments
2- PayHist(CustomerId, BnkId, BranchCode, PayDate, Amount)
for customer payments history

I need to find sum/count of All Payments for each bankId & PayDate &
BranchCode with one query.
I write query for each table like this:

A:
SELECT BnkId, BranchCode, PayDate, sum(Amount) as Amnt, count(*) as
Cnt
FROM CurrPay
GROUP BY BnkId, BranchCode, PayDate

B:
SELECT BnkId, BranchCode, PayDate, sum(Amount) as Amnt, count(*) as
Cnt
FROM PayHist
GROUP BY BnkId, BranchCode, PayDate

Each of these queries work correctly but i need grouping on UNION ALL
of these two table
because we have records with same bankId & PayDate & BranchCode in two
tables.

SELECT BnkId, BranchCode, PayDate, sum(Amount) as Amnt, count(*) as
Cnt
FROM
(
SELECT BnkId, BranchCode, PayDate, Amount
FROM CurrPay
UNION ALL
SELECT BnkId, BranchCode, PayDate, Amount
FROM PayHist
)
GROUP BY BnkId, BranchCode, PayDate

But DB2 Ver 6 not support this query.
please help me. thanks.