Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ticky sql
Dmitri Priimak <priimak_at_Stanford.EDU> wrote in message news:<cnuje0$7eo$1_at_news.Stanford.EDU>...
> 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?
No. What you are using is called inline view (i.e. subquery in FROM clause). Unlike subquery in WHERE clause, inline view can't reference objects of the main view. Received on Mon Nov 29 2004 - 11:43:47 CST