| |
Welcome to the dBforums forums.
You are currently viewing our boards as a guest which gives you limited access to view most discussions, articles and access our other FREE features. By joining our free community you will have access to post topics, communicate privately with other members (PM), respond to polls, upload your own photos and access many other special features. Registration is fast, simple and absolutely free so please, join our community today!
If you have any problems with the registration process or your account login, please contact contact support.
If you prefer not to see double-underlined words and corresponding ads, place your cursor here for ContentLink opt out.
|
 |
12-05-03, 18:57
|
|
Registered User
|
|
Join Date: Dec 2003
Posts: 2
|
|
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')
|
|
12-08-03, 14:58
|
|
Registered User
|
|
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.
Quote:
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')
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Concatenating Multiple rows to end of results set
