I have spent a day on this, and I can see the potential problem but having one of those days where it is just not working out.
I have an application that needed a bit of a tweak, and got so far before I ran into a problem. So here is a bit of history on this section of the application. I have a component that is to do with Car Hire, the Database structure is set in place that it is difficult to modify the database too much. It was all working fine until the client wanted to be able to filter results based on a pickup / drop off location. This then through the calculation of the Car Hire into total kaos. The query is as this to return the records. Select CarHire.CarHireId, CarHire.Location, CarHire.Inclusions, CarHire.CarSeats, CarHire.CarCode, CarHire.OperatorId, CarHire.CompanyId, CarHirePrices.CurrencyId, CarHire.CarGroup, CarHire.CarType, CarHire.RegionId, CarHire.IsActive, CarHire.GSTApplies, Currency.CU_Code, Currency.CU_Currency, Currency.CU_CountryName, Currency.CU_Symbol, Currency.CU_Position, Currency.CU_Rate, Currency.CU_DateEntered, Currency.CU_isActive, CarHireCompany.CC_Name, CarHireCompany.CC_Details, CarHirePrices.Markup, CarHirePrices.Tax, CarHirePrices.Commision, CarHirePrices.SellBy, CarHirePrices.CarHirePricesId, CarHireSeasonality.IsSeasonal, CarHireSeasonality.FromDate, CarHireSeasonality.ToDate, CarHirePrices.EnteredDate, from CarHire inner join CarHirePrices ON CarHire.CarHireId = CarHirePrices.CarHireId inner join CarHireCompany ON CarHire.CompanyId = CarHireCompany.CC_ID inner join Currency ON CarHirePrices.CurrencyId = Currency.CU_ID inner join CarHireSeasonality ON CarHirePrices.CarHirePricesId = CarHireSeasonality.CarHirePricesId where CarHire.IsActive = 1 Now that works, fine. I created two tables for pickup and dropoff, which are basic ID, CarHireId, LocationId and wanted to add it to the above code in a manner as. Select ........... <cfif LocationPickup neq ""> , PickupRegion.LocationName like '%LocationPickup%' </cfif> <cfif LocationDropOff neq ""> , DropOffRegion.LocationName like '%LocationDropOff%' </cfif> >From carHire <cfif LocationPickup neq ""> inner join regions as PickupRegion ON CarHire.CarHireId = CarHirePickup.CarHireId </cfif> <cfif LocationDropOff neq ""> inner join regions as DropOffRegion ON CarHire.CarHireId = CarHireDropOff.CarHireId </cfif> inner join CarHirePrices ON CarHire.CarHireId = CarHirePrices.CarHireId inner join CarHireCompany ON CarHire.CompanyId = CarHireCompany.CC_ID inner join Currency ON CarHirePrices.CurrencyId = Currency.CU_ID inner join CarHireSeasonality ON CarHirePrices.CarHirePricesId = CarHireSeasonality.CarHirePricesId where CarHire.IsActive = 1 But for some reason the query doesn't work and starts to complain, when there is either a Pickup or DropOff about error in syntax. Now the above was typed in a hurry so there maybe a problem. But when I run the query in SQL Manager it seems to reorganise the SQL Code so that it runs of one of the Location Tables, anyone have any ideas? Andrew Scott Senior Coldfusion Developer Aegeon Pty. Ltd. <http://www.aegeon.com.au> www.aegeon.com.au Phone: +613 9015 8628 Mobile: 0404 998 273 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
