All Forums Database
shaves 22 posts Joined 04/15
12 Oct 2015
Creating derived query from 2 different tables / 3707 Syntax error

I'm trying to add a field from a different table to a derived query.  This is the first time I have tried to do this.  The query below was working unitl I added the bolded information below.  The error message is as follows:
SELECT Failed.  3707: Syntax error, expected something like ')' between 'soh' and '.'
What am I doing worng.  Thanks for the help.........
 
with drvd_qry (soh.operating_unit, s.grp_brn_id, s.stn_id, s.glt_seq) as

(select
soh.operating_unit,
s.grp_brn_id,
s.stn_id,
s.glt_seq

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.stn_id, s.glt_seq)

select
dq.operating_unit,
dq.grp_brn_id,
dq.stn_id,
dq.glt_seq,
f.merch_num as MERCH_NUM,
f.ret_ref_num as REF_NUM,
0.00 as GL_AMT,

case when f.tran_typ_cde = 1 then f.tran_amt
    when f.tran_typ_cde = 4 then f.tran_amt * -1
    end as BANK_AMT,
   
sum (GL_AMT - BANK_AMT) as DIFF
   
from paymt.fndng_recncl_dtl_rprt f

inner join rfs.cc_mrchnt_nbr m on m.mrchnt_nbr = f.merch_num and credit_card_typ = 'VI'

inner join drvd_qry dq on dq.stn_id = m.sta_stn_id --and  substr(s.grp_brn_id, 1, 2) = 'G1'
 
 where f.row_stat_cde = 'A'
 and cast (f.tran_proc_date as date format 'MM/DD/YYYY') = '09/23/2015'
 
group by
dq.operating_unit,
dq.grp_brn_id,
dq.stn_id,
dq.glt_seq,
f.merch_num,
f.ret_ref_num,
GL_AMT,
BANK_AMT

order by 1, 2, 6, 7

dnoeth 4628 posts Joined 11/04
12 Oct 2015

There's no qualified names in the column list:
with drvd_qry (operating_unit, grp_brn_id, stn_id, glt_seq) as

Dieter

shaves 22 posts Joined 04/15
13 Oct 2015

Dieter...........Thank you very much.  That solved my problem........

You must sign in to leave a comment.