Recursive Query

[Former Member]

Hello MAXDB-Gurus

MaxDB 7.8

In my table rbobjekte i have structured data.

Each record has a rbobjekteid and a oberobjektid (=parent)

I love the recursive query option of MaxDB.

With this one i get all rbobjekteid's under the given startpoint (20000001):

DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ

(rbobjekteid)

AS (SELECT ro.rbobjekteid

FROM rbobjekte ro

WHERE oberobjektid = '20000001' 

UNION ALL SELECT ro.rbobjekteid FROM rbobjekte ro ,TMPOBJ

WHERE ro.oberobjektid = TMPOBJ.rbobjekteid )

SELECT tmpobj.rbobjekteid FROM TMPOBJ

Now i need to get records from another table(contacts) where rbobjekteid in (the recursive query), but the recursive query is not allowed as a subselect ??

What is the best way to get this running?

It could work with select... union all select ... union all select...  but i don't know the levels? Could be from 1 to nn

Any help welcomed!

Best regards    

Albert

[holger_becker · Accepted solution]

Hi Albert,

I would suggest to use a join for the last step in the recursive select.
Something like that:

DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ
(rbobjekteid)
AS (SELECT ro.rbobjekteid
FROM rbobjekte ro
WHERE oberobjektid = '20000001' 
UNION ALL SELECT ro.rbobjekteid FROM rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid )
SELECT tmpobj.rbobjekteid FROM TMPOBJ, tab2
where tmpobj.col = tab2.col

I've not tested it so without guarantee.

Kind regards
Holger

[Former Member · Accepted solution]

Hello Holger

Working!

Thank you.

Best Regards

Albert