Convert this Jet SQL statement to T-SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Convert this Jet SQL statement to T-SQL

Author

Topic

kamikaze
Starting Member

8 Posts

Posted - 2010-03-10 : 08:45:45

SELECT tblJobDetails.*, OwnedVehicle.CO2 AS VCO2, ([EndMileage]-[StartMileage]*([VCO2])) AS tot1, tblPostcodes.x AS xPosd, tblPostcodes.y AS yPosd
FROM tblStatus INNER JOIN (tblPostcodes INNER JOIN (tblJobDetails LEFT JOIN OwnedVehicle ON tblJobDetails.VehicleID = OwnedVehicle.VehicleID) ON tblPostcodes.PostcodeID = tblJobDetails.PostcodeID) ON tblStatus.StatusID = tblJobDetails.StatusID;

Can anybody help convert this JET SQL statement to T-SQL? the problem lies when i call VCO2 in my calulation. Since this is an expression from a previuos line in the same query. how do i go about making this work and retain my expression collumn.

ummm...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 09:28:26

SELECT tblJobDetails.*, OwnedVehicle.CO2 AS VCO2, ([EndMileage]-[StartMileage]*([OwnedVehicle.CO2])) AS tot1, tblPostcodes.x AS xPosd, tblPostcodes.y AS yPosd
FROM tblStatus INNER JOIN (tblPostcodes INNER JOIN (tblJobDetails LEFT JOIN OwnedVehicle ON tblJobDetails.VehicleID = OwnedVehicle.VehicleID) ON tblPostcodes.PostcodeID = tblJobDetails.PostcodeID) ON tblStatus.StatusID = tblJobDetails.StatusID;

Madhivanan

Failing to plan is Planning to fail

kamikaze
Starting Member

8 Posts

Posted - 2010-03-10 : 13:10:02

thank you very much for your time and effort madhivanan. However this ststement still throws back the same error in SQL server 2005 "error Message: Invalid Column name 'OwnedVehicle.VCO2' " I dont know where it's going wrong somehow the expression is not being recognised as an expreession.. Please is there any other suggestion is welcome

ummm...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-10 : 15:21:59

You have to write OwnedVehicle.CO2 and not OwnedVehicle.VCO2
If that isn't the problem then show the actual query.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-11 : 02:12:14

quote:
Originally posted by kamikaze

thank you very much for your time and effort madhivanan. However this ststement still throws back the same error in SQL server 2005 "error Message: Invalid Column name 'OwnedVehicle.VCO2' " I dont know where it's going wrong somehow the expression is not being recognised as an expreession.. Please is there any other suggestion is welcome

ummm...

Can you post your new code that throws error?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources