How do I concatenate strings from a column into a single row in DB2

Prev: How to "LOAD COPY NO" without backup ?
Next: System Time and Utilities Start/Stop Time

From: Salvatore Vacca on 20 Nov 2008 04:31

---

On 19 Nov, 23:35, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
> On Nov 19, 2:22 pm, mark83anth...(a)gmail.com wrote:
>
>
>
> > On Nov 19, 3:53 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
>
> > > On Nov 19, 12:49 pm, mark83anth...(a)gmail.com wrote:
>
> > > > On Nov 19, 2:28 pm, mark83anth...(a)gmail.com wrote:
>
> > > > > How do I concatenate strings from a column into a single row?
>
> > > > > Whats the logic to create the function in DB2.
>
> > > > > Given is below.
>
> > > > > Color
> > > > > ------
> > > > > red
> > > > > orange
> > > > > blue
> > > > > green
>
> > > > > And return a resultset like this:
>
> > > > > Colors
> > > > > -------------------------
> > > > > red,orange,blue,green
>
> > > > I will make it clear.
>
> > > > The table name is Colors
> > > > CREATE TABLE Colors
> > > > (
> > > >     Color VARCHAR(32)
> > > > )
>
> > > > Values in the Table are
> > > > Color
> > > > ------
> > > > red
> > > > orange
> > > > blue
> > > > green
>
> > > > My output should be when I do a Select * from Tabname, shd be
>
> > > > red,orange,blue,green
>
> > > > Waiting for your reply
>
> > > > Thanks in Advancd
>
> > > Mark:
>
> > > Have a look at a posting to this newsgroup titled "concatenating
> > > historical records" from October '07. In it, I listed two ways I know
> > > of/have learned to do this.
>
> > > --Jeff- Hide quoted text -
>
> > > - Show quoted text -
>
> > Hi,
>
> > I tried doing this
>
> > SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
> > Color))),'<A>',''),'</A>',' ')
> > FROM Colors;
>
> > It works fine with my DB2 9.5 client on my machine but does not work
> > with Db2 Z OS 9.1.
>
> > It gives me the following error message
>
> > SQL0171N  The data type, length or value of argument "1" of routine
> > "REPLACE"
> > is incorrect.  SQLSTATE=42815
>
> > SQL0171N  The data type, length or value of argument "1" of routine
> > "REPLACE" is incorrect.
>
> > Explanation:
>
> > The data type, length or value of argument "<n>" of routine "<name>"
> > is
> > incorrect.
>
> > The statement cannot be processed.
>
> > User response:
>
> > Ensure the arguments of the routine conform to the rules of the
> > routine.
>
> >  sqlcode: -171
>
> >  sqlstate: 42815
>
> Well, notwithstanding a simple fix for the problem you're having with
> the XML function, assuming that DB2 9.1 for Z/OS supports recursive
> common table expressions (CTEs), you could try the other technique
> demonstrated in the earlier posting I mentioned.
>
> --Jeff


I’m not sure if my query can help:

NOTE: the query is not tested!

