SQL-problem

Walter Janißen

SQL-problem

Hi

I am asking that on behalf of a question from an application developer. He wants to generate a list using SQL. Given e.g. the two tables T1 and T2

T1:

Number
-----------
1
2

T2:

Number Asset
--------------------
1          AAAA
1          BBBB
1          CCCC
2          DDDD
2          EEEE

The result should be:

1 AAAA, BBBB, CCCC
2 DDDD, EEEE

Does that work? I am not sure, if recursive SQL will do it.

regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



Edited By:
Walter Janißen[Organization Members] @ Jul 22, 2011 - 09:26 AM (Europe/Berlin)

Walter Jani&#223;en

SQL-problem
(in response to Walter Janißen)
Hi

I just solved this problem. For all who are interested in the solution:

WITH S (LEVEL, NUMBER, ASSET, ASSET_LIST) AS
(SELECT 1, A1.NUMBER, MIN(A1.ASSET) AS ASSET,
MIN(A1.ASSET!!SPACE(3000)) AS ASSET_LIST
FROM A1
GROUP BY A1.NUMBER
UNION ALL
SELECT LEVEL + 1, S.NUMBER, A1.ASSET,
STRIP(S.ASSET_LIST)!!','!!A1.ASSET
FROM S
INNER JOIN TABLE
(SELECT NUMBER, MIN(ASSET) AS ASSET
FROM A1
WHERE S.NUMBER = A1.NUMBER
AND S.ASSET < A1.ASSET
GROUP BY A1.NUMBER
) AS A1
ON S.NUMBER = A1.NUMBER
)
SELECT S.NUMBER, MAX(S.ASSET_LIST) AS "List"
FROM S
GROUP BY S.NUMBER

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



Roy Boxwell

SQL-problem
(in response to Walter Janißen)
also a recursive question! Not bad Walter

Roy Boxwell
SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Von: "Walter Jani&#223;en" <[login to unmask email]>
An: [login to unmask email]
Datum: 22.07.2011 13:29
Betreff: [DB2-L] - RE: SQL-problem



Hi

I just solved this problem. For all who are interested in the solution:

WITH S (LEVEL, NUMBER, ASSET, ASSET_LIST) AS
(SELECT 1, A1.NUMBER, MIN(A1.ASSET) AS ASSET,
MIN(A1.ASSET!!SPACE(3000)) AS ASSET_LIST
FROM A1
GROUP BY A1.NUMBER
UNION ALL
SELECT LEVEL + 1, S.NUMBER, A1.ASSET,
STRIP(S.ASSET_LIST)!!','!!A1.ASSET
FROM S
INNER JOIN TABLE
(SELECT NUMBER, MIN(ASSET) AS ASSET
FROM A1
WHERE S.NUMBER = A1.NUMBER
AND S.ASSET < A1.ASSET
GROUP BY A1.NUMBER
) AS A1
ON S.NUMBER = A1.NUMBER
)
SELECT S.NUMBER, MAX(S.ASSET_LIST) AS "List"
FROM S
GROUP BY S.NUMBER

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]
Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996





Site Links: View post online View mailing list online Send new post
via email Unsubscribe from this mailing list Manage your subscription

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

FirstName LastName

RE: SQL-problem
(in response to Roy Boxwell)

Thanks for sharing.  I think I'll be able to use this to list all users in a workload on fewer lines.  I appreciate it.  

Ron Thomas

RE: SQL-problem
(in response to FirstName LastName)


SELECT ITEM_NBR,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',trait_nbr))) as CLOB(32K)), 3)
FROM k3S003.ITEM_TRAIT where item_nbr = 102016
GROUP BY ITEM_NBR
FETCH FIRST 10 ROWS ONLY WITH UR;

Michael Hannan

RE: SQL-problem
(in response to Ron Thomas)

In Reply to Ron Thomas:

SELECT ITEM_NBR,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',trait_nbr))) as CLOB(32K)), 3)
FROM k3S003.ITEM_TRAIT where item_nbr = 102016
GROUP BY ITEM_NBR
FETCH FIRST 10 ROWS ONLY WITH UR;

Nice trick for those on certain DB2 Versions.

Db2 zOS V12 L501 provides the LISTAGG function to make it even easier, but XML was doing this type of aggregate first (since what year I don't know).  Walter might have wished for a time machine back in 2011, in his original question with recursion. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd