Subject Re: [firebird-support] Unknown alias
Author Helen Borrie
At 03:31 PM 25/09/2004 +0200, you wrote:
>What's wrong with this query:
>select Table1.*, (select first 1 Table2.A from Table2 where <some_criteria>)
>as NAME, Table3.Field1
>from Table1
>inner join Table3
>on NAME = Table3.Field1
>where <some_criteria>
>order by <some_criteria>
>I get an error, that NAME is unknown field.
>Is it not permited to use an alias in the join ?

NAME **is** an unknown field - it is the alias for an expression.

Actually, it's not permitted to OMIT aliases on *any* columns in statements
that have joins.

In any case, you don't need the expression NAME in your set, since you are
getting this value from Table 3:

from Table1 t1
where exists (select 1 from Table2 t2
where t3.Field1 = t2.A
and <some fully aliased criteria correlating Table2 with Table1>)
and <some_fully_aliased_criteria>
order by <some_fully_aliased_criteria > [or degree numbers]