Hi Elke, I found error. Sorry it is my mistake.
My final query is:
DECLARE C1 CURSOR FOR
WITH RECURSIVE PX1 (ID, PARENT_ID, NAME, TYPE) AS
(
SELECT ID, PARENT_ID, NAME, 'Car' FROM ADMIN.CAR
UNION ALL
SELECT ADDRESS.ID, ADDRESS.PARENT_ID, ADDRESS.NAME, 'Address'
FROM ADMIN.ADDRESS, PX1
WHERE PX1.PARENT_ID = ADDRESS.ID
)
SELECT DISTINCT ID, PARENT_ID, NAME, TYPE FROM PX1
//
DECLARE C2 CURSOR FOR
WITH RECURSIVE PX2 (ID, PARENT_ID, NAME, TYPE, FOLGE, TREELEVEL) AS
(
SELECT ID, PARENT_ID, NAME, TYPE, CHR(ID), 1 AS TREELEVEL
FROM C1 WHERE PARENT_ID = -1
UNION ALL
SELECT C1.ID, C1.PARENT_ID, C1.NAME, C1.TYPE, FOLGE || lfill (CHR(C1.ID), ' ', 10), TREELEVEL + 1
FROM C1, PX2
WHERE PX2.ID = C1.PARENT_ID AND PX2.TYPE = 'Address' /* here was problem */
/* WHERE PX2.ID = C1.PARENT_ID AND TREELEVEL < 3 my previous condition */
)
SELECT ID, PARENT_ID, NAME, TYPE, FOLGE, TREELEVEL
FROM PX2
The error ocured when there was 2 the same id in C1 cursor. 1st from table car and 2nd from table address. It caused loop witout end. So, there is no bug.
Sorry for my inattention, and thank you.
Regards, Dusan
On Fri, 4 Jun 2004 09:23:02 +0200, Zabach, Elke <[EMAIL PROTECTED]> wrote:
Dusan Kolesar wrote:
Hello Elke, you are absolutly right. There is a endless loop. But I don't see eny reason for it. My result is OK into correct treelevel (I have nodes in levels 1,2,3). And then there is added level 4 the same as level 2, level 5 the same as level 3, ...
The manual says: "It is ensured, however, that the results of the n th execution are used for the n+1 th execution to avoid an endless loop."
So I think there is some bug in the recursive cursor.
As I am not able to find this with the info I have so far and my tries to reproduce it here did not succeed, may I ask for the table definitions and data (send directly to me, not to the list) to be able to reproduce and check where the problem is.
You can be assured that the data is handled as save as possible and deleted after having found the problem.
Elke SAP Labs Berlin
Thank you very much for your useful help.
Regards, Dusan
On Tue, 1 Jun 2004 14:01:09 +0200, Zabach, Elke <[EMAIL PROTECTED]> wrote:
> Hi,
>
> As expected, the second recursive call produces more level than 50 -->
> 52 * 10 bytes (lfill-length) exceeds the maximum length of the
> result-column.
>
> Now the question is, why are there so many levels? To find out, please
> do the second query let's say 4 times. Once with the additional
> predicate in the second union-part: AND TREELEVEL <= 3, once with <= 4,
> <=5, <= 6. Then compare the results. One of these additional once has a
> wrong parent_id/id-combination, resulting in an 'endless' loop during
> this recursion. I think, something like
> ID PARENT_ID
> X Y can be found with even level-nos
> Y X can be found in the odd level-nos
> can be seen. One level will add 2 additional records, the other level
> will add 14. Therefore the check should be not too hard.
>
> Elke
> SAP Labs Berlin
>
>> -----Ursprüngliche Nachricht-----
>> Von: Dusan Kolesar [mailto:[EMAIL PROTECTED]
>> Gesendet: Dienstag, 1. Juni 2004 11:48
>> An: Zabach, Elke
>> Betreff: Re: AW: AW: AW: AW: Combine cursors
>>
>> Hello Elke,
>>
>> This is my knltrace.prt
>>
>> Dusan
>>
>> On Tue, 1 Jun 2004 10:56:23 +0200, Zabach, Elke <[EMAIL PROTECTED]>
>> wrote:
>>
>> > Hi,
>> >
>> > You should look for knltrace.prt on the rundirectory (perhaps with
>> > OS-commands, not with dbm).
>> >
>> > Elke
>> > SAP Labs Berlin
>> >
>> >> -----Ursprüngliche Nachricht-----
>> >> Von: Dusan Kolesar [mailto:[EMAIL PROTECTED]
>> >> Gesendet: Dienstag, 1. Juni 2004 10:33
>> >> An: Zabach, Elke
>> >> Betreff: Re: AW: AW: AW: Combine cursors
>> >>
>> >> Hello Elke,
>> >>
>> >> I have tryed to make trace file using :
>> >> dbmcli -d enercon -u dbm,dbm util_execute diagnose vtrace default
>> select
>> >> on
>> >> 'SQL execution with error'
>> >> dbmcli -d enercon -u dbm,dbm trace_flush
>> >> ..\..\..\depend\bin\xkernprot -f knltrace akbm
>> >> dbmgetf -d enercon -u dbm,dbm -k KNLTRCPRT -f d:\text.txt
>> >> dbmcli -d enercon -u dbm,dbm util_execute diagnose vtrace default
>> select
>> >> off
>> >>
>> >> but, my text.txt contains only:
>> >>
>> >>
>>
************************************************************************
>> >> *** KERNPROT 7.4.3 2004-06-01 09:26:29
>> ***
>> >>
>>
************************************************************************
>> >>
>> >>
>> >> On Tue, 1 Jun 2004 08:14:47 +0200, Zabach, Elke
<[EMAIL PROTECTED]>
>> >> wrote:
>> >>
>> >> > Hi,
>> >> >
>> >> > if the rundirectory of the database you want to vtrace includes at
>> >> least
>> >> > one Blank,
>> >> > (\PROGRAM FILES\ does !) then this may cause the problem
mentioned.
>> >>
>> >> Yes, you are right. My SapDb directory is F:\Program Files\sapdb\
>> >>
>> >> >
>> >> > Then please go into the Rundirectory of your database
>> >> > and do
>> >> > xkernprot -f knltrace akbm
>> >> > The resulting file is what you are looking for.
>> >> >
>> >> > Elke
>> >> > SAP Labs Berlin
>> >> >
>> >> >> -----Ursprüngliche Nachricht-----
>> >> >> Von: Dusan Kolesar [mailto:[EMAIL PROTECTED]
>> >> >> Gesendet: Montag, 31. Mai 2004 09:19
>> >> >> An: Zabach, Elke
>> >> >> Betreff: Re: AW: AW: Combine cursors
>> >> >>
>> >> >> Hello Elke,
>> >> >> I have tryed to make VTrace as you describe in your document, but
>> >> some
>> >> >> error ocoured
>> >> >> during 4.th step.
>> >> >>
>> >> >> > dbmcli -d enercon -u dbm,dbm trace_prot akb
>> >> >>
>> >> >> ERR
>> >> >> -24964,ERR_EXECUTE: error in program execution
>> >> >> 1,""F:\PROGRAM FILES\SAPDB\DEPEND\bin\xkernprot" -d ENERCON akb"
>> >> >> *** ERROR -903: HOST FILE OPEN ERROR, wrong file or device name
>> >> >>
>> >> >>
>> >> >> reading : f:\program
>> files\sapdb\indep_data\wrk\ENERCON\knltrace.dat
>> >> >> creating: ENERCON.prt
>> >> >>
>> >> >> Regards, Dusan
>> >> >>
>> >> >> On Fri, 28 May 2004 10:50:21 +0200, Zabach, Elke
>> >> <[EMAIL PROTECTED]>
>> >> >> wrote:
>> >> >> > Hi,
>> >> >> >
>> >> >> > May I ask you to prepare a vtrace according to
>> >> >> > http://sapdb.2scale.net/moin.cgi/VTrace
>> >> >> > but change DEFAULT to DEFAULT SELECT in step 1 and 6
>> >> >> > and change skb in step 4 to akbm.
>> >> >> >
>> >> >> > Perhaps you will have to increase the database parameter
>> >> >> TRACE_PAGES_US
>> >> >> > a little bit so that everything from statement text to the
>> error -
>> >> 2010
>> >> >> > will fit into.
>> >> >> >
>> >> >> > Please send the resulting file (the part starting with the
>> >> statement
>> >> >> > text, ending with some lines starting with RECEIVE and having
>> ***
>> >> >> -2010
>> >> >> > in) to the list/directly to me.
>> >> >> >
>> >> >> > Elke
>> >> >> > SAP Labs Berlin
>> >> >> >
>> >> >> >> -----Ursprüngliche Nachricht-----
>> >> >> >> Von: Dusan Kolesar [mailto:[EMAIL PROTECTED]
>> >> >> >> Gesendet: Freitag, 28. Mai 2004 09:09
>> >> >> >> An: Zabach, Elke
>> >> >> >> Cc: SAP DB mailing list
>> >> >> >> Betreff: Re: AW: Combine cursors
>> >> >> >>
>> >> >> >> On Thu, 27 May 2004 15:50:58 +0200, Zabach, Elke
>> >> >> <[EMAIL PROTECTED]>
>> >> >> >> wrote:
>> >> >> >>
>> >> >> >> > 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.
>> >> >> >>
>> >> >> >> car.Id and address.Id have the same type Integer.
>> >> >> >> car.Id acquire values 2..35 and address.Id 1..7000.
>> >> >> >> I'm using 4..5 levels in my tree.
>> >> >> >> So I think, concatenetion should not cause eny problems.
>> >> >> >>
>> >> >> >> >
>> >> >> >> > 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.
>> >> >> >> >
>> >> >> >>
>> >> >> >> Reason why I do my selects is:
>> >> >> >> My address tree is relativ big (aprox.7 000 - 20 000 nodes in
>> the
>> >> >> >> future).
>> >> >> >> Somewhere in this tree are cars (aprox 100 - 500).
>> >> >> >>
>> >> >> >> My 1st query is selecting all cars and only that address which
>> are
>> >> >> >> parents, ..., of them.
>> >> >> >> Address subtree with cars.
>> >> >> >>
>> >> >> >> My 2nd query sorts result in the tree representation.
>> >> >> >> It is for comfortable tree inserting in my client aplication.
>> >> >> >>
>> >> >> >> Thank a lot for your response.
>> >> >> >>
>> >> >> >> Dusan
>> >> >> >>
>> >> >> >>
>> >> >> >> > 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]
-- Dusan Kolesar Helsinska 19 040 13 Kosice Slovakia e-mail : [EMAIL PROTECTED] ICQ# : 160507424
-=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]
