Andrew Just thought I would re-ignite this thread again with some more info.
Your right that in MSSQL this will work.. SELECT BI_NettPrice * BI_Tax AS Price FROM BookingItems ORDER BY Price ....but this will not, which is the issues I have come across.... SELECT BI_NettPrice * BI_Tax AS Price FROM BookingItems WHERE Price > 1 ORDER BY Price ..will return a "Invalid column name 'Price'." error message In those cases, you have to use the function in the statement to make it work, like... SELECT BI_NettPrice * BI_Tax AS Price FROM BookingItems WHERE Price > (BI_NettPrice * BI_Tax) ORDER BY Price ..or something like that Steve -----Original Message----- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Scott Sent: Friday, 22 June 2007 1:52 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CF Query Question Steve, Are you sure? SELECT BI_NettPrice * BI_Tax AS Price FROM BookingItems ORDER BY Price Works for me. I know I use it all the time when writing complex queries. Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. www.aegeon.com.au Phone: +613 8676 4223 Mobile: 0404 998 273 -----Original Message----- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Steve Onnis Sent: Friday, 22 June 2007 12:42 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: CF Query Question Select loanno, LoanProgram, BorrowerName, LoanFacility, BorRate, InvstLoanRate, SettlementDate, FeeFreeRate, OtherFeeRate, PenaltyRate, BorRate-InvstLoanRate as lender_margin from trails -- MySQL ORDER BY lender_margin -- MSSQL ORDER BY CONVERT(NUMERIC, BorRate-InvstLoanRate) MySQL lets you used aliased columns in WHERE, ORDER BY, HAVING and GROUP BY where MSSQL doesn't let you ________________________________ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Friday, 22 June 2007 12:22 PM To: cfaussie@googlegroups.com Subject: [cfaussie] CF Query Question Hi, have the following query select loanno, LoanProgram, BorrowerName, LoanFacility, BorRate, InvstLoanRate, SettlementDate, FeeFreeRate, OtherFeeRate, PenaltyRate, BorRate-InvstLoanRate as lender_margin from trails I am wondering is there is a way that i can sort the output based on the value of "lender_margin" given its value is created from two other columns in the table, whilst lender_margin is not a physical column in the table Regards Claude --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
