Combine Multiple Columns into single Coma seperated string result.

Askana · #1 (**permalink**) 07-27-09, 07:21

Please help me folks,

I am new to Db2 and is having trouble figuring out how to solve this issue.
I have a list of Countries in one table and i have another table having relation between the Countries and Rollout.

When i create a new Rollout i insert the values into Rollout and RolloutCountry for specifying more than one Country for Rollout. Now i need to show all the Country Description corresponding to a specific Rollout as Coma seperated ones. I tried writing functions but i can't get the logic. Please help me.

Code:

Country
-------
CountryId
CountryDescription

Code:

RolloutCountry
--------------
Id
CountryId
RolloutId

Thanks,
Askana

sathyaram_s · #2 (**permalink**) 07-27-09, 07:27

Script Library (The post "Generate SELECT <all column names> from <tablename>" )

This link should give an example

Cheers
sathy

Askana · #3 (**permalink**) 07-27-09, 08:04

Dear Sathyaram S,

thanks for the link ,

this is the code i tried

Code:

WITH temp(RolloutId,LocationId,LocationList) AS
(
	SELECT
		RXL.RolloutId,
		RXL.LocationId,		
		CAST(L.LocationDescription AS VARCHAR(100))
		FROM RolloutLocationXRef RXL
		INNER JOIN Location L ON RXL.LocationId = L.LocationId
		WHERE RXL.RolloutId = 2
		
	UNION ALL
	
	SELECT temp.LocationList||','||L.LocationDescription
	FROM temp
	INNER JOIN RolloutLocationXRef RXL ON temp.RolloutId = RXL.RolloutId
	INNER JOIN Location L ON temp.LocationId = L.LocationId
	WHERE LOCATE(L.LocationDescription,temp.LocationList)=0
	AND RXL.RolloutId = 2
)
Select * from temp@

but i am getting this error

Code:

SQL0421N  The operands of a set operator or a VALUES clause do not have the same number of columns.

Please guide me.

stolze · #4 (**permalink**) 07-27-09, 08:34

Well, as the error message says, your first part for the UNION ALL has 3 columns and the other part has only 1 column - that can't work.

Askana · #5 (**permalink**) 07-27-09, 09:06

Thank you, I understood my mistake,

I changed the code to

Code:

WITH temp(RolloutId,LocationId,LocationList) AS
(
	SELECT
		RXL.RolloutId,
		RXL.LocationId,		
		CAST(L.LocationDescription AS VARCHAR(100))
		FROM RolloutLocationXRef RXL
		INNER JOIN Location L ON RXL.LocationId = L.LocationId
		WHERE RXL.RolloutId = 2
		
	UNION ALL
	
	SELECT 
		RXL.RolloutId,
		RXL.LocationId,	
		temp.LocationList||','||L.LocationDescription
	FROM temp
	INNER JOIN RolloutLocationXRef RXL ON temp.RolloutId = RXL.RolloutId
	INNER JOIN Location L ON temp.LocationId = L.LocationId
	WHERE LOCATE(L.LocationDescription,temp.LocationList)=0
	AND RXL.RolloutId = 2
)
Select * from temp@

now i am getting this error

Code:

SQL0345N  The fullselect of the recursive common table expression
 "DB2INST4.TEMP " must be the UNION of two or more fullselects and 
cannot include column functions, GROUP BY clause, HAVING clause, 
ORDER BY clause, or an explicit join including an ON clause.

please guide me.

n_i · #6 (**permalink**) 07-27-09, 10:26

Quote:

Originally Posted by Askana

please guide me.

Read the error message.

Askana · #7 (**permalink**) 07-28-09, 01:25

Dear n_i,

I cant figure out what i am doing wrong, I am new to DB2. Please help me.

sathyaram_s · #8 (**permalink**) 07-28-09, 06:39

Another clue

Quote:

SQL0345N The fullselect of the recursive common table expression
"DB2INST4.TEMP " must be the UNION of two or more fullselects and
cannot include column functions, GROUP BY clause, HAVING clause,
ORDER BY clause, or an explicit join including an ON clause.

