Dusan Kolesar wrote: > > 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 >
It may be that admin.car.id is longer than address.id, causing the || to fail. It may be that the number of levels is too high for the || in the second case. Despite the fact, that type is not needed in C1, what do you want to do With this double-recursive-select? In c1 all grand...grand-parents of car_ids are in. And then you seem to do the same thing for the second time in C2. Sorry, but I do not see the reason for this and cannot help to overcome the -2010-problem. Elke SAP Labs Berlin > On Thu, 27 May 2004 09:30:52 +0200, Zabach, Elke <[EMAIL PROTECTED]> > 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/[EMAIL PROTECTED] > > -=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/[EMAIL PROTECTED]
