Results 1 to 11 of 11
  1. #1
    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    http://www.dbforums.com/db2/1615607-...ml#post6264910 (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.

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

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

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

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,415
    Quote Originally Posted by Askana

    please guide me.
    Read the error message.
    ---
    "It does not work" is not a valid problem statement.

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

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    Another clue
    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.

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

  10. #10
    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
    LOCATE(L.LocationDescription,temp.LocationList)=0
    Try to use something like this:

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •