Dear All!
I observe a very strange behaviour when 'counting leaves in a tree'.
Sorry, its a bit lengthy report, but I tried to keep it as short as possible.
Scenario:
Imagine a tree with lots of categories as nodes, and articles as leaves.
Leaves normally occur at the outermost node level, but in rare cases they
can also exist somewhere within in the tree.
Now, given we describe categories and relations in two tables(->(1), see
below),
the details of such categories/articles are described in other tables (not
here).
In CATEGORY, the categories of the tree are stored using the categoryid,
an additional field is intended to keep the number of direct article
children of each category.
The filed CATEGORY.test is for additional testing purposes to be described
soon.
In the real case this table contains some few thousand entries.
In the table RELATION I store the relations CATEGORY->CATEGORY
and CATEGORY->ARTICLE. So, PARENTID can contain entries like
CATEGORY.CATEGORYID, and CHILDID can contain entries of both types
CATEGORY.CATEGORYID and ARTICLE.ARTICLEID
(the latter one not described here, since its not important for the
problem).
In the real case this table contains quite some ten thousand entries.
Problem(s):
a) If I try to count the direct article children of each node(category),
I use the statement ->(2). But this statement does not work properly.
It always gives the total number of leaves instead of evaluating
only the direct number of leaves for each node.
My impression is, the subselect does not evaluate the categoryid
of the particular entry in the table CATEGORY.
The check whether categoryid is evaluated at all in a simpler update
statement works as intended
(see second part of the update into field TEST and result comments in
->(2)).
I think the wrong evaluation in the subselect is a bug.
The fact that I always get the total number of article children
indicates that the AND clause is not properly evaluated.
Any comments on that by the SAPdb experts?
b) In fact, the field namings here have been changed,
simply to make the problem clearer for this report.
In the real situation the field CATEGORY.CATEGORYID was named
CATEGORY.NAVID,
and RELATION.PARENTID was called RELATION.NAVID, too.
For this situation I tried to avoid mixing up the fields by using
aliases,
the coresponding statement was then ->(3).
But this does not work since I am obviously not allowed to use
aliases in the SET clause (see result comment at ->(3)).
In a last try I left out the aliases for the fields to be set ->(4),
But then SQL studio crashes and is closed by Windows!
(see result comment at ->(4)).
Hm. This should not happen at all. Any commenst here by the SAPdb
experts?
Conclusion & Request:
It seems I am somehow stuck in restrictions I neither understand nor like
too much.
Is there anybody with some valuable tip or
can the SAPdb experts confirm a bug which possibly might be
solved in some next revision of SABdb and/or SQL studio?
If I missed to describe important details please tell me.
Cheers
Kind regards,
Dr. Michael Neuber
living systems AG
-----------------------------------------------------------------------
(1) CREATE TABLE CATEGORY
( categoryid INTEGER NOT NULL,
directartricles INTEGER,
test INTEGER,
CONSTRAINT PK_CATEGORYID KEY (CATEGORYID))
//
CREATE TABLE RELATION
( PARENTID INTEGER NOT NULL,
CHILDID INTEGER NOT NULL)
//
CREATE INDEX IDX_PARENTID_RELATION ON RELATION (PARENTID asc)
//
CREATE INDEX IDX_CHILDID_RELATION ON RELATION (CHILDID asc)
//
-----------------------------------------------------------------------
(2) UPDATE category SET
directartricles = (SELECT count(r.childid)
FROM relation r
WHERE r.parentid (+) = categoryid
AND r.childid NOT IN (SELECT categoryid FROM
category)),
test = categoryid
Result: Does deliver the wrong counts (directarticles is set with *all*
children in the whole tree)
in fact 80,023 is the number of *all* articles,
and in fact the first 15 categories have no direct leaves at all (!), so
the results should be 0.
it looks as if in the subselect, the categoryid of each entry in table
CATEGORY
is not properly evaluated.
However, the field test is correctly filled(!).
Categoryid Count test
-1 80,023 -1
1 80,023 1
2 80,023 2
3 80,023 3
4 80,023 4
....
-----------------------------------------------------------------------
(3) UPDATE category a SET
a.directarticles = (SELECT count(r.childid)
FROM relations r
WHERE r.navid (+) = a.navid
AND r.childid NOT IN (SELECT navid FROM
category)),
a.test = a.navid
Result:
Error message 5016, Missing delimiter '=' at a.directartcles (expects '
=' after 'a.')
-----------------------------------------------------------------------
(4) UPDATE category a SET
directarticles = (SELECT count(r.childid)
FROM relations r
WHERE r.navid (+) = a.navid
AND r.childid NOT IN (SELECT navid FROM
category)),
test = a.navid
Result: SQL studio crashes,
subsequent Windows error message:
SQL Sto.exe -Application error
The instruction at "0x0312f45b" referenced memory at "0x00000008".
The memory could not be "read".
Click Ok to terminate the program.
Click on cancel to debug the program.
####################################################################################
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
