Creating derived query from 2 different tables / 3707 Syntax error

Database
Enthusiast

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

2 REPLIES
Senior Apprentice

Re: Creating derived query from 2 different tables / 3707 Syntax error

There's no qualified names in the column list:

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

Enthusiast

Re: Creating derived query from 2 different tables / 3707 Syntax error

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