Computed Fields Reuse

Hi,
I have the following query
SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
Access allows the calling of computed fields by using the column alias.
Thx in advanceOn Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
>Hi,
>I have the following query
>SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
>Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
>Access allows the calling of computed fields by using the column alias.
>Thx in advance
Hi JL,
This is not allowed in standard SQL. The common workarounds are:
* Simply repeat the expression, as in your example above
* Use a derived table. This is especially useful if the expression is long
and complicated. In your case, it would be overkill, but to demonstrate
the technique:
SELECT OrderQ, OrderQ * Tax AS SalesAmount
FROM (SELECT Prod * Qty AS OrderQ, Tax
FROM Orders) AS X
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks.
I dont understand why it's not allowed in standard SQL. Is this something
Yukon would improve?
"Hugo Kornelis" wrote:
> On Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
> >Hi,
> >I have the following query
> >
> >SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
> >
> >Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
> >Access allows the calling of computed fields by using the column alias.
> >
> >Thx in advance
> Hi JL,
> This is not allowed in standard SQL. The common workarounds are:
> * Simply repeat the expression, as in your example above
> * Use a derived table. This is especially useful if the expression is long
> and complicated. In your case, it would be overkill, but to demonstrate
> the technique:
> SELECT OrderQ, OrderQ * Tax AS SalesAmount
> FROM (SELECT Prod * Qty AS OrderQ, Tax
> FROM Orders) AS X
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||This is the way the SQL language is designed, in the ANSI SQL standard. So, access doesn't follow
the ANSI standard. Logically, a SQL statement is processed in the following order:
FROM
WHERE
GROUP BY
HAVING
SELECT <column list>
ORDER BY
TOP (Not in ANSI SQL standard)
Also, the SELECT list is not processed sequentially from left to right, all happens at the same time
(logically). If that were not the case, it would be difficult to do things like:
UPDATE tbl
SET colA = colB, colB = colA
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"JL" <JL@.discussions.microsoft.com> wrote in message
news:A2B2AD3B-A0B0-4A1A-9297-68AE652E8E17@.microsoft.com...
> Thanks.
> I dont understand why it's not allowed in standard SQL. Is this something
> Yukon would improve?
> "Hugo Kornelis" wrote:
>> On Tue, 4 Jan 2005 13:11:02 -0800, JL wrote:
>> >Hi,
>> >I have the following query
>> >
>> >SELECT PROD*QTY as ORDERQ, PROD*QTY*TAX as SALESAMOUNT FROM orders
>> >
>> >Is there a way to eliminate the need to repeat the formula (PROD*QTY)?
>> >Access allows the calling of computed fields by using the column alias.
>> >
>> >Thx in advance
>> Hi JL,
>> This is not allowed in standard SQL. The common workarounds are:
>> * Simply repeat the expression, as in your example above
>> * Use a derived table. This is especially useful if the expression is long
>> and complicated. In your case, it would be overkill, but to demonstrate
>> the technique:
>> SELECT OrderQ, OrderQ * Tax AS SalesAmount
>> FROM (SELECT Prod * Qty AS OrderQ, Tax
>> FROM Orders) AS X
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
>Thanks.
>I dont understand why it's not allowed in standard SQL. Is this something
>Yukon would improve?
Hi JL,
The full explanation is posted on a regular basis by Joe Celko. Click the
following link to find several copies of his explanation on Google Groups:
http://groups-beta.google.com/groups?as_q=%22here%20is%20how%20a%20select%20works%22&safe=images&as_uauthors=Celko&lr=&hl=en
The important part of his explanation is that the complete SELECT clause
is evaluated at once. Column aliases don't exist until AFTER the SELECT
clause is evaluated. (And since evaluating the SELECT clause is the last
step, they don't exist during evaluation of the WHERE clause either - that
is another common question in this group).
I don't know aboout Yukon, but I'd be surprised if this feature would be
added. Just think about the possible misinterpretations. What output would
you expect from this query:
SELECT (A - B) AS A, (B - A) AS B
FROM SomeTable
I agree that the above query is silly, but it is valid syntax and there's
no way to decide if each of the A's and B's refer to the column or to the
alias. And the same problem suddenly looks a lot less confusing if you
look at some other examples, that are all quite likely to be used in many
places in production code:
SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
ROUND (SomeValue, 2) AS SomeValue,
CONVERT (char(10), SomeDate, 101) AS SomeDate,
SUM (SalesTotal) AS SalesTotal,
...
FROM ...
Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
parts of the SELECT clause, which one would you expect to be used? Can you
see the confusion this would cause?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:
> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
> >Thanks.
> >
> >I dont understand why it's not allowed in standard SQL. Is this something
> >Yukon would improve?
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
> http://groups-beta.google.com/groups?as_q=%22here%20is%20how%20a%20select%20works%22&safe=images&as_uauthors=Celko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Another thing to keep in mind is that SQL Server DOES provide you with the
concept of a DERIVED TABLE, as in the example that was provided to you. MS
Access CAN NOT; you always have to actually save this as another query
definition first.
So, yes, there are some drawbacks; however, there are advantages too.
Sincerely,
Anthony Thomas
"JL" <JL@.discussions.microsoft.com> wrote in message
news:8EF0B4A0-B69E-4BD0-ABF6-EC6C64C259A7@.microsoft.com...
Your explanation is very helpful. I can see why it's better not to permit
this kind of column naming. Thx.
"Hugo Kornelis" wrote:
> On Tue, 4 Jan 2005 14:41:02 -0800, JL wrote:
> >Thanks.
> >
> >I dont understand why it's not allowed in standard SQL. Is this
something
> >Yukon would improve?
> Hi JL,
> The full explanation is posted on a regular basis by Joe Celko. Click the
> following link to find several copies of his explanation on Google Groups:
>
http://groups-beta.google.com/groups?as_q=%22here%20is%20how%20a%20select%20works%22&safe=images&as_uauthors=Celko&lr=&hl=en
> The important part of his explanation is that the complete SELECT clause
> is evaluated at once. Column aliases don't exist until AFTER the SELECT
> clause is evaluated. (And since evaluating the SELECT clause is the last
> step, they don't exist during evaluation of the WHERE clause either - that
> is another common question in this group).
> I don't know aboout Yukon, but I'd be surprised if this feature would be
> added. Just think about the possible misinterpretations. What output would
> you expect from this query:
> SELECT (A - B) AS A, (B - A) AS B
> FROM SomeTable
> I agree that the above query is silly, but it is valid syntax and there's
> no way to decide if each of the A's and B's refer to the column or to the
> alias. And the same problem suddenly looks a lot less confusing if you
> look at some other examples, that are all quite likely to be used in many
> places in production code:
> SELECT COALESCE (SomeColumn, 0) AS SomeColumn,
> ROUND (SomeValue, 2) AS SomeValue,
> CONVERT (char(10), SomeDate, 101) AS SomeDate,
> SUM (SalesTotal) AS SalesTotal,
> ...
> FROM ...
> Now if SomeColumn, SomeValue, SomeDate and SalesTotal are re-used in other
> parts of the SELECT clause, which one would you expect to be used? Can you
> see the confusion this would cause?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>