Antwort: [DB2-L] SQL HELP

Rodney Krick

Antwort: [DB2-L] SQL HELP


Renu,

you can do it with recursion, but if you have lots of data, you may run
into performance headaches...

WITH PARENT ( ID
, IDX
, ADDRESS
) AS
(SELECT A.ID
, A.IDX
, VARCHAR(A.DETAIL, 1024)
FROM TEST1 A
WHERE NOT EXISTS (SELECT 1 FROM TEST1 T WHERE T.ID = A.ID
AND T.IDX < A.IDX)
UNION ALL
SELECT B.ID
, B.IDX
, CONCAT (C.ADDRESS, CONCAT(' ', B.DETAIL))
FROM TEST1 B
,PARENT C
WHERE B.ID = C.ID
AND B.IDX > C.IDX
AND NOT EXISTS
(SELECT 1 FROM TEST1 X WHERE X.IDX > C.IDX AND X.IDX < B.IDX)
)
SELECT ID, ADDRESS
FROM PARENT E
WHERE E.IDX = (SELECT MAX(F.IDX) FROM TEST1 F WHERE F.ID = E.ID)
ORDER BY ID
WITH UR;

DSNT404I SQLCODE = 347, WARNING: THE RECURSIVE COMMON TABLE EXPRESSION
PARENT
MAY CONTAIN AN INFINITE LOOP
DSNT418I SQLSTATE = 01605 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXODML SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 0 0 0 1210597956 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'00000000' X'00000000' X'00000000' X'48284244'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
ID ADDRESS
---------+---------+---------+---------+---------+---------+---------+---------+

1 MIKE ANGEL 323 APT MAIN STREET NY NY
2 DAVID ESTER 12 EDGEMERE STREET PRINCETON NJ
3 RS
DSNE610I NUMBER OF ROWS DISPLAYED IS 3



Mit freundlichen Grüßen / Best regards,

Rodney Krick
Senior Consultant
____________________________
aformatik Training & Consulting GmbH & Co. KG
Tilsiter Str. 8, D-71065 Sindelfingen, Germany
Tel.: +49 (0)7031-23 807-0
Fax: +49 (0)7031-23 807-19

mailto:[login to unmask email]
___________________________________________
AITP Authorized Independent Training Provider for IBM Software
____________________________________________
Kommanditgesellschaft mit Sitz in Sindelfingen
Amtsgericht Stuttgart HRA 242677
USt-Id. Nr.: DE 814174749
pers. haftende Gesellschafterin: aformatik Verwaltungs-GmbH
Geschäftsführer: Dipl. Inform. Andreas Anglett
Sitz in Sindelfingen, Amtsgericht Stuttgart HRB 245894



-----DB2 Data Base Discussion List <[login to unmask email]> schrieb: -----


An: [login to unmask email]
Von: [login to unmask email]
Gesendet von: DB2 Data Base Discussion List <[login to unmask email]>
Datum: 01/15/2008 04:45AM
Thema: [DB2-L] SQL HELP


Hello List
I need help to write this sql
Table name is TEST1
Table structure is :
id smallint,
idx smallint,
Detail varchar(30)
Data is :
id , idx , detail
1 , 0 , 'Mike Angel'
1 , 1 , '323 apt '
1 , 2 , 'Main Street'
1 , 3 , 'NY'
1 , 4 , 'NY'
2 , 0 , 'David Ester'
2 , 1 , '12 Edgemere Street'
2 , 2 , 'Princeton'
2 , 3 , 'NJ'
3 , 0 , 'RS'
3 , 1 , ' '
I want the result as
1, 'Mike Angel 323 apt Main street NY NY'
2, 'David Ester 12 Edgemere Street Princeton NJ'
3, 'RS'
Thanks in advance
Renu






Start the year off right. Easy ways to stay in shape in the new year.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no
cost, click on Member Services
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: SQL HELP
(in response to Rodney Krick)
This functionality is called "aggregate concatenation": instead of
a SUM or COUNT of all entries within a GROUP BY, you essentially
want the concatenation of them.

Have a look at slides 44 through 50 of
http://www.abis.be/resources/presentations/idug20071106ctedb2.pdf
where a similar problem is worked out using recursive SQL.

-- Peter Vanroose
ABIS Training & Consulting
Leuven, Belgium.


















__________________________________________________________
Ta semester! - sök efter resor hos Yahoo! Shopping.
Jämför pris på flygbiljetter och hotellrum här:
http://shopping.yahoo.se/c-169901-resor-biljetter.html?partnerId=96914052

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

John McKown

Re: SQL HELP
(in response to Peter Vanroose)
> -----Original Message-----
> From: DB2 Data Base Discussion List
> [mailto:[login to unmask email] On Behalf Of Peter Vanroose
> Sent: Wednesday, January 16, 2008 1:08 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] SQL HELP
>
>
> This functionality is called "aggregate concatenation": instead of
> a SUM or COUNT of all entries within a GROUP BY, you essentially
> want the concatenation of them.
>
> Have a look at slides 44 through 50 of
> http://www.abis.be/resources/presentations/idug20071106ctedb2.pdf
> where a similar problem is worked out using recursive SQL.
>
> -- Peter Vanroose

Too bad that DB2 does not allow the user defination of aggregrate
functions (aka SUM, COUNT, etc). I have such a one for PostgreSQL (an
open-source RDMS that runs mainly on Linux or *BSD).

--
John McKown
Senior Systems Programmer
HealthMarkets
Keeping the Promise of Affordable Coverage
Administrative Services Group
Information Technology

The information contained in this e-mail message may be privileged
and/or confidential. It is for intended addressee(s) only. If you are
not the intended recipient, you are hereby notified that any disclosure,
reproduction, distribution or other use of this communication is
strictly prohibited and could, in certain circumstances, be a criminal
offense. If you have received this e-mail in error, please notify the
sender by reply and delete this message without copying or disclosing
it.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms