Received: from fslc.ser.usu.edu (fslc.ser.usu.edu [129.123.28.122]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4H1QQA94777 for ; Wed, 16 May 2001 21:26:26 -0400 (EDT) (envelope-from rmello@fslc.usu.edu) Received: (from rmello@localhost) by fslc.ser.usu.edu (8.12.0.Beta5/8.12.0.Beta5/Debian 8.12.0-1) id f4H1QF54025705; Wed, 16 May 2001 19:26:15 -0600 Date: Wed, 16 May 2001 19:26:15 -0600 From: Roberto Mello To: "?$B;3$5$s" Cc: pgsql-sql@postgresql.org Subject: Re: Give me a suggestion 'START WITH .. CONNECT BY'. Message-ID: <20010516192615.B25655@cc.usu.edu> References: <000c01c0dd1a$f1683340$6f40a8c0@isiwebnet.co.jp.isiwebnet.co.jp> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.17i In-Reply-To: <000c01c0dd1a$f1683340$6f40a8c0@isiwebnet.co.jp.isiwebnet.co.jp>; from yamahi@isi-webnet.co.jp on Tue, May 15, 2001 at 05:40:32PM +0900 X-Archive-Number: 200105/180 X-Sequence-Number: 2997 On Tue, May 15, 2001 at 05:40:32PM +0900, ?$B;3$5$s wrote: > Hello! > I try to translate a database to PostgreSQL from ORACLE, but have a regret > over a function '... START WITH .. CONNECT BY ...'. > This function is descrived in a sql sentence for ORACLE, and I could not > find in PostgreSQL. > For example > SELECT LEVEL, COMP_ITM, COMP_NAME, COMP_AMT FROM PSFILE > START WITH PRNT_ITM = 'A0010' > CONNECT BY PRIOR COMP_ITM = PRNT_ITEM; > There's no equivalent for the Oracle tree extensions in PostgreSQL. Neither LEVEL, START WITH or CONNECT BY PRIOR. There's no easy way out of this AFAIK. You'll have to re-write your code in a way that allows you to do this query in a SQL92 way. In Joe Celko's "SQL For Smarties" he talks about a nifty algorithm that you can use to do this. The OpenACS folks used that approach to port Oracle's CONNECT BY to PostgreSQL, so you could probably ask them over at openacs.org/bboard. -Roberto P.S: I plan to add this to my expanded "Porting From Oracle" chapter of the documentation. -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Air conditioned environment - Do not open Windows.