ticky sql

Hi All.

I am very much new to Oracle. I am having a small problem. Let me explain. The queries bellow are meaningless but they do illustrate the problem. Let say I have a table name(id, name, fullname_lc)

I can run this query:

select (select name from name where id = n1.id) from name n1 where fullname_lc like 'fire.%';

but when I try this one:

select (select name from (select id, name from name where id = n1.id) where length(name) < 3 ) from name n1 where fullname_lc like 'fire.%';

I get
ERROR at line 1:
ORA-00904: "N1"."ID": invalid identifier

In other words if there are two levels of subselects I don't seem to be able to refer to the outer tables from the deepest subselect.

The question is. Is there are way to do this?

--
Dmitri Priimak