November 3, 2004 at 2:51 am
Hi,
I've been sitting here for more than a day trying to figure out why this query will not run. I'm practically gnawing the desk at the moment! Query Analyser seems to be complaining about the aliases I am using for "Max(s.quantity) AS [qty]", "Max(o.originaltotal) AS [order value]", and "[order value]/[qty] AS AVGPrintPrice" (the error message is shown at the bottom).
SELECT d.storeid, o.ordernumber, o.emailaddress, o.friendlyname, pa.status,
Max(o.originaltotal) AS [order value] , Max(o.orderid) AS orderid,
Max(o.orderdate) AS [orderdate], Max(s.quantity) as [qty],
[order value]/[qty] AS AVGPrintPrice, d.telno
FROM ((([net-prints-reporting].dbo.payment pa
INNER JOIN [net-prints].dbo.orders o ON pa.orderid = o.orderid)
INNER JOIN [net-prints].dbo.deliveryaddress d ON (o.orderid = d.orderid))
INNER JOIN [net-prints].dbo.productline p ON o.orderid = p.orderid)
INNER JOIN [Sum of Quantity] s ON o.orderid = s.orderid
GROUP BY d.storeid, o.ordernumber, o.emailaddress, o.friendlyname,
pa.status, AVGPrintPrice, d.telno
HAVING (((o.ordernumber) LIKE 'NWP*')
AND ((Max(o.orderdate))>dateadd(day, -10, getdate())))
The following error message is displayed multiple times for [AVGPrintPrice], and once each for [qty] and [order value], where X is one of these aliases.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'X'.
I'm using SQL Server 2000.
I'd be really grateful if anyone has any suggestions!
Thanks,
Richard
November 3, 2004 at 3:03 am
I think you can't use the aliases here at all. Try and replace this with (Max(o.originaltotal)/Max(s.quantity) as [qty]) AS AVGPrintPrice
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 3, 2004 at 7:27 am
Thanks Frank, that seems to have done the trick - although now query analyser is now complaining about AVGPrintPrice being an invalid column name if I try and group by AVGPrintPrice...
I'm sure I'll figure it out though.
Cheers,
Richard.
November 4, 2004 at 7:06 am
The only time you can use an aliased field name is in the order by clause... or if u are using it in a subquery
--order by
Select a*b-c as Calc1, SommeCol as Temp from dbo.Test group by a*b-c, SommeCol order by Calc1, Temp
--subquery
Select dtA.Calc1, dtA.Temp from (Select a*b-c as Calc1, SommeCol as Temp from dbo.Test group by a*b-c, SommeCol) dtA group by Calc1, Temp order by Calc1, Temp
November 5, 2004 at 1:16 pm
You have to define computed columns in a subquery to be able to use the column (by alias) in the GROUP BY clause. The aggregate functions stay in the main query.
SELECT ..., MAX() as ..., AVGPrintPrice
FROM (
SELECT ..., [order value]/[qty] as AVGPrintPrice, ...
FROM ...
WHERE ...
) x
GROUP BY ..., AVGPrintPrice, ...
HAVING ...
This would work if AVGPrintPrice was computed from table fields. In this case it is being computed from other columns defined by aggregate functions, which could be done by multiple levels of subqueries. But I suspect you have the wrong formula for this column, what you want is something like "AVG(o.originaltotal / s.quantity)" or "MAX(o.originaltotal / s.quantity)". The way you have it defined (if it worked) could divide a price in one record by a quantity from another record and return a value not associated with any record.
The MAX(orderdate) has to be in a HAVING clause, but I think your query would do less work if "ordernumber like 'NWP*'" was in a WHERE clause. HAVING is for filter conditions that can't be determined until after the records are grouped (usually involving an aggregate function).
It also looks like you should have an inner query that groups the records from orders and [Sum of Quantity], then join the results to the other tables.
Finally, what are you getting from the productline table? No fields from this table are referenced, although you may be using it to limit orderid values.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply