On 2012-12-01 10:09:15 -0300, Leonardo M. Ramé 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.
>
> I need this:
>
> select col1, col2, colN
> from ...
> join ...
> where
> col1 = condition1, col2 = condition2
>
> But now I need to know the name of the column in advance, and some times
> the column name does refer to a real column, but a CASE statement or a
> COALESCE.
>

Let me try to explain what kind of queries I'm working with:

For example:

select
case
when (a.IDADICIONAL is null) then 'TITULAR'
else 'ADICIONAL'
end as Tipo,
c.idcliente, c.apellido, c.nombres
from clientes c
left join adicionales a on a.IDADICIONAL = c.IDCLIENTE

I would like to use the "Tipo" column in a where statement.

One way to do this is to create a View, but sometimes I can't alter the
DLL of some databases.

Another way, is to do this

select tipo, idcliente, apellido, nombres from
(
select
case
when (a.IDADICIONAL is null) then 'TITULAR'
else 'ADICIONAL'
end as Tipo,
c.idcliente, c.apellido, c.nombres
from clientes c
left join adicionales a on a.IDADICIONAL = c.IDCLIENTE
) as foo
where foo.tipo like 'AD%'

Here I don't know if the subquery will get ALL the records and then
apply a filter on it. What if the subquery contains millions of
records?.

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