Hallo Elke
Thank you for your help. This suggestion works from sql studio ! First select creates rname as resultset which can be used in a second select. >From my application (connecting via ODBC) the first select is ok. The second gives me an error "unknown table rname" ??? Is this an odbc problem ?? Best regards Albert Hallo, no, it is not possible to use recursive query as subselect. But as the result of the recursive query is a named resultset, you can use its name in another's select from-part like Select * from xyz join abc where xyz.rbobjekteid in (select rbobjekteid from rname) (or use it in the join itself as third table) Elke SAP Labs Berlin Sitz der Gesellschaft/Registered Office: Walldorf, Germany Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso Plattner Registergericht/Commercial Register Mannheim No HRB 350269 Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung oder Weitergabe der E-Mail ausdrücklich untersagt. Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen Dank. This e-mail may contain trade secrets or privileged, undisclosed, or otherwise confidential information. If you have received this e-mail in error, you are hereby notified that any review, copying, or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation. > -----Original Message----- > From: Beermann, Albert [mailto:[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] > Sent: Montag, 12. Februar 2007 10:50 > To: maxdb@lists.mysql.com > Subject: How to use a recursive query as subquery ??? > > 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 > >
