On 2012-12-02 16:59:24 +0100, Thomas Steinmaurer wrote:

>  
>
> >> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé
> <[1][1]l.rame@...>
> >> wrote:
> >>
> >> > Hi, I'm wondering if there's a way to refer columns by number in
> WHERE
> >> > conditions.
> >> >
> >> > I need this because I'm creating a generic method to create where
> >> > clauses for hopefully ANY query, in FreePascal
> >>
> >> What you suggest, sir, is blasphemy. One of the major creeds of
> relational
> >> theory is the separation of the logical from the physical. Codd and all
> >> his minions will strike you down.
> >>
> >> On the other hand, why not just use consistent aliases?
> >>
> >> select
> >> case
> >> when (a.IDADICIONAL is null) then 'TITULAR'
> >> else 'ADICIONAL'
> >> end as col1,
> >> c.idcliente as col2, c.apellido as col3, c.nombres as col4
> >> from clientes c
> >> left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
> >>
> >> >
> >> >
> >
> > Ann, column name is not the problem. What I wanted to do is to use the
> > alias in the WHERE clause, there's no solution for this in a simple
> > select.
>
> You can use a derived table.
>

Yes Thomas, I ended up using a derived table.

I have one doubt regarding derived tables. Imagine an inner query that
returns millions of records, and the outer table with a where clause that
limits the results to just a few records, how this affects to
server performance?.



Regards,
--
Leonardo M. Ramé
http://leonardorame.blogspot.com