with
tt (id,gr,txt) as
(
values (1,'GROUPA','TEXTA') union all
values (2,'GROUPB','TEXTC') union all
values (3,'GROUPB','TEXTD') union all
values (4,'GROUPA','TEXTB') union all
values (5,'GROUPC','TEXTE') union all
values (6,'GROUPB','TEXTF')
),
st(ig,i,des,d) as
( select rank() over (order by gr), rank() over (order by gr,id), txt,
gr from tt),
progs (num,d,strtmp) as
(values(0,'
','
')
union all
select num + 1,
(select d from st where i = num + 1),
(CASE WHEN (select ig from st where i = num + 1) =
(select i from st where i = num + 1) then '' else ltrim(strtmp) end)
|| (select des from st where i = num + 1) || ';'
from progs where num < (select count(*) from st)
)
select d,strtmp from progs p where p.num >0 and p.num=(select max
(p1.num) from progs p1 where p1.d=p.d)
;


Regards
Salvatore Vacca

From: mark83anthony on 20 Nov 2008 17:17

---

On Nov 20, 4:31 am, Salvatore Vacca <s.va...(a)gmail.com> wrote:
> On 19 Nov, 23:35, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
>
>
>
>
>
> > On Nov 19, 2:22 pm, mark83anth...(a)gmail.com wrote:
>
> > > On Nov 19, 3:53 pm, jefftyzzer <jefftyz...(a)sbcglobal.net> wrote:
>
> > > > On Nov 19, 12:49 pm, mark83anth...(a)gmail.com wrote:
>
> > > > > On Nov 19, 2:28 pm, mark83anth...(a)gmail.com wrote:
>
> > > > > > How do I concatenate strings from a column into a single row?
>
> > > > > > Whats the logic to create the function in DB2.
>
> > > > > > Given is below.
>
> > > > > > Color
> > > > > > ------
> > > > > > red
> > > > > > orange
> > > > > > blue
> > > > > > green
>
> > > > > > And return a resultset like this:
>
> > > > > > Colors
> > > > > > -------------------------
> > > > > > red,orange,blue,green
>
> > > > > I will make it clear.
>
> > > > > The table name is Colors
> > > > > CREATE TABLE Colors
> > > > > (
> > > > >     Color VARCHAR(32)
> > > > > )
>
> > > > > Values in the Table are
> > > > > Color
> > > > > ------
> > > > > red
> > > > > orange
> > > > > blue
> > > > > green
>
> > > > > My output should be when I do a Select * from Tabname, shd be
>
> > > > > red,orange,blue,green
>
> > > > > Waiting for your reply
>
> > > > > Thanks in Advancd
>
> > > > Mark:
>
> > > > Have a look at a posting to this newsgroup titled "concatenating
> > > > historical records" from October '07. In it, I listed two ways I know
> > > > of/have learned to do this.
>
> > > > --Jeff- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Hi,
>
> > > I tried doing this
>
> > > SELECT replace(replace(xml2clob(xmlagg(xmlelement(NAME a,
> > > Color))),'<A>',''),'</A>',' ')
> > > FROM Colors;
>
> > > It works fine with my DB2 9.5 client on my machine but does not work
> > > with Db2 Z OS 9.1.
>
> > > It gives me the following error message
>
> > > SQL0171N  The data type, length or value of argument "1" of routine
> > > "REPLACE"
> > > is incorrect.  SQLSTATE=42815
>
> > > SQL0171N  The data type, length or value of argument "1" of routine
> > > "REPLACE" is incorrect.
>
> > > Explanation:
>
> > > The data type, length or value of argument "<n>" of routine "<name>"
> > > is
> > > incorrect.
>
> > > The statement cannot be processed.
>
> > > User response:
>
> > > Ensure the arguments of the routine conform to the rules of the
> > > routine.
>
> > >  sqlcode: -171
>
> > >  sqlstate: 42815
>
> > Well, notwithstanding a simple fix for the problem you're having with
> > the XML function, assuming that DB2 9.1 for Z/OS supports recursive
> > common table expressions (CTEs), you could try the other technique
> > demonstrated in the earlier posting I mentioned.
>
> > --Jeff
>
> I’m not sure if my query can help:
>
> NOTE: the query is not tested!
>
> with
> tt (id,gr,txt) as
> (
>         values (1,'GROUPA','TEXTA') union all
>         values (2,'GROUPB','TEXTC') union all
>         values (3,'GROUPB','TEXTD') union all
>         values (4,'GROUPA','TEXTB') union all
>         values (5,'GROUPC','TEXTE') union all
>         values (6,'GROUPB','TEXTF')
> ),
> st(ig,i,des,d) as
> ( select rank() over (order by gr), rank() over (order by gr,id), txt,
> gr from tt),
> progs (num,d,strtmp) as
>         (values(0,'
> ','
> ')
>         union all
>                 select num + 1,
>                 (select d from st where i = num + 1),
>                 (CASE WHEN (select ig from st where i = num + 1) =
> (select i from st where i = num + 1) then '' else ltrim(strtmp) end)
>                   || (select des from st where i = num + 1) || ';'
> from progs where num < (select count(*) from st)
>         )
> select d,strtmp from progs p where p.num >0 and p.num=(select max
> (p1.num) from progs p1 where p1.d=p.d)
> ;
>
> Regards
> Salvatore Vacca- Hide quoted text -
>
> - Show quoted text -

Thanks Jeff,

I was able to use recursive SQL to achieve what I wanted to do.

But , I think it would be great using xmlagg and replace functions as
i did it in DB2 LUW.

I will explain what I did again.

I have a Table called Colors both in Db2 9.5 for LUW and DB2 9.1 on Z/
OS

I was able to use XML functions to return the result set like below

<A>PRA1</A><A>PRA2</A><A>PRA3</A><A>PRA4</A><A>PRA5</A><A>PRA6</A>
both in LUW and Z/OS

by using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
TECPG01.TEST1111;

but when I am trying to use the replace function like

SELECT replace(replace(xmlagg(xmlelement(NAME a, NAME)),'<A>',' ' ),'</
A>',' ') FROM TECPG01.TEST1111

It works fine with db2 on LUW but generates an error for Db2 on z/OS
saying that argument 1 of the routine REPLACE is wrong.

All i found was the the replace function was not able to generate the
result from xmlagg(xmlelement(NAME a, NAME)) as
<A>PRA1</A><A>PRA2</A><A>PRA3</A><A>PRA4</A><A>PRA5</A><A>PRA6</A> and
then

replace the search string '<A>' with the replaced string ' '.


But on Db2 on LUW , It was doing that. I was able to get the result as
PRA1 PRA2 PRA3 and so on.


Can anyone get me the equivalent of the above SQL in DB2 on z/OS

Thanks in Advance for valuable thoughts..

From: Tonkuma on 21 Nov 2008 07:29

---

DB2 for z/OS doesn't support casting XML data type to another data
type.
(See Table 13. Supported casts between built-in data types
on Page 80 of "DB2 Version 9.1 for z/OS SQL Reference".)
Also, DB2 for z/OS doesn't support XML2CLOB function.

From: Tonkuma on 21 Nov 2008 07:52

---

> I have a Table called Colors both in Db2 9.5 for LUW and DB2 9.1 on Z/OS

> SELECT replace(replace(xmlagg(xmlelement(NAME a,NAME)),'<A>',' ' ),'</A>',' ') FROM TECPG01.TEST1111
>
> It works fine with db2 on LUW but generates an error for Db2 on z/OS
> saying that argument 1 of the routine REPLACE is wrong.
>
It was neccesary to add an XML2CLOB function on DB2 9.1 for LUW, like
this:
SELECT replace(replace(XML2CLOB(xmlagg(xmlelement(NAME a,
color))),'<A>', ''), '</A>', ' ') FROM Colors

I got error message SQL0440N for
SELECT replace(replace(xmlagg(xmlelement(NAME a, color)),'<A>', ''),
'</A>', ' ') FROM Colors

SQL0440N No authorized routine named "REPLACE" of type "FUNCTION"
having compatible arguments was found.

From: mark83anthony on 21 Nov 2008 09:49

---

On Nov 21, 7:52 am, Tonkuma <tonk...(a)fiberbit.net> wrote:
> > I have a Table called Colors both in Db2 9.5 for LUW and DB2 9.1 on Z/OS
> > SELECT replace(replace(xmlagg(xmlelement(NAME a,NAME)),'<A>',' ' ),'</A>',' ') FROM TECPG01.TEST1111
>
> > It works fine with db2 on LUW but generates an error for Db2 on z/OS
> > saying that argument 1 of the routine REPLACE is wrong.
>
> It was neccesary to add an XML2CLOB function on DB2 9.1 for LUW, like
> this:
> SELECT replace(replace(XML2CLOB(xmlagg(xmlelement(NAME a,
> color))),'<A>', ''), '</A>', ' ') FROM Colors
>
> I got error message SQL0440N for
> SELECT replace(replace(xmlagg(xmlelement(NAME a, color)),'<A>', ''),
> '</A>', ' ') FROM Colors
>
> SQL0440N  No authorized routine named "REPLACE" of type "FUNCTION"
> having compatible arguments was found.

Hi,

thanks for replying.

Yeah, u are true, It was necessary to use a xml2clob in order to get
the result on Db2 9.1 for LUW.

But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
TECPG01.TEST1111 on Db2 9.1 for z/OS I was able to get the same
result as in using XML2CLOB with above for Db2 LUW.

I went over the syntax of Replace. replace function accepts only
expressions as arguments in Db2 for LUW
but Replace functions accepts only string aruguments in db2 for z/OS.

Does this anyway is effecting what I wanted to achieve.

If yes, How can I convert an expression to a string argument.

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How to "LOAD COPY NO" without backup ?
Next: System Time and Utilities Start/Stop Time