From: JD

To: Editor

Date: 12 Apr 2004

 

The bottom of page 179 reads:

 

The problem is that if a similar query were run against the LINKS table in Figure 7.5, namely

 

SELECT ccode, ‘P1’ AS pcode

FROM links

CONNECT BY PRIOR ccode = pcode

START with ccode = ‘P1’

 

Two things:

 

1. The LINKS in 7.5 consists of (CP#, PP#). I’m guessing that CP# and PP# correspond with CCODE and PCODE, respectively, but

 

2. There is no value ‘P1’ in CP#.

 

I got the duplicate results using:

 

SELECT cp#, 'P1' AS "Parent"

FROM links

CONNECT BY PRIOR cp# = pp#

START WITH pp# = 'P1'

 

No nitpicking or stone throwing, here; I’m just letting you know...

 

And thanks for your persistence.

 

With the changes I made, the implementation give the results you predicted, and your point regarding the lack of hierarchy-handling capability is well made.

 

I revisited your book as I was trying to explain to a colleague that the START … CONNECT wasn’t a suitable solution to his current problem.  The bust on page 179 slowed me down a little bit…

 

Oracle does (very quietly) make the point that their START … CONNECT feature is suitable for handling hierarchies.  Oracle should, perhaps, emphasize that this feature operates correctly only on “true hierarchies” as you described them earlier in the chapter.  The use of imprecisely defined jargon - or the imprecise use of precisely defined jargon – might be part of the problem.  It’s everywhere, isn’t it?

 

From this I’ve concluded that Oracle’s START … CONNECT only works when the data values avoid ambiguity.  And, in those cases, it’s not a very helpful feature – ‘cause there’s very little to CONNECT!

 

 

From: Fabian Pascal

To: JD

 

There are several problems with the feature, and only one is that it works only in very simple cases.

Looks like you're right. Apparently I initially used different column names, then changed them but did not correct the SQL statement.

It was the job of the copy editor to locate such errors, but the one I worked with was useless, hence the several errors that were undetected (see Errata). I even wrote an article on that subject.

Nobody caught it before you. Draw your own conclusions.

 

 

Posted 6/18/04