Help selecting an alias

[Pumkin]

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]

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" <Po********@gmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.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]

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

[Erland Sommarskog]

Pumkin (Po********@gmail.com) writes:

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'

I think I would have needed to have more information about where
this query appears.

What you can do is:

SELECT SQL, Oracle
FROM (SELECT Cod1 AS SQL, Cod2 AS Oracle FROM tbl) AS d
WHERE SQL = 'one' AND Oracle = 'two'

The thiing in a parentheses in a derived table. You can use a derived
table for several purposes. Here the purpose is to define queries that
are defined in the rest in the query.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Pumkin]

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.