Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Question Unanswered: can't use function 'with' and 'union'

    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

  2. #2
    Join Date
    Mar 2004
    Posts
    4
    Never Mind
    I figured it out

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    can you share with us what you found ?

    Thanks for your time


    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Mar 2004
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •