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

[mark83...@gmail.com]

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

[mark83...@gmail.com]

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

[jefftyzzer]

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

[mark83...@gmail.com]

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

[jefftyzzer]

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

[Salvatore Vacca]

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

[mark83...@gmail.com]

> 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..

[Tonkuma]

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.

[Tonkuma]

> 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.

[mark83...@gmail.com]

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.

[Tonkuma]

> 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 got same result by using Command Editor on DB2 LUW 9.1 and 9.5
without using XML2CLOB function.
But, the data type must be XML and I guessed that Command Editor
converted the result into displayable format.
Because, the statement "SELECT SUBSTR(XMLAGG(XMLELEMENT(NAME a, NAME)),
1,100) from Colors;", returned error message SQL0440N.
It is neccesary XML data to cast to string(I did it by using XML2CLOB)
to use the XML data as a string argument of DB2 functions like SUBSTR,
REPLACE, so on.

>
> 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.
>

REPLACE on DB2 for LUW is almost same as REPLACE on DB2 for z/OS.

If you read the description of the function on SQL Reference of DB2
for LUW,
you can see:
The first argument can be of any built-in character string or binary
string type.
<snipped>
The type of the second and third arguments is identical to that of the
first argument.

> Does this anyway is effecting what I wanted to achieve.
>
> If yes, How can I convert an expression to a string argument.

As far as I know, there's no way to convert XML data to string on DB2
for z/OS by using bult-in functions.

[mark83...@gmail.com]

Hi Tonkuma,

Thanks so much for replying.

I am a DB2 AIX guy, so fairly new to z/os.

I used recursive SQL to produce what i wanted to.

Below is the SQL.

WITH temp1(NAME, all_words, cnt) as
(SELECT min(NAME), VARCHAR(min(NAME),2000),SMALLINT(1) FROM
TECPG01.TEST1111 a
UNION ALL
SELECT a.NAME, b.all_words||','||a.NAME,SMALLINT(b.cnt+1)
FROM TECPG01.TEST1111 a, temp1 b
WHERE a.NAME > b.NAME
AND a.NAME = (select min(c.NAME) from TECPG01.TEST1111 c where c.NAME
> b.NAME)
)
SELECT all_words FROM temp1 d
where cnt = (SELECT max(cnt) FROM temp1)

Now, I decided to write a function and ask users to call this function
to concatenate multiple rows to a string.

How do I write a function using Recursive SQL for this,

Does DB2 for z OS support Recursive SQL.

I would be very thankful to you if you get me this function for me to
get going.

Thanks in Advance again.

[mark83...@gmail.com]

> Thanks in Advance again.- Hide quoted text -

>
> - Show quoted text -

Or else,

Can i get an equivalent function/ stored procedure of the below
function in DB2 for z os.

CREATE FUNCTION get_str()
LANGUAGE SQL
RETURNS VARCHAR(1024)
BEGIN ATOMIC
DECLARE str VARCHAR(1024);
SET str = '';
loop1: FOR row AS (SELECT Name FROM TECPG01.TEST1111)
DO
IF row.Name IS NOT NULL THEN
SET str = str || row.Name || '';
END IF;
END FOR loop1;
RETURN str;
END@

[sahil...@gmail.com]

CREATE TABLE COLOR
(
COLORS VARCHAR(MAX)
)



CREATE FUNCTION [UDF_COL_TO_SINGLE_ROW]
(
@VALUE AS VARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @colors NVARCHAR(50)

SELECT @colors = COALESCE(@colors + ',', '') + COLORS
FROM
COLOR

RETURN @colors
END

SELECT DBO.UDF_COL_TO_SINGLE_ROW ('DASD') AS COLOR_NAME


IT MUST WORKS.....ALL D BEST............

[sahil...@gmail.com]