Hi,

I need to do something like this:

SELECT Field1 AS Toto, Toto FROM Table1

But i have a "Toto incorrect column name".

Is there a way to do this (It works in Access) ?

Thanks.

Many thanks.
This solution is not too "slow" ?

"Steve Kass" <skass@drew.edu> a écrit dans le message de
news:uMrJLf61DHA.2000@TK2MSFTNGP11.phx.gbl...
[quoted text, click to view]

[quoted text, click to view]

Yes it is...
Imagine a complex select case upon 7 or 8 fields.
If i have to perform more operations on the result (with other fields), i
could just *recall* the final result with, for example, the alias.
It perfectly works on some other RDBMS, because RDMBS is not the point here.

thanks.

It's so good to be understood, sometimes... ;o)
Thanks again for your very comprehensive answers !

[quoted text, click to view]
If i have to perform more operations on the result (with other fields
[sic]), i could just *recall* the final result with, for example, the
alias. <<

The relational model is **precisely** the point. Fields are not columns.
In a procedural programming language, fields are read from left to
right; in SQL, the entire row is read at once.

Here is how a SELECT works in SQL ... at least in theory. Real products
will optimize things when they can.

a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.

b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE
clause is applied to the working set in the FROM clause.

c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the new
grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.

d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.

e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions in
the SELECT are done after all the other clauses are done. The “AS”
operator can also give names to expressions in the SELECT list. These
new names come into existence all at once, but after the WHERE clause,
GROUP BY clause and HAVING clause has been executed; you cannot use them
in the SELECT list or the WHERE clause for that reason.

If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated as
matching (just like in the GROUP BY).

f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.

[quoted text, click to view]
point here. <<

No, it works in file systems, never in SQL. I hope that you don't think
that MySQL or ACCESS are RDBMSs.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

[quoted text, click to view]
probably even better on SQL Server, since the values you will be using
will already be calculated. If you repeat the calculation three times,
it will have to be evaluated three times. <<

Louis, you usually know better! The optimizer is free to do the
calculation once and facotr out common subexpressions, but you cannot
reference the calculation as if you were still in a procedural language.
There are products that produce the result sets column by column,
instead of row by row like SQL Server.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

It is free to, but I don't get the feeling that the optimizer would be smart
enough (or at least take the time) to deal with something along the lines
of:


SELECT (column1 * 10) + 20, column1 * 10, column1 * 20, power((column1 *
10),2)
FROM ...

and realize that column1 * 10 is a part of every calculation. I was just
suggesting that it might be a bit better performance wise to rewrite the
query as:

SELECT col1Times10 + 20, col1Times10, col1Times10 * 2, power(col1Times10,2)
FROM (SELECT column1 * 10 as col1Times10
FROM

Mathematically they are equivalent, and should be precisely the same as the
type of thing that the optimizer does, but I don't have that kind of faith
:) I should have been a bit more soft in my answer, stating that it might
have to be done this way, or at least it is easier to code. I know that I
once had to do the standard deviation calculation in 6.5 about 20 times in a
query once, and it took three lines of SQL expression to make it work
(dealing with nulls was a real pain.) They added it to 2000, so I won't be
going back there (and I have thankfully forgotten how I did it!)

When I executed the statements above, using the Northwind..orders tables, it
did some substituion:

SELECT (freight * 10) + 20, freight * 10, freight * 20, power((freight *
10),2)
FROM orders

was evaluated to:

Compute Scalar(DEFINE:([Expr1002]=[Orders].[Freight]*10.00+20.00,
[Expr1003]=[Orders].[Freight]*10.00, [Expr1004]=[Orders].[Freight]*20.00,
[Expr1005]=power([Orders].[Freight]*10.00, 2)))

while

SELECT col1Times10 + 20, col1Times10, col1Times10 * 2, power(col1Times10,2)
FROM (SELECT freight * 10 as col1Times10
FROM orders) as orders

was evaluated to:

Compute Scalar(DEFINE:([Expr1002]=[Orders].[Freight]*10.00+20.00,
[Expr1003]=[Orders].[Freight]*10.00,
[Expr1004]=[Orders].[Freight]*10.00*2.00,
[Expr1005]=power([Orders].[Freight]*10.00, 2)))

Which is pretty much the same thing. It was easier to write though, if
col1Times10 took three or four lines, or it was a non-deterministic function
call.


However, to allow this sort of thing:

select column1 * 10 as colTimes10, colTimes10 *2 ...

syntactically would not absolutely require column by column evaluation (not
doubting that is why Access allows it though :). It would require a bit
more intelligent of a compiler. As long as you evaluate this expression
from left to right, there is not a problem doing macro like expansion of
attributes. It would not make it any faster though.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[quoted text, click to view]