Re: ticky sql

> 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.
I think so.

> 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.%';
you can change like this.

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

but I think this is what you actually wanna query.

select name from name
where length(name) < 3
and fullname_lc like 'fire.%';

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?
Received on Tue Nov 23 2004 - 19:51:55 CST