title: Teradata / Combine records with a UNION not working
body: <p>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:</p>
  
  <pre><code> Group             Branch               Ref NUm            GL Amt           Bank Amt
     135                 15                    1z2x3c                25.00               0.00
     135                 15                    1z2x3c                  0.00              25.00
  </code></pre>
  
  <p>What I would like to see is this:</p>
  
  <pre><code> 135                15                    1z2x3c                   25.00             25.00
  </code></pre>
  
  <p>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.........</p>
  
  <p>This is the sql I have developed:</p>
  
  <pre><code> 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
  </code></pre>