Just an FYI: if one is on CF8, you can see the generated SQL in a dump of the query (or by using the RESULT attribute and dumping it). And even in CF7 and 6, I seem to recall that there are some undocumented java objects you can call to show you the generated SQL in a query. (Not denying the value of what you proposed, Haikal, and FusionReactor has the same sort of jdbc wrapper feature that can also show you the generated SQL. But if one wants JUST that, the monitor tools are overkill--though most would benefit from running either one.)
/charlie -----Original Message----- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Haikal Saadh Sent: Thursday, January 10, 2008 12:41 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Small but large problem Are you using SeeFusion? If you are, you should be able to see what the exact SQL passed to CF is. (And also, why are you not using cfqueryparam ?) Andrew Scott 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 <http://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 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 -~----------~----~----~----~------~----~------~--~---
