On 2012-12-02 18:16:17 +0100, Thomas Steinmaurer wrote:
>
>
> >>> >> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé
> >>> <[1][1][1][email protected]>
> >>> >> 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?.
> >
> > I don't have an answer out-of-the box, but this can be easily checked by
> > inspecting the execution plan and I/O statistics.
>
> Answering myself: The following derived table query on the primary key
> on a table with ~108.000 records results in one indexed read.
>
> select * from (
> select
> id as id1
> from
> accommodation
> )
> where
> id1 = 29
>
That's great! thank you.
--
Leonardo M. Ramé
http://leonardorame.blogspot.com
