Simple query question

Question

Post reply

Simple query question

Lars Jensen

SSC Journeyman

Points: 80
More actions
December 24, 2006 at 7:39 am

#171647

I have recently upgraded from Access 2003 to SQL Server 2005. In Access I had a query that basically did something like the following:
SELECT Amount as Amount1, Amount1 / 10 AS Amount2, Amount2 / 10 AS Amount3
FROM tblFlow
This is not the actual SELECT statement, but the basics are the same - I know the above does not make much sense.
When this is copied to a view in SQl Server, I get an error stating that the column Amount2 can not be found. It seems that there is a problem referencing calculated columns when used in another column for calculating.
I expect that the above should be possible to do in a View. Do I need to declare the Amount2 and Amount3 columns as variables, or what prefix should be used to make sure that SQL server can reference the calculated fields?
Thanks for help up-front - I am new to SQL Server....

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Let's assume this scenario :

SELECT COUNT(*) AS Total, SUM(Col1) AS SumCOL1, SumCOL1 *1.0 / Total AS Avg

FROM dbo.YourTable

As you stated, the columns SumCOL1 and Total do not yet exist neither in the base table/view nor in the query. That is why you are getting this error message. You have 2 options from there :

1 - Use a derived table

SELECT Total, SumCOL1, SumCOL1 / Total AS AVG FROM (

SELECT COUNT(*) AS Total, SUM(Col1) AS SumCOL1 FROM dbo.YourTable) dtTS

2 - Reuse the expression in the 3rd column.

SELECT COUNT(*) AS Total, SUM(Col1) AS SumCOL1, SUM(Col1) *1.0 / COUNT(*) AS Avg

Both will get you the same performance (untested), it's just a matter of prefference and cleanliness of the code.

steve block SSCertifiable Points: 7698 More actions · Answer 2

If amounts 2 & 3 are truly calculations based upon other calculations, could they be expressed in terms of the original amount field?

SELECT Amount as Amount1, Amount / 10 AS Amount2, Amount / 100 AS Amount3

FROM tblFlow

Steve

Lars Jensen SSC Journeyman Points: 80 More actions · Answer 3

I changed the structure and imbedded a subquery to do part one of the calculations, and then did the rest in the original query.

It could all be done each time, but it was a lot of calculations to find market value of a bond basend on a lot of factors, so repeating that four times in the code would not be nice.

Thanks for the help.

Regards

DaveItz SSCrazy Points: 2146 More actions · Answer 4

I don't think there is a clean way to code something like that, short of, as was suggested, using subqueries or views.

If Access' parser could manage it, SQL Server should be able to handle these kinds of issues.

(By having it add an optional way of aliasing the resulting columns of a query, such as ::.Amount1, and making a quick check for circular referencing and doing a direct inline text replacement, when validating, the rest of the logic would stay the same and it would be able to optimize it exactly as before. I don't know why SQL Server never got around to adding this functionality. Maybe they themselves couldn't find a clean way to code it without adding an additional parsing pass.)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

That would be a nice question to ask the developpement team. I'm sure there must be a very good reason but I just cannot imagine it for now. As you stated there must be either a performance reason or circular refference problem but I can't say for sure which one it is.