SELECT Failed 3707 Syntax error, expected something like an 'EXCEPT' keyword or an 'UNION' keyword or a 'MINUS' keyword between

Database

SELECT Failed 3707 Syntax error, expected something like an 'EXCEPT' keyword or an 'UNION' keyword or a 'MINUS' keyword between

Below if the SQL I am trying to get to run. I am receiving the following error message upon execution: SELECT Failed 3707 Syntax error, expected something like an 'EXCEPT' keyword or an 'UNION' keyword or

a 'MINUS' keyword between ')' and the word 'z'

Here is the code:

with        dqry_cc (   mop_cd, mop_desc, group_branch_id, ecr_tmz_name ,

                                stn_stn_id ,  stn_id, grp_brn_id  ) as

(

select     

    a.mop_cd ,

    a.mop_desc,

    so.group_branch_id ,

     t.ecr_tmz_name,

     so.stn_stn_id,

      s.stn_id ,

      s.grp_brn_id 

from        

rfs.stn_ops_hierarchies so,

  rfs.mthd_of_pymts a,

rfs.stns s,

rfs.tmzs_map t

where     

                           s.stn_id   =     so.stn_stn_id

               and        a.mpt_mop_type_code = 'CC'

                and         S.TMZ = T.TMZS_TMZ

                and         so.group_id ='01'

                and         so.region_cd = 'RGN_stl_99'

                and         so.group_branch_id ='0101'

)

select        * 

from              

