can't use function 'with' and 'union'
error :
Error: SQL0104N An unexpected token "as" was found following "with dt_range (dt)". Expected tokens may include: "JOIN". SQLSTATE=42601
(State:42601, Native Code: FFFFFF98)

SQL Statement:

with dt_range (dt) as
(values month(cast('2004-01-01' as date)) union all select (dt + 1) from dt_range where dt < month(cast('2004-03-26' as date)) )
select distinct (case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end) as mth,
ab.rpt_nm, ab.seq, (substr(bb.trx_ym,1,4)) as trx_yr, (substr(bb.trx_ym,5,2)) as trx_mth, ab.desc, ab.pur_lst,
sum(bb.rm_amt) as rcp_amt, 0 as pmt_amt
from dt_range a
left outer join db2inst.itpitsvpur1_1 ab on ab.desc <> ''
left outer join db2inst.itpitsvtrx bb on
bb.trx_ym = substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),4,4)||substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),1,2)
and rtrim(bb.trx_typ) in ('R','BR')
and locate(bb.pur_cde, ab.pur_lst, 1) <> 0 and bb.pur_cde <> '' and bb.loan_relshp = ab.relshp_ind and bb.del_flg <> 'Y'
where rtrim(ab.rpt_nm) in ('ITIS','ITIS 1.1')
group by case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end,
ab.rpt_nm, ab.seq, (substr(bb.trx_ym,1,4)), (substr(bb.trx_ym,5,2)), ab.desc, ab.pur_lst

union

with dt_range (dt) as
(values month(cast('2004-01-01' as date)) union all select (dt + 1) from dt_range where dt < month(cast('2004-03-26' as date)) )
select distinct (case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end) as mth,
ab.rpt_nm, ab.seq, (substr(bb.trx_ym,1,4)) as trx_yr, (substr(bb.trx_ym,5,2)) as trx_mth, ab.desc, ab.pur_lst,
0 as rcp_amt, sum(bb.rm_amt) as pmt_amt
from dt_range a
left outer join db2inst.itpitsvpur1_1 ab on ab.desc <> ''
left outer join db2inst.itpitsvtrx bb on
bb.trx_ym = substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),4,4)||substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),1,2)
and rtrim(bb.trx_typ) in ('P','BP','M')
and locate(bb.pur_cde, a.pur_lst, 1) <> 0 and bb.pur_cde <> '' and bb.loan_relshp = a.relshp_ind and bb.del_flg <> 'Y'
where rtrim(ab.rpt_nm) in ('ITIS','ITIS 1.1')
group by case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end,
ab.rpt_nm, ab.seq, (substr(bb.trx_ym,1,4)), (substr(bb.trx_ym,5,2)), ab.desc, ab.pur_lst

Never Mind
I figured it out

can you share with us what you found ?

Thanks for your time


Sathyaram

WITH dt_range (dt) as (values month(cast('2004-01-01' as date)) union all select (dt + 1) from dt_range where dt < month(cast('2004-03-26' as date)) )
(
select a.dt, ... from Table_1 b where XXX ....
UNION
select a.dt, ... from Table_1 b where YYY ....
UNION
select a.dt, ... from Table_1 b where YYY ....

)order by ...

SAMPLE:-

with dt_range (dt) as (values month(cast('2004-01-01' as date)) union all select (dt + 1) from dt_range where dt < month(cast('2004-03-26' as date)) )
(
select distinct a.dt as mth,
b.rpt_nm, b.seq, (substr(c.trx_ym,1,4)) as trx_yr, (substr(c.trx_ym,5,2)) as trx_mth, b.desc, b.pur_lst,
sum(c.rm_amt) as rcp_amt, 0 as pmt_amt
from dt_range a
left outer join db2inst.itpitsvpur1_2 b on b.desc <> ''
left outer join db2inst.itpitsvtrx c on
c.trx_ym = substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),4,4)||substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),1,2)
and rtrim(c.trx_typ) in ('R','BR') and c.pur_cde = '' and c.del_flg <> 'Y'
where rtrim(b.rpt_nm) = 'ITIS 1.2' and b.seq in (10026,10028,10034) and substr(b.pur_lst,3,1) = ' '
group by a.dt, b.rpt_nm, b.seq, (substr(c.trx_ym,1,4)), (substr(c.trx_ym,5,2)), b.desc, b.pur_lst

UNION

select distinct a.dt as mth,
b.rpt_nm, b.seq, (substr(c.trx_ym,1,4)) as trx_yr, (substr(c.trx_ym,5,2)) as trx_mth, b.desc, b.pur_lst,
0 as rcp_amt, sum(c.rm_amt) as pmt_amt
from dt_range a
left outer join db2inst.itpitsvpur1_2 b on b.desc <> ''
left outer join db2inst.itpitsvtrx c on
c.trx_ym = substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),4,4)||substr((case when a.dt < 10 then '0'||rtrim(char(a.dt))||'-2004' else rtrim(char(a.dt))||'-2004' end),1,2)
and rtrim(c.trx_typ) in ('P','BP','M') and c.pur_cde = '' and c.del_flg <> 'Y'
where rtrim(b.rpt_nm) ='ITIS 1.2' and b.seq in (10026,10028,10034) and substr(b.pur_lst,3,1) = ' '
group by a.dt, b.rpt_nm, b.seq, (substr(c.trx_ym,1,4)), (substr(c.trx_ym,5,2)), b.desc, b.pur_lst

) order by 1,2,3