I'm not sure what you're doing with the LIKE comparisons in the field list. Are you trying to return true for the matching records? It's not invalid syntax, but my instinct says that just LocationName should be in the field list (if at all) and the LIKE match should be in the where clause.
Blair On Jan 10, 2008 4:31 PM, Andrew Scott <[EMAIL PROTECTED]> wrote: > 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. > 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 -~----------~----~----~----~------~----~------~--~---
