From: Dusan Kolesar (dusankolesarszm.sk)
Date: Thu May 27 2004 - 04:44:09 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Hello Elke,

When I try:

DECLARE C1 CURSOR FOR
SELECT * FROM ADDRESS FOR REUSE
//
DECLARE C2 CURSOR FOR
   WITH RECURSIVE PX2 (ID, PARENT_ID, NAME, FOLGE, TREELEVEL) AS
   (
     SELECT ID, PARENT_ID, NAME, CHR(ID), 1 AS TREELEVEL FROM C1 WHERE
PARENT_ID=-1
     UNION ALL
     SELECT C1.ID, C1.PARENT_ID, C1.NAME,
       FOLGE || lfill (CHR(C1.ID), ' ', 10), TREELEVEL + 1
     FROM C1, PX2
        WHERE PX2.ID = C1.PARENT_ID
   )
SELECT ID, PARENT_ID, NAME, FOLGE, TREELEVEL
 FROM PX2
ORDER BY FOLGE

it is working OK.

But query:
DECLARE C1 CURSOR FOR
   WITH RECURSIVE PX1 (ID, PARENT_ID, NAME, TYPE) AS
   (
     SELECT ID, PARENT_ID, NAME, 1 FROM ADMIN.CAR
     UNION ALL
     SELECT ADDRESS.ID, ADDRESS.PARENT_ID, ADDRESS.NAME, 2 FROM
ADMIN.ADDRESS, PX1
       WHERE PX1.PARENT_ID = ADDRESS.ID
   )
SELECT DISTINCT ID, PARENT_ID, NAME, TYPE FROM PX1 FOR REUSE
//
DECLARE C2 CURSOR FOR
   WITH RECURSIVE PX2 (ID, PARENT_ID, NAME, FOLGE, TREELEVEL) AS
   (
     SELECT ID, PARENT_ID, NAME, CHR(ID), 1 AS TREELEVEL FROM C1 WHERE
PARENT_ID=-1
     UNION ALL
     SELECT C1.ID, C1.PARENT_ID, C1.NAME,
       FOLGE || lfill (CHR(C1.ID), ' ', 10), TREELEVEL + 1
     FROM C1, PX2
        WHERE PX2.ID = C1.PARENT_ID
   )
SELECT ID, PARENT_ID, NAME, FOLGE, TREELEVEL
 FROM PX2
ORDER BY FOLGE
gives me error : General error;-2010 POS(1) Assignment impossible, char
value too long.
Highlited is "DECLARE" C2 CURSOR FOR
1.st query is OK (i can see the ressult).

What can bee the reason ??
Thank you.

Dusan

On Thu, 27 May 2004 09:30:52 +0200, Zabach, Elke <elke.zabachsap.com>
wrote:
>
> Dusan Kolesar wrote:
>>
>> Hello,
>>
>> I have two cursors.
>> First is for select lines from 2 tables.
>> This is my semi result. This result I want to sort also.
>> DECLARE C1 CURSOR FOR
>> WITH RECURSIVE PX (ID, PARENT_ID, NAME, TYPE) AS
>> (
>> SELECT ID, PARENT_ID, NAME, 1 FROM ADMIN.CAR
>> UNION ALL
>> SELECT ADDRESS.ID, ADDRESS.PARENT_ID, ADDRESS.NAME, 2 FROM
>> ADMIN.ADDRESS, PX
>> WHERE PX.PARENT_ID = ADDRESS.ID
>> )
>> SELECT DISTINCT ID, PARENT_ID, NAME, TYPE FROM PX
>>
>> Using next cursor I want to sort my result table (it is tree
>> representation)
>> DECLARE C2 CURSOR FOR
>> WITH RECURSIVE PX (ID, PARENT_ID, NAME, FOLGE, TREELEVEL) AS
>> (SELECT ID, PARENT_ID, NAME, CHR(ID), 1 AS TREELEVEL FROM
>> ADDRESS WHERE
>> PARENT_ID=-1
>> UNION ALL
>> SELECT ADDRESS.ID, ADDRESS.PARENT_ID, ADDRESS.NAME,
>> FOLGE || lfill (CHR(ADDRESS.ID), ' ', 10), TREELEVEL + 1
>> FROM ADDRESS, PX
>> WHERE PX.id = ADDRESS.PARENT_ID
>> )
>> SELECT ID, PARENT_ID, NAME, FOLGE, TREELEVEL
>> FROM PX
>> ORDER BY FOLGE
>>
>> Is it possible to combine these cursors?
>> I want to put cursor C1 into cursor C2 (insted table ADDRESS).
>>
> YES
> Just change all ADDRESS to C1.
>
> Elke
> SAP Labs Berlin
>
>> Thanks for advice.
>> Regards, Dusan
>>
>> -=x=-
>> Skontrolované antivírovým programom NOD32
>>
>>
>> --
>> MaxDB Discussion Mailing List
>> For list archives: http://lists.mysql.com/maxdb
>> To unsubscribe:
> http://lists.mysql.com/maxdb?unsub=elke.zabachsap.com

-=x=-
Skontrolované antivírovým programom NOD32

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]