Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    2

    Unhappy Unanswered: Concatenating Multiple rows to end of results set

    I am trying to create a fixed position output file with customer information and the first three accounts they have with us per the following query in DB2/UDB Version 7.2. But I am getting the error:

    [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "fetch" was found following "e.cst_id - b.cst_id". Expected tokens may include ")". SQLSTATE=42601

    Any ideas would be appreciated.


    Thanks,
    Dave

    select cast(ltrim(a.cst_id) as char(10)) ||
    case when a.cst_typ_id = '01'
    then cast('P' as char(1))
    else cast('C' as char(1))
    end ||
    cast(ltrim(a.tax_id_nbr) as char(9)) ||
    cast(ltrim(a.ip_nm_line_1) as char(40)) ||
    cast(ltrim(a.ip_nm_line_2) as char(40)) ||
    cast(ltrim(a.addr_line_1) as char(40)) ||
    cast(ltrim(a.addr_line_2) as char(40)) ||
    cast(ltrim(a.addr_city) as char(29)) ||
    cast(ltrim(a.addr_st) as char(2)) ||
    cast(left(ltrim(a.addr_zip_cd),5) as char(5)) ||
    case when length(ltrim(rtrim(a.wk_area_tele_cd))) = 0
    then cast(' ' as char(3))
    else cast(ltrim(a.wk_area_tele_cd) as char(3))
    end ||
    case when length(ltrim(rtrim(a.wk_tele_nbr))) = 0
    then cast(' ' as char(7))
    else cast(ltrim(a.wk_tele_nbr) as char(7))
    end ||
    case when length(ltrim(rtrim(a.wk_tele_extn))) = 0 or
    ltrim(rtrim(a.wk_tele_extn)) = '0000'
    then cast (' ' as char(4))
    else cast(ltrim(a.wk_tele_extn) as char(4))
    end ||
    case when length(ltrim(rtrim(a.hm_area_tele_cd))) = 0
    then cast(' ' as char(3))
    else cast(ltrim(a.hm_area_tele_cd) as char(3))
    end ||
    case when length(ltrim(rtrim(a.hm_tele_nbr))) = 0
    then cast(' ' as char(7))
    else cast(ltrim(a.hm_tele_nbr) as char(7))
    end ||
    (select cast(ltrim(c.acct_nbr) as char(35))
    from whse.ar c
    where b.ar_id = c.ar_id and
    b.as_of_dt = c.as_of_dt and
    c.acct_nbr in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id fetch first 1 rows only)) ||
    (select cast(ltrim(c.acct_nbr) as char(35))
    from whse.ar c
    where b.ar_id = c.ar_id and
    b.as_of_dt = c.as_of_dt and
    c.acct_nbr in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id and
    d.acct_nbr not in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only)) ||
    (select cast(ltrim(c.acct_nbr) as char(35))
    from whse.ar c
    where b.ar_id = c.ar_id and
    b.as_of_dt = c.as_of_dt and
    c.acct_nbr in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id and
    d.acct_nbr not in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only))
    from whse.cst a,
    whse.cst_ar b
    where a.cst_id = b.cst_id and
    a.as_of_dt = b.as_of_dt and
    ltrim(rtrim(cst_from_rel_cd)) || ltrim(rtrim(cst_to_rel_cd)) in ('SECJOR','PRIJOR','SECJNT','PRIJNT','TSTTRS','BUS
    INC','PRIOWN','SECJAN','PRIJAN','SECPPR','PRIPPR',
    'PRIBUS','PRITR','TRS','DBA','PRINPR','TST','BUS',
    'PRIPTN','PRITRS','DBAINC','PRIDBA','LLCINC','OWN'
    ,'SECOWN','SECDBA')

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Concatenating Multiple rows to end of results set

    May I suggest that you look into the ROWNUMBER() function which may simplify your query. Use the result set from the ROWNUMBER() as a in-line query in a FROM. Below is a template to get you started.

    SELECT ...... FROM
    (SELECT cst_id, acct_nbr, ROWNUMBER() OVER (PARTITION BY cst_id, acct_nbr ORDER BY as_of_dt) as seq) x
    WHERE x.cst_id = (join to your customer address table)
    AND x.seq <= 3

    Then you can use a CASE statement to grab the first account (seq = 1) as a column, the seq = 2 as 2nd account, and so forth.


    Originally posted by davew@pcbancorp
    I am trying to create a fixed position output file with customer information and the first three accounts they have with us per the following query in DB2/UDB Version 7.2. But I am getting the error:

    [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "fetch" was found following "e.cst_id - b.cst_id". Expected tokens may include ")". SQLSTATE=42601

    Any ideas would be appreciated.


    Thanks,
    Dave

    select cast(ltrim(a.cst_id) as char(10)) ||
    case when a.cst_typ_id = '01'
    then cast('P' as char(1))
    else cast('C' as char(1))
    end ||
    cast(ltrim(a.tax_id_nbr) as char(9)) ||
    cast(ltrim(a.ip_nm_line_1) as char(40)) ||
    cast(ltrim(a.ip_nm_line_2) as char(40)) ||
    cast(ltrim(a.addr_line_1) as char(40)) ||
    cast(ltrim(a.addr_line_2) as char(40)) ||
    cast(ltrim(a.addr_city) as char(29)) ||
    cast(ltrim(a.addr_st) as char(2)) ||
    cast(left(ltrim(a.addr_zip_cd),5) as char(5)) ||
    case when length(ltrim(rtrim(a.wk_area_tele_cd))) = 0
    then cast(' ' as char(3))
    else cast(ltrim(a.wk_area_tele_cd) as char(3))
    end ||
    case when length(ltrim(rtrim(a.wk_tele_nbr))) = 0
    then cast(' ' as char(7))
    else cast(ltrim(a.wk_tele_nbr) as char(7))
    end ||
    case when length(ltrim(rtrim(a.wk_tele_extn))) = 0 or
    ltrim(rtrim(a.wk_tele_extn)) = '0000'
    then cast (' ' as char(4))
    else cast(ltrim(a.wk_tele_extn) as char(4))
    end ||
    case when length(ltrim(rtrim(a.hm_area_tele_cd))) = 0
    then cast(' ' as char(3))
    else cast(ltrim(a.hm_area_tele_cd) as char(3))
    end ||
    case when length(ltrim(rtrim(a.hm_tele_nbr))) = 0
    then cast(' ' as char(7))
    else cast(ltrim(a.hm_tele_nbr) as char(7))
    end ||
    (select cast(ltrim(c.acct_nbr) as char(35))
    from whse.ar c
    where b.ar_id = c.ar_id and
    b.as_of_dt = c.as_of_dt and
    c.acct_nbr in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id fetch first 1 rows only)) ||
    (select cast(ltrim(c.acct_nbr) as char(35))
    from whse.ar c
    where b.ar_id = c.ar_id and
    b.as_of_dt = c.as_of_dt and
    c.acct_nbr in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id and
    d.acct_nbr not in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only)) ||
    (select cast(ltrim(c.acct_nbr) as char(35))
    from whse.ar c
    where b.ar_id = c.ar_id and
    b.as_of_dt = c.as_of_dt and
    c.acct_nbr in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id and
    d.acct_nbr not in (select acct_nbr
    from whse.ar d,
    whse.cst_ar e
    where d.ar_id = e.ar_id and
    d.as_of_dt = e.as_of_dt and
    e.cst_id = b.cst_id fetch first 1 rows only) fetch first 1 rows only))
    from whse.cst a,
    whse.cst_ar b
    where a.cst_id = b.cst_id and
    a.as_of_dt = b.as_of_dt and
    ltrim(rtrim(cst_from_rel_cd)) || ltrim(rtrim(cst_to_rel_cd)) in ('SECJOR','PRIJOR','SECJNT','PRIJNT','TSTTRS','BUS
    INC','PRIOWN','SECJAN','PRIJAN','SECPPR','PRIPPR',
    'PRIBUS','PRITR','TRS','DBA','PRINPR','TST','BUS',
    'PRIPTN','PRITRS','DBAINC','PRIDBA','LLCINC','OWN'
    ,'SECOWN','SECDBA')

Posting Permissions

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