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')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')