Peter, The reason it's not working is not because of the alias, but because you are trying to use the alias in the SELECT clause. MySQL only allows you to reference aliases in the GROUP BY, ORDER BY, or HAVING clauses. (http://dev.mysql.com/doc/refman/5.0/en/problems-with- alias.html)
You'll need to re-write the full equation used to create the alias and add parenthesis as necessary. One other big note on this query: You didn't include all of the WHERE clause joins for the tables in your provided SQL, but you should really perform your table JOINs in the FROM clause (i.e. INNER JOIN Sites ON visaudactweight.SiteID = Sites.SiteID). You will see performance improvements on query execution inversely proportional to the number of records in your tables by doing this - up to 80-85%. HTH, Jon On Oct 20, 2007, at 11:56 AM, Peter Tanswell wrote: > Hi there > > I have a query that I have created - it works against a MS-Access > database > but does not work with MySQL. I have searched through the MySQL > notes that I > have but cant find out why - I think its because of the AS MatTotal > section > of the query. > > Thanks in advance for feedback > > > SELECT visaudactweight.RecID, Sites.SiteName, > visaudactweight.VADateTime, > CompanyNames.CompanyName, GateKeeperRecs.RegoNo, > GKeeperNames.GKeeperName, > AuditorNames.AuditorName, GateKeeperRecs.NetWeight, > (visaudactweight.Asphalt + visaudactweight.Cardboard_CompactedDry + > visaudactweight.Cardboard_CompactedWet + > visaudactweight.Cardboard_LooseDry+ > visaudactweight.Cardboard_LooseWet + visaudactweight.Cardboard_Waxed + > visaudactweight.Clay + visaudactweight.CleanFill + > visaudactweight.Concrete+ > visaudactweight.Food_Kitchen + > visaudactweight.Garbags + visaudactweight.Glass + > visaudactweight.Leather_Textiles + > visaudactweight.Leather_TextilesFurn + > visaudactweight.Leather_TextilesMatt + > visaudactweight.Leather_TextilesCar + > visaudactweight.Metal_Ferrous + visaudactweight.Metal_NonFerrous + > visaudactweight.Paper + > visaudactweight.Plastic_BagsFilm + visaudactweight.Plastic_Hard + > visaudactweight.PlasterBoard + visaudactweight.Polystyrene + > visaudactweight.Rocks_Bricks + visaudactweight.Rubble + > visaudactweight.Soil+ > visaudactweight.Tyres_Rubber + visaudactweight.Vegetation_Garden + > visaudactweight.Wood_Timber + > visaudactweight.Wood_Pallets + visaudactweight.Wood_Fencing + > visaudactweight.Wood_Furniture + visaudactweight.Other_CD + > visaudactweight.Other_CI + visaudactweight.Hazardous) > AS MatTotal, ((MatTotal - > GateKeeperRecs.NetWeight)/GateKeeperRecs.NetWeight)*100 > as PercentDiff > FROM visaudactweight, Sites, AuditorNames, CompanyNames, > GateKeeperRecs, > GKeeperNames > WHERE (((((visaudactweight.SiteID = Sites.SiteID) > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion 8 - Build next generation apps today, with easy PDF and Ajax features - download now http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:291669 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