An alternate approach would be

Code:

 
WITH myquery(RolloutId,LocationId,LocationList) AS    -- the original output 
(
	SELECT
		RXL.RolloutId,
		RXL.LocationId,		
		CAST(L.LocationDescription AS VARCHAR(100))
		FROM RolloutLocationXRef RXL
		INNER JOIN Location L ON RXL.LocationId = L.LocationId
		WHERE RXL.RolloutId = 2
) , 
temp(a,bc) 
(
select * from myquery where <something>=<something>
union all
select * from temp where <condition>
)
select * from temp

Askana · #9 (**permalink**) 07-28-09, 07:11

Thanks Sathyaram S,

Code:

WITH myquery(RolloutId,LocationId,LocationList) AS  
(
	SELECT
		RXL.RolloutId,
		RXL.LocationId,		
		CAST(L.LocationDescription AS VARCHAR(100))
		FROM RolloutLocationXRef RXL
		INNER JOIN Location L ON RXL.LocationId = L.LocationId
		WHERE RolloutId =2
) , 
temp(a,b,c) AS
(
select RolloutId,LocationId,LocationList from myquery WHERE RolloutId = 2
union all
SELECT 	RXL.RolloutId,
	RXL.LocationId,	
	myquery.LocationList||','||L.LocationDescription
	FROM myquery
	INNER JOIN RolloutLocationXRef RXL ON myquery.RolloutId = RXL.RolloutId
	INNER JOIN Location L ON RXL.LocationId = L.LocationId
	WHERE LOCATE(L.LocationDescription,myquery.LocationList)=0
)
select * from temp fetch first 1 row only@

this is working , but need to test before i implement it.

DB2Plus · #10 (**permalink**) 07-29-09, 08:10

Quote:

Originally Posted by Askana

Thank you, I understood my mistake,

I changed the code to

Code:

WITH temp(RolloutId,LocationId,LocationList) AS
(
	SELECT
		RXL.RolloutId,
		RXL.LocationId,		
		CAST(L.LocationDescription AS VARCHAR(100))
		FROM RolloutLocationXRef RXL
		INNER JOIN Location L ON RXL.LocationId = L.LocationId
		WHERE RXL.RolloutId = 2
		
	UNION ALL
	
	SELECT 
		RXL.RolloutId,
		RXL.LocationId,	
		temp.LocationList||','||L.LocationDescription
	FROM temp
	INNER JOIN RolloutLocationXRef RXL ON temp.RolloutId = RXL.RolloutId
	INNER JOIN Location L ON temp.LocationId = L.LocationId
	WHERE LOCATE(L.LocationDescription,temp.LocationList)=0
	AND RXL.RolloutId = 2
)
Select * from temp@

now i am getting this error

Code:

SQL0345N  The fullselect of the recursive common table expression
 "DB2INST4.TEMP " must be the UNION of two or more fullselects and 
cannot include column functions, GROUP BY clause, HAVING clause, 
ORDER BY clause, or an explicit join including an ON clause.

please guide me.

You have to remove

Quote:

LOCATE(L.LocationDescription,temp.LocationList)=0

Try to use something like this:

....

Quote:

UNION ALL

SELECT
ij.RolloutId,
ij.LocationId,
temp.LocationList|| ',' || ij.LocationDescription
FROM temp
join table
(select select distinct L.LocationDescription, RXL.RolloutId, RXL.LocationId
from
RolloutLocationXRef RXL
WHERE RXL.RolloutId = 2
JOIN
Location L
ON temp.LocationId = L.LocationId

) ij

on temp.LocationId = ij.LocationId
and
temp.RolloutId = ij.RolloutId

Lenny77 · #11 (**permalink**) 07-29-09, 09:56

I don't see any links between RolloutLocationXRef RXL and Location L tables (inside of the ij table).[/B]
So, we'll Cartesian product.

Join Table is working.

But it could work in all if the tables is not too huge
Lenny K.

Internet Services

Web Development

Digital Marketing

Consumer Tech