How to use a recursive query as subquery ??? 2007-02-12 - By Beermann, Albert
Back
Hello Everybody
I use a recursive query to get all rbobjektideid from structured data
(bill of material with up to 99 levels, each rbobjekteid belongs to an
oberobjektid)
DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ (LEVEL, rbobjekteid,
oberobjektid)
AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid
FROM rbobjekte ro
WHERE oberobjektid = '10091691'
UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM
rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
SELECT distinct rbobjekteid FROM TMPOBJ
Now i have all rbobjekteid's from all levels under my start object
(10091691)
Now i want to use this command as a subquery !!!!!!!!!!???????????
Select * from xyz join abc where xyz.rbobjekteid in (
DECLARE RNAME CURSOR FOR WITH RECURSIVE TMPOBJ (LEVEL, rbobjekteid,
oberobjektid)
AS (SELECT 1, ro.rbobjekteid, ro.oberobjektid
FROM rbobjekte ro
WHERE oberobjektid = '10091691'
UNION ALL SELECT TMPOBJ.LEVEL + 1, ro.rbobjekteid, ro.oberobjektid FROM
rbobjekte ro ,TMPOBJ
WHERE ro.oberobjektid = TMPOBJ.rbobjekteid)
SELECT distinct rbobjekteid FROM TMPOBJ
)
error !! missing delimiter )
from sql studio the word rname is marked red !!!
Is it possible to use a recursive query as subquery ???
Any help welcomed
best regards
Albert
|
|
