with CASH_SUBM as
(select payor.name payor_name, ssn,
sum(subm_actv_part.amt)
tot_cash_amt
from db2inst1.cr_subm_actv_part subm_actv_part, db2inst1.cr_subm_actv subm_actv,
db2inst1.cr_payor_sub subm, db2inst1.fn_payor payor,
db2inst1.part0 p
where subm_actv.fk_cr_payor_subzid = subm.zid
and subm_actv_part.fk_cr_subm_actvzid = subm_actv.zid
and subm_actv_part.be_zid = p.zid
and subm.fk_fn_payorzid = payor.zid
-- other system
and payor.typ_text_id in ('AAAA','BBBB')
-- exclude reversals
and subm.status_ind <= 8
and subm_actv.status_ind <= 8
group by payor.name, p.ssn
),
CASH_FN_TXN as
(select payor.name payor_name, ssn,
sum(
case when fn_txn_rvrsl_ind = 1
then - f.tot_amt
else
f.tot_amt
end
) tot_fn_txn_amt
from db2inst1.fn_txn_log f, db2inst1.fn_payor payor,
db2inst1.cr_subm_actv_part actv_part, db2inst1.part0 p
where substr(fncl_txn_cd,1,2)= 'CR'
and f.payor_id = payor.typ_zid
-- Other System
and payor.typ_ind = 3 and payor.typ_text_id in ('AAAA','BBBB')
and source_typ = 23
and actv_part.zid = f.source_zid
and actv_part.be_ssn = p.ssn
group by payor.name, ssn
)
select coalesce(CASH_FN_TXN.payor_name, CASH_SUBM.payor_name) payor,
coalesce( CASH_FN_TXN.ssn, CASH_SUBM.ssn) ssn, coalesce(TOT_fn_txn_AMT,0) tot_fn_txn_amt,
coalesce( TOT_CASH_AMT,0) tot_cash_amt,
(coalesce(tot_fn_txn_amt,0) - coalesce(tot_cash_amt,0) ) difference
from CASH_FN_TXN full outer join CASH_SUBM
on CASH_FN_TXN.payor_name = CASH_SUBM.payor_name
and CASH_FN_TXN.ssn = CASH_SUBM.ssn
order by coalesce(CASH_FN_TXN.payor_name, CASH_SUBM.payor_name), coalesce(CASH_FN_TXN.ssn, CASH_SUBM.ssn)

When i run the query above, i get two rows for the same
Payor AAAA and ssn xxxxxxxxx. It does not happen with all the SSNs. I have had this problem before with other queries and i think it's a bug in DB2 v7.2 SQL on AIX.
If i qualify the query with 'ssn = xxxxxxxxx' in the where clause, i get only one row for the payor and ssn.

Multiple row example:

Payor SSN Tot_fn_txn_amt Tot_cash_amt difference
AAAA xxxxxxxxx -198.34 5127.62 -5325.96
AAAA xxxxxxxxx 5325.96 5127.62 198.34

Can somebody tell me if there is something wrong with my query or is this a bug in DB2 ?