(

select     

    cc.group_branch_id  as  GpBr, 

    cc.mop_desc as Descr,

  CAST(SYSLIB.ERAC_GMT_TO_LCL(p.pymt_dt, cc.ecr_tmz_name) AS timestamp(6)) as LocalPaydate,

  cast(SYSLIB.ERAC_GMT_TO_LCL(p.pymt_dt, cc.ecr_tmz_name) as date)  as ph_pymt_dt,

  dr.dvr_srnm as DriverFName, 

  dr.dvr_frst_name as DriverLName,

  b.cr_card_hld_name as HolderName,

  cast(ra.ecr_ticket_no as varchar(30))as ECARS2,

  p.doc_nbr as ECARS1,

  b.rea_rnt_agr_nbr as RntAgrNo, 

  p.pymt_amt as AMOUNT,

  p.cur_curr_cd as Curr,

  cast('XXXXXXXXXXXX'||COALESCE(b.last_four_cc_nbr, b.CR_CARD_NBR ) as varchar(24)) as CardNo,

  cast(b.cr_card_exp_dat as date format 'mm/yy')as ExpireDate,

  cast(

case             substr(p.pymt_typ,1,1) 

                when      'R'  then  

                                 -p.auth_amt

                else 

                                 case substr(p.pymt_typ,2,1) 

                when      'R'  then

                                                               -p.auth_amt

                                else 

                                                                p.auth_amt 

                                 end

                end 

                as decimal (15,3)) as AUTH_AMT, 

  cast(p.auth_date as date format 'mm/dd/yyyy') as AuthDate,

  p.auth_nbr as AuthNo,

  b.card_auto_swiped as Swipe,

  rc.rv_meaning as PType

from        

  dqry_cc  cc,       

  rfs_rv.rnt_agr_mthd_pymts b,

  rfs_rv.rnt_agrs ra,

  rfs.cg_ref_codes rc,

  rfs.PYMTS p

   left outer join

   ( 

select        dvr.dvr_srnm, 

  dvr.dvr_frst_name,

  dvr.rdy_rnt_agr_nbr,

  dvr.main_dvr_flg

  from rfs_rv.dvr_rras 

  dvr

where        dvr.main_dvr_flg = 'MR'

) dr

  on p.ram_rea_rnt_agr_nbr = dr.rdy_rnt_agr_nbr

where        

                                cc.mop_cd = b.mop_mop_cd

                and         b.rea_rnt_agr_nbr = ra.rnt_agr_nbr

                and         p.sta_stn_Id = cc.stn_stn_id

                and         b.rea_rnt_agr_nbr = p.ram_rea_rnt_agr_nbr

                and         b.seq_nbr = p.ram_seq_nbr

                and         p.pymt_typ = rc.rv_low_value

                and         p.pymt_typ not in ('X','A','DS')

                and         rc.rv_domain = 'PYMTS.PYMT_TYP'

       --         and         ph_pymt_dt  = ?

union

     all

(select   

  cc.group_branch_id as GpBr,

  cc.mop_desc as Descr,

  cast(SYSLIB.ERAC_GMT_TO_LCL(ph.pymt_dt, cc.ecr_tmz_name) AS timestamp(6)) as LocalPaydate,

  cast(SYSLIB.ERAC_GMT_TO_LCL(ph.pymt_dt, cc.ecr_tmz_name) as date)  as ph_pymt_dt,

  dr.dvr_srnm as DriverFName,

  dr.dvr_frst_name as DriverLName,

  ph.name as HolderName,

  pd.ticket_no as ECARS2,

cast( case when rb.ecr_lgcy_resv_nbr  is null then

         case when substr(pd.ticket_no,1,1) = 'D'  then

            'D'||substr(pd.ticket_no, 2,6)

                         else

                  null

                         end

       else   'D'||rb.ecr_lgcy_resv_nbr

     end  as CHAR(7)) as ECARS1,

  rb.rnt_agr_nbr as RntAgrNo,

case when ph.cr_card_trans_typ_cde='R' then

     -1*pd.pymt_amt

    else

       pd.pymt_amt

    end   as AMOUNT,

pd.cur_curr_cd as Curr,

  ph.CR_CARD_NBR as CardNo,

cast(ph.exp_dt as date format 'mm/yy') as ExpireDate,

  cast(0 as decimal(15,3)) as AUTH_AMT,

  cast(null as date format 'mm/dd/yyyy') as AuthDate,

ph.auth_nbr as AuthNo,

cast(null as char(2)) as Swipe,

  cast(null as varchar(60)) as PType,

  cast(ph.paph_fin_trans_ref_id as decimal(19,0)) as REFID

  from

  dqry_cc  cc, 

  rfs_rv.pre_applied_pymts_hdr ph,

  rfs_rv.pre_applied_pymts_det pd

left outer join

   (select      

    ra.rnt_agr_nbr,

    ra.ecr_ticket_no,

    ra.ecre_rent_cntrct_nbr,

    ra.ecr_lgcy_resv_nbr,

    cc.grp_brn_id --

from           

   rfs_rv.rnt_agrs ra,

   dqry_cc  cc

   where   ra.sta_stn_id_orig_co = cc.stn_id QUALIFY ROW_NUMBER() OVER(PARTITION BY  ra.rnt_agr_nbr ORDER BY ra.rnt_agr_nbr) = 1

   ) rb

      on pd.ticket_no = rb.ecr_ticket_no

left outer join

       (select      

            dvr.dvr_srnm,

              dvr.dvr_frst_name,

              dvr.rdy_rnt_agr_nbr

       from rfs_rv.dvr_rras dvr

       where dvr.main_dvr_flg = 'MR'

       ) dr

       on rb.rnt_agr_nbr = dr.rdy_rnt_agr_nbr

   where ph.pymt_stn_id = cc.stn_stn_id

   and ph.mop_mop_cd = cc.mop_cd

      and ph.pymt_id = pd.pap_pymt_id

--      and ph_pymt_dt  = ?

) z 

left outer join

(select

     ft.paymt_mdia_proc_sys_cde as Settlement,

     ft.prim_acct_frst_six_dgt_nbr as First_Six,

     ft.fin_tran_ref_id

            from paymt.fin_tran ft) pft 

            on pft.fin_tran_ref_id =  z.refid

Any and all help will be greatly appreciated

1 REPLY
Enthusiast

Re: SELECT Failed 3707 Syntax error, expected something like an 'EXCEPT' keyword or an 'UNION' keyword or a 'MINUS' keyword between

  --         and         ph_pymt_dt  = ?

) <sub_query_name>

union

     all

SELECT * FROM

(select