pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Give me a suggestion 'START WITH .. CONNECT BY'.
2+ messages / 2 participants
[nested] [flat]

* Give me a suggestion 'START WITH .. CONNECT BY'.
@ 2001-05-15 08:40  山さん <yamahi@isi-webnet.co.jp>
  0 siblings, 1 reply; 2+ messages in thread

From: 山さん @ 2001-05-15 08:40 UTC (permalink / raw)
  To: pgsql-sql

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;

I  want to know an similar SQL description in PostgreSQL. Will you give me a
suggestion?
                                                              Thanks!
Hirosi Yamaoka
Co.ltd Integrated Systems Institute
701-0211 688-55 Higasiune, Okayama-city, Okayama-pref. Japan

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Give me a suggestion 'START WITH .. CONNECT BY'.
@ 2001-05-17 01:26  Roberto Mello <rmello@cc.usu.edu>
  parent: 山さん <yamahi@isi-webnet.co.jp>
  0 siblings, 0 replies; 2+ messages in thread

From: Roberto Mello @ 2001-05-17 01:26 UTC (permalink / raw)
  To: ?$B;3$5$s <yamahi@isi-webnet.co.jp>; +Cc: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-05-17 01:26 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-15 08:40 Give me a suggestion 'START WITH .. CONNECT BY'. 山さん <yamahi@isi-webnet.co.jp>
2001-05-17 01:26 ` Roberto Mello <rmello@cc.usu.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox