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
