>>> >> On Sat, Dec 1, 2012 at 8:09 AM, Leonardo M. Ramé
>>> <[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
--
With regards,
Thomas Steinmaurer
http://www.upscene.com/
