Posted by Hugo Kornelis on 03/25/06 00:10

On 24 Mar 2006 04:20:49 -0800, Pumkin wrote:

>Hello guys, I need help in something as I don't know if it is possible
>what I want.
>I have a select like this...
>
>SELECT Cod1 as SQL, Cod2 as Oracle FROM table
>
>and I need to sort by alias SQL or Oracle as the select is composed
>dinamically so it could be either Cod1 as SQL or Cod2 as SQL and the
>user needs to filter the data using SQL or ORACLE.
>
>I need something like this:
>
>SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
>Oracle = 'two'
>
>Any ideas?
>
>Thank you

Hi Pumkin,

You can't reference an alias directly (except in the ORDER BY clause),
but you can do it indirectly if you use a derived table:

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle
FROM YourTable) AS Der
WHERE SQL = 'one'
AND Oracle = 'two'
ORDER BY SQL ASC, Oracle DESC

> the select is composed
>dinamically

Please read the following article very carefully:

http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP

[Back to original message]