Re: Using column aliasses in the same query

On 17 apr 2011, at 13:43, pasman pasmański wrote:

> Maybe you think about WITH queries?
>
> 2011/4/17, Robert J.C. Ivens <robert(at)roclasi(dot)com>:
>>
>> On 17 apr 2011, at 13:21, Leif Biberg Kristensen wrote:
>>
>>> On Sunday 17 April 2011 13:01:45 Robert J.C. Ivens wrote:
>>>> Hi,
>>>>
>>>> I am not sure if there ever was a feature request for using defined
>>>> column
>>>> aliases in the rest of a query. This would make queries with a lot of
>>>> logic in those aliased columns a lot smaller and this easier to
>>>> write/debug.
>>>>
>>>> I already know you can use the following syntax:
>>>>
>>>> SELECT col1, col2, col3, (col2-col3) as col4 FROM (SELECT col1, (long and
>>>> lots of logic here) as col2, col3 FROM table) s WHERE col2 < aValue
>>>>
>>>> But when you need to use (calculated) values from the actual record and
>>>> or
>>>> have sub-selects in your main select that also need to use these values
>>>> things get really hairy. I don't know if the SQL specification allows it
>>>> but I know that RDBMS's like Sybase already support this.
>>>>
>>>> Any thoughts?
>>>
>>> It's easy to define a view or an SQL function and stash the hairy logic
>>> there.
>>>
>>> regards, Leif
>>
>> True, but that is essentially the same thing as the example query I gave.
>> There are plenty of cases where this approach is not workable.
>>
>> Cheers,
>> Robert

CTE's are another option yes. But again it becomes really hairy (if not impossible) when you have a query where the calculated columns are used all over the place as input values for other subqueries.
Being able to use the aliases in the same scope would simplify things tremendously.

Cheers,
Robert