Duplicate records with a UNION

Database
Enthusiast

Duplicate records with a UNION

I have a query with a UNION.  I was trying to combine 2 records into 1.  The top half gets records from 1 set of tables and the bottom half gets records from a differen set of tables.  Both halves use fields from 2 derived queries.  The results currently look like this:

Group             Branch               Ref NUm            GL Amt           Bank Amt

  135                 15                    1z2x3c                25.00               0.00

  135                 15                    1z2x3c                  0.00              25.00

 

What I would like to see is this:

  135                15                    1z2x3c                   25.00             25.00

 

I added the fields with 0.00 in each half so I had the same number of columns since the 2 halves bring back different amount fields.  I have also compared all of the fields and their datatypes.  If they were different, then I used CAST to make them the same.  Can I do this with a UNION or is there some other method to combines 2 rows into 1?  I really appreciate your help.  Thanks.........

 

This is the sql I have developed:

with drvd_qry (operating_unit, grp_brn_id, ecr_dept_id, stn_id) as

(select

soh.operating_unit,

s.grp_brn_id,

s.ecr_dept_id,

s.stn_id

from stns s

inner join rfs.stn_ops_hierarchies soh on soh.stn_stn_id = s.stn_id

where substr(s.grp_brn_id, 1, 2) = 'G1'

group by soh.operating_unit, s.grp_brn_id, s.ecr_dept_id, s.stn_id),

qry_drvd (department, ecr_ticket_no, open_item_id, rnt_agr_nbr, monetary_amount) as

(select

j.department,

j.ecr_ticket_no,

j.open_item_id,

j.rnt_agr_nbr,

cast(j.monetary_amount as decimal (15,2))

from

rfs.journal_entries j

where

j.business_unit = 'A0141'

and j.accounting_date = cast ('23-SEP-2015' as date format 'dd-MMM-YYYY')

and  j.account_gl = '109850')

select

bb.BU,

bb.GPBR,

bb.STN_ID,

bb.DEPTID,

cast(ft.mrchnt_nbr as decimal (20,0)) as MERCH_NUM,

bb.TICKET_NUM,

ft.prim_acct_frst_six_dgt_nbr as FIRST6,

ft.prim_acct_last_four_dgt_nbr as LAST4,

bb.AUTH_NUM,

cast(ft.stlmt_uniq_ref_nbr as decimal (20,0)) as REF_NUM,

bb.GL_AMT,

bb.BANK_AMT

from

(select

aa.bu,

aa.gpbr,

aa.stn_id,

aa.deptid,

aa.ticket_num,

p.auth_nbr as AUTH_NUM,

p.fin_tran_ref_id,

aa.GL_AMT,

CAST(0 AS DECIMAL (15,2)) as BANK_AMT

from

(select

dq.operating_unit as BU,

dq.grp_brn_id as GPBR,

dq.stn_id as STN_ID,

qd.department as DEPTID,

qd.ecr_ticket_no as TICKET_NUM,

qd.open_item_id,

qd.rnt_agr_nbr,

cast(qd.monetary_amount as decimal (15,2)) as GL_AMT

from

qry_drvd qd,

drvd_qry dq

where dq.ecr_dept_id = qd.department ) aa

left outer join rfs.pymts p on p.ecr_pymt_id = cast(aa.open_item_id as decimal(19,0))

and p.ram_rea_rnt_agr_nbr = aa.rnt_agr_nbr) bb

left outer join paymt.fin_tran ft on ft.fin_tran_ref_id = bb.fin_tran_ref_id

UNION

select

b.BU,

b.GPBR,

b.STN_ID,

b.DEPTID,

cast(b.MERCH_NUM as decimal(20,0)),

qd.ecr_ticket_no as TICKET_NUM,

b.FIRST6,

b.LAST4,

b.AUTH_NUM,

cast(b.REF_NUM as decimal(20,0)),

b.GL_AMT,

b.BANK_AMT

from

(select

a.BU,

a.GPBR,

a.STN_ID,

a.DEPTID,

a.MERCH_NUM,

a.REF_NUM,

a.FIRST6,

a.LAST4,

p.auth_nbr as AUTH_NUM,

p.ecr_pymt_id,

a.GL_AMT,

a.BANK_AMT

from

(select

dq.operating_unit as BU,

dq.grp_brn_id as GPBR,

dq.stn_id as STN_ID,

dq.ecr_dept_id as DEPTID,

cast(f.merch_num as varchar(20)) as MERCH_NUM,

f.ret_ref_num as REF_NUM,

ft.prim_acct_frst_six_dgt_nbr as FIRST6,

ft.prim_acct_last_four_dgt_nbr as LAST4,

CAST(0 AS DECIMAL(15,2)) as GL_AMT,

cast(case when f.tran_typ_cde = 1 then f.tran_amt

    when f.tran_typ_cde = 4 then f.tran_amt * -1

    end as decimal (15,2)) as BANK_AMT,

ft.fin_tran_ref_id

--sum (GL_AMT - BANK_AMT) as DIFF

from paymt.fndng_recncl_dtl_rprt f,

rfs.cc_mrchnt_nbr m,

drvd_qry dq,

paymt.fin_tran ft

--rfs.pymts p

where f.row_stat_cde = 'A'

and cast (f.tran_proc_date as date format 'MM/DD/YYYY') = '09/23/2015'

and m.mrchnt_nbr = f.merch_num

and m.credit_card_typ = 'VI'

and dq.stn_id = m.sta_stn_id

and ft.stlmt_uniq_ref_nbr = f.ret_ref_num

--having GL_AMT + bank_AMT > 300

group by

dq.operating_unit,

dq.grp_brn_id,

dq.stn_id,

dq.ecr_dept_id ,

f.merch_num,

f.ret_ref_num,

ft.prim_acct_frst_six_dgt_nbr,

ft.prim_acct_last_four_dgt_nbr,

GL_AMT,

BANK_AMT,

ft.fin_tran_ref_id) a

left outer join rfs.pymts p on p.fin_tran_ref_id = a.fin_tran_ref_id) b

left outer join qry_drvd qd on cast(qd.open_item_id as decimal(19,0)) = b.ecr_pymt_id

--order by 1, 5,6