Microsoft SQL Server forum - Help selecting an alias

Author

Help selecting an alias

Pumkin

2006-03-24, 7:38 am

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

DickChristoph

2006-03-24, 8:25 pm

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
>

Hugo Kornelis

2006-03-24, 8:25 pm

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

Pumkin

2006-03-27, 7:37 am

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.

Sponsored Links