If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Combine Multiple Columns into single Coma seperated string result.

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 6
Red face Combine Multiple Columns into single Coma seperated string result.

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

Last edited by Askana; 07-27-09 at 09:48.
Reply With Quote
  #2 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Script Library (The post "Generate SELECT <all column names> from <tablename>" )

This link should give an example

Cheers
sathy
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 6
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.

Last edited by Askana; 07-27-09 at 09:46.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,721
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.
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 6
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.
Reply With Quote
  #6 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,333
Quote:
Originally Posted by Askana

please guide me.
Read the error message.
__________________
---
"It does not work" is not a valid problem statement.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 6
Dear n_i,

I cant figure out what i am doing wrong, I am new to DB2. Please help me.
Reply With Quote
  #8 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
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
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 6
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.
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Posts: 150
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
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Jul 2009
Location: NY
Posts: 963
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On