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.
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]
