Leo Kirch wrote:
>
> hi,
> i've got a strange problem with a dbproc definition while
> using variables
> within. attached is the source (don't ask what i'm trying to do :)
>
> thanks a lot
> yours
> sebastian
This is a bug, we will try to fix as soon as possible.
The subquery in your last select causes the trouble.
If you will be able to change your dbproc like this
CREATE DBPROC DBA.GET_TREE_ATTR (
IN REVISION INTEGER,
IN DEPTH INTEGER,
IN ROOT CHAR(50))
RETURNS CURSOR AS
DECLARE xyz CURSOR FOR
SELECT VALUE, TYPE, ELEMENT_UID
FROM DBA.ELEMENT_ATTRIBUTES
WHERE
( ELEMENT_UID,ELEMENT_VERSION) IN (
SELECT
ELEMENT_UID, MAX(ELEMENT_VERSION)
FROM
DBA.ELEMENT_ATTRIBUTES
WHERE
ELEMENT_VERSION <= :REVISION
GROUP BY
ELEMENT_UID, TYPE
);
DECLARE :$CURSOR CURSOR FOR
WITH RECURSIVE TREE
(NELEMENT_UID, NELEMENT_VERSION, NPARENT_UID, NPARENT_VERSION, NLEVEL,
NSOFTLINK, NDELETED)
AS
(
SELECT
ELEMENT_UID, ELEMENT_VERSION, PARENT_UID, PARENT_VERSION, 0, SOFTLINK,
DELETED
FROM
DBA.ELEMENT_TREE
WHERE
ELEMENT_VERSION <= :REVISION AND
PARENT_UID = :ROOT
UNION ALL
SELECT
ELEMENT_UID, ELEMENT_VERSION, PARENT_UID, PARENT_VERSION, NLEVEL+1,
SOFTLINK, DELETED
FROM
DBA.ELEMENT_TREE, DBA.TREE
WHERE
TREE.NELEMENT_UID = ELEMENT_TREE.PARENT_UID AND
TREE.NELEMENT_VERSION = ELEMENT_TREE.PARENT_VERSION
AND ( NLEVEL + 1 ) <= :DEPTH
)
SELECT
NELEMENT_UID AS ELEMENT_UID,
MAX(NELEMENT_VERSION) AS ELEMENT_VERSION,
NPARENT_UID AS PARENT_UID,
NLEVEL AS LEVEL,
NSOFTLINK AS SOFTLINK,
(MAX(NDELETED) MOD 2) AS DELETED,
xyz.VALUE,
xyz.TYPE
FROM
DBA.TREE
LEFT JOIN
DBA.xyz ON NELEMENT_UID = DBA.xyz.ELEMENT_UID
GROUP BY
NELEMENT_UID, NPARENT_UID, NLEVEL,NSOFTLINK, VALUE, TYPE
ORDER BY
NLEVEL,NELEMENT_UID;
it should work
Elke
SAP Labs Berlin
> //-----------------snip---------------
> CREATE TABLE "DBA"."ELEMENT_ATTRIBUTES"
> (
> "ELEMENT_UID" Char (10) ASCII NOT NULL
> DEFAULT
> 'UNDEFINED',
> "ELEMENT_VERSION" Integer NOT NULL
> DEFAULT 1,
> "TYPE" Char (10) ASCII NOT NULL
> DEFAULT 'UNDEFINED',
> "VALUE" Char (200) ASCII NOT NULL,
> "DELETED" Integer NOT NULL DEFAULT
> 0,
> PRIMARY KEY ("ELEMENT_UID", "ELEMENT_VERSION", "TYPE")
> )
> //
> CREATE TABLE "DBA"."ELEMENT_TREE"
> (
> "ELEMENT_UID" Char (10) ASCII NOT NULL,
> "ELEMENT_VERSION" Integer NOT NULL
> DEFAULT 1,
> "NAME" Char (50) ASCII NOT NULL
> DEFAULT 'UNDEFINED',
> "TYPE" Char (10) ASCII NOT NULL
> DEFAULT 'UNDEFINED',
> "PARENT_UID" Char (10) ASCII NOT NULL
> DEFAULT '1',
> "PARENT_VERSION" Integer NOT NULL
> DEFAULT 1,
> "SOFTLINK" Boolean NOT NULL DEFAULT FALSE,
> "DELETED" Integer NOT NULL DEFAULT
> 0,
> "ACTIVATED" Boolean NOT NULL DEFAULT TRUE,
> PRIMARY KEY ("ELEMENT_UID", "ELEMENT_VERSION")
> )
> //
> CREATE DBPROC DBA.GET_TREE_ATTR (
> IN REVISION INTEGER,
> IN DEPTH INTEGER,
> IN ROOT CHAR(50))
>
> RETURNS CURSOR AS
>
> DECLARE :$CURSOR CURSOR FOR
> WITH RECURSIVE TREE
> (NELEMENT_UID, NELEMENT_VERSION, NPARENT_UID,
> NPARENT_VERSION, NLEVEL,
> NSOFTLINK, NDELETED)
> AS
> (
> SELECT
> ELEMENT_UID, ELEMENT_VERSION, PARENT_UID, PARENT_VERSION,
> 0, SOFTLINK,
> DELETED
> FROM
> DBA.ELEMENT_TREE
> WHERE
> ELEMENT_VERSION <= :REVISION AND
>
> PARENT_UID = :ROOT
>
> UNION ALL
>
> SELECT
> ELEMENT_UID, ELEMENT_VERSION, PARENT_UID, PARENT_VERSION,
> NLEVEL+1,
> SOFTLINK, DELETED
> FROM
> DBA.ELEMENT_TREE, DBA.TREE
> WHERE
> TREE.NELEMENT_UID = ELEMENT_TREE.PARENT_UID AND
> TREE.NELEMENT_VERSION = ELEMENT_TREE.PARENT_VERSION
>
> AND ( NLEVEL + 1 ) <= :DEPTH
> )
>
> SELECT
> NELEMENT_UID AS ELEMENT_UID,
> MAX(NELEMENT_VERSION) AS ELEMENT_VERSION,
> NPARENT_UID AS PARENT_UID,
> NLEVEL AS LEVEL,
> NSOFTLINK AS SOFTLINK,
> (MAX(NDELETED) MOD 2) AS DELETED,
> ELEMENT_ATTRIBUTES.VALUE,
> ELEMENT_ATTRIBUTES.TYPE
> FROM
> DBA.TREE
> LEFT JOIN
> DBA.ELEMENT_ATTRIBUTES ON NELEMENT_UID =
> ELEMENT_ATTRIBUTES.ELEMENT_UID
> WHERE
> (
> ELEMENT_ATTRIBUTES.ELEMENT_UID,ELEMENT_ATTRIBUTES.ELEMENT_VERS
> ION) IN (
> SELECT
> ELEMENT_UID, MAX(ELEMENT_VERSION)
> FROM
> DBA.ELEMENT_ATTRIBUTES
> WHERE
> ELEMENT_VERSION <= :REVISION
> GROUP BY
> ELEMENT_UID, TYPE
> )
> GROUP BY
> NELEMENT_UID, NPARENT_UID, NLEVEL,NSOFTLINK, VALUE, TYPE
>
> ORDER BY
> NLEVEL,NELEMENT_UID;
> //-----------------snap---------------
>
> _________________________________________________________________
> Add photos to your e-mail with MSN 8. Get 2 months FREE*.
> http://join.msn.com/?page=features/featuredemail
>
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
