Teradata / Combine records with a UNION not working

Question

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

 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

 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

Answer 1

Since data is coming from two different tables and then I believe doing group by over the union will not help as derived table using with clause cannot be used inside subquery .If possible load this data to a a volatile table and then do group by and find the max of ("GL Amt"), ("Bank Amt") and or create a view and then query...

Answer 2

Perform the union and then use group by and because you dummy fields are 0 use SUM to join both rows

  SELECT "Group", "Branch", "Ref Num", SUM("GL Amt"), SUM("Bank Amt")
  FROM ( 
       SELECT * FROM YourQuery1
       UNION ALL
       SELECT * FROM YourQuery2
      ) as t
  GROUP BY  
       "Group", "Branch", "Ref Num"