Re: SQL QUESTION (ADVANCED ??)

Hi Jos,

JK>   table A        table B       table C
JK>   acol          acol ccol       ccol
JK>
JK>    A1            A1 - C1         C1
JK>    A2            A2 - C1         C2
JK>    A3            A2 - C2         C3
JK>    A4            A3 - C2         C4
JK>    A5            A3 - C3         C5
JK>                  A4 - C3
JK>                  A4 - C4
JK>                  A5 - C4
JK>                  A5 - C5

The obvious answer would be building two views, which give all connected acols to a given acol, and then use two recursive queries to retrieve the whole group. It should look something like this:

 /* view gives all directly connected A's which are greater than acol1 */

CREATE VIEW connected_acols_up (acol1, acol2) AS
(SELECT B1.acol, B2.acol

   FROM B B1,
        B B2,
  WHERE B1.ccol = B2.ccol
    AND B1.acol < B2.acol)
/

 /* same, just the other way round. We need two views so we don't get into a loop */

CREATE VIEW connected_acols_down (acol1, acol2) AS
(SELECT B1.acol, B2.acol

   FROM B B1,
        B B2,
  WHERE B1.ccol = B2.ccol
    AND B1.acol > B2.acol)
/

 /* given A-value "Ax", find the lowest connected A. */

COLUMN lowest_a NEW_VALUE new_start_a

SELECT NVL(MIN (acol1),'Ax') lowest_A
  FROM connected_acols_down
CONNECT BY prior acol2 = acol1
 START WITH acol1 = 'Ax'
/

 /* now that we have the starting point, recursively find all connected A's */

SELECT acol1
  FROM connected_acols_up
CONNECT BY acol2 = prior acol1
 START WITH acol1 = &&new_start_a
/

(Can't try all this right now at home, but you should get the idea. Sorry if i
mixed up "up" and "down" views or the "priors" above. I always have to try out where to put the "prior"...)

Just one problem left: With version 6, you can't use CONNECT BY on a JOIN
(which our VIEWs actually are). Is this different for version 7?

Christian

---
Mail >16 KB? cv_at_pe2.escape.de!