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)
>

Complex Self Join

This is a very complex query and i have tried everything with no sucess.

I'm having 3 Tables,

Orders which is having fields like CustomerID, ManufactureID, MerchID :- These all ID Fields (around 6) are foreign key of Contacts and Address Tables.

Address Table is having AddrID(Primary Key), ShortName

Contacts is having ContID (Primary Key), AddrID (Foreign Key), PersonName

I want to retreieve info in a single query which can return a single row with following columns :-

OrderNo, CustomerName, ManufactureName, MerchName etc. (all 6 columns) by joining these 3 tables

can anybody help in this.

You will need to join each table with a unique alias.

For example:

SELECT OrderNo, CustomerName = cust.PersonName,
ManufactureName = manu.PersonName,......
FROM Orders or
JOIN Contacts cust ON cust.ContactID = or.CustomerID
JOIN Address custadd ON custadd.AddrID = cust.AddrID
JOIN Contacts manu ON manu.ContactID = or.ManufactureID
JOIN Address manuadd ON manuadd.AddrID = manu.AddrID

etc

I will warn you this will be relatively slow, if there are lots of records in Contacts.

Another way would be:

Select OrderNo,
CustomerName = (SELECT PersonName FROM Contacts cn WHERE cn.ContactID = or.CustomerID)
ManufactureName = (SELECT PersonName FROM Contacts cn WHERE cn.ContactID = or.ManufactureID)

FROM Orders or|||

Could you use one IDE (for example, Design Query in Editor in SQL Server 2005) to do this? You drag and drop your tables in the editor, and then you link them through key fields, and you pick the columns you are interested.

The query will be something look like this:

SELECT a.OrderNo, b.CustomerName, b.ManufactureName, c.PersonName as MerchName FROM Orders a INNER JOIN Contacts b ON a.CustomerID=b.ContID INNER JOIN Address c ON c.AddrID=a.AddrID

|||

Thanks for your response.

pls note i can also use seperate queries to extract each column, even that will do. Do you think that will be faster then joining all together..

|||It depends on the contacts table whether it will be faster or not. I have had experience in SQL 2000 where self joining 5 tables, worked fine, then the 6th make it take 4 times longer to return.

You will have to play with it and see what works for your situation.