SQL & PL/SQL

Hi all,

Using rdbms 11.2.0.3, I'd like to know if there was a way to "inherit" values from a parent row when using a hierarchical query.

For example, with these values:

CREATE TABLE foohh (
 id NUMBER,
 parent NUMBER,
 name VARCHAR2(64),
 continent VARCHAR2(32)
);

INSERT INTO foohh VALUES ( 1, null, 'United States', 'AMERICA' ) ;
INSERT INTO foohh VALUES ( 2, 1, 'California', null ) ;
INSERT INTO foohh VALUES ( 3, 2, 'San Francisco', null ) ;
INSERT INTO foohh VALUES ( 4, 3, 'Golden Gate', null ) ;
INSERT INTO foohh VALUES ( 5, null, 'China', 'ASIA' ) ;
INSERT INTO foohh VALUES ( 6, 5, 'Beijing', null ) ;
INSERT INTO foohh VALUES ( 7, 6, 'Great Wall', null ) ;

I'd like to be able to display the continent of any lines.

The query below returns the leaves, but I can't see their continent:

SELECT
 id
 ,connect_by_root(parent) parent
 ,sys_connect_by_path(name, '-->') path
 ,nvl( continent, 'unknown' ) continent
FROM foohh
WHERE
 connect_by_root(parent) is null
 and connect_by_isleaf=1
CONNECT BY PRIOR id=parent
;

ID PARENT PATH CONTINENT
---------- ---------- ------------------------------------------------------------ ------------------------------
4 -->United States-->California-->San Francisco-->Golden Gate unknown
7 -->China-->Beijing-->Great Wall unknown

Is there a smart way to do that?

Thanks,

Anthony