Help selecting an alias

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

I don't understand what you mean. Is the column SQL, SQL Server Code and the
column Oracle, Oracle Code? If so why would the alias SQL be 'one' and the
alias Oracle be 'two'

> SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' AND
> Oracle = 'two'
As written I assume you mean something like

Select SQL, Oracle from
(SELECT Cod1 as SQL, Cod2 as Oracle FROM table) ss
WHERE SQL = 'one' AND Oracle = 'two'

Which is the same as

SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE Cod1 = 'one' AND Cod2 =
'two'

neither of which appear to be too useful.

Please post some DDL and expected output.
--
-Dick Christoph
"Pumkin" <PopClaudia@gmail.com> wrote in message
news:1143202849.620253.156550@z34g2000cwc.googlegroups.com...
> 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
>

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

Thank You all... Your idea with the table within the table really
worked.

SELECT SQL, Oracle
FROM   (SELECT Cod1 AS SQL, Cod2 AS Oracle FROM tbl) AS d
WHERE  SQL = 'one' AND Oracle = 'two'
That is the final select that worked for me.