Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle Conditional CONNECT BY - Possible?

Re: Oracle Conditional CONNECT BY - Possible?

From: Edzard <edzard_at_volcanomail.com>
Date: 17 Mar 2002 04:29:49 -0800
Message-ID: <5d75e934.0203170429.727f5b0e@posting.google.com>


Hi,

Although I'm not familiar with the subject "website building application", I like to react to your message. The procedural code that you listed is clear to read, also without the particular subject knowledge. It is a nice demo of the use of records and tables within PL/SQL, if I may judge.

But I wonder why the CONNECT BY was not suited. I assume that a solution within SQL is peferred over the use of procedural code. From your code, the following SQL can be derived:

CONNECT BY PRIOR.link_website_id IS NOT NULL AND website_id = PRIOR.link_website_id
AND NVL (parent_id, -1) = NVL (PRIOR.link_element_id, -1)) OR PRIOR.link_website_id IS NULL
AND PRIOR.is_parent = ‘Y'
AND website_id = PRIOR.website_id
AND NVL (parent_id, -1) = NVL (PRIOR.parent_id, -1)

As I say, I'm not familiar with what is really meant here, but I hope that this SQL correctly expresses the application logic. I also assume that you started with a similar expression, maybe using an equivalent DECODE or CASE construction. Why did that not succeed? Is there some restriction on the clauses in a CONNECT BY clause? I'm only aware of a restriction on the FROM part, you can not use CONNECT BY on a join. But that can easily be worked around by including the join in a view. May be the use of a view is a solution here too.

Like to hear,

Edzard Received on Sun Mar 17 2002 - 06:29:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US