>- see footer for list info -< But he noted - there is no SQL error... - the SQL is simply bringing back incorrect data....
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gary F Sent: 03 February 2006 14:32 To: Coldfusion Development Subject: Re: [CF-Dev] SQL CASE statements >- see footer for list info -< Apologies if you have already done this, but have you tried CFOUTPUTing the whole SQL statement and then copying it into Studio/Navigator (or whatever) to execute it directly from the db server? This often helps me debug syntax, especially since most sql tools will hint at what the problem could be. I'm not that hot at sql so sorry I can't figure it out. Gary. On 2/3/06, Snake <[EMAIL PROTECTED]> wrote: > > >- see footer for list info -< > I have the following query. > > <CFQUERY name="QRY_AllFields" datasource="#request.global.database.DSN#" > username="#request.global.database.username#" > password="#request.global.database.password#" blockfactor="100"> > SELECT c.CountryCode, c.Country, r.regionid, r.region, ct.cityid, > ct.city, > (c.countrycode + '_' + > CASE > WHEN isnumeric(r.regionID) = 1 THEN cast(r.regionID > AS varchar) > else '0' > END > +'_'+ > CASE > WHEN isnumeric(ct.cityID) = 1 THEN cast(ct.cityIDAS > varchar) > else '0' > END) > > > AS locationstring > FROM ( countries c LEFT JOIN factor_country_lookup FCL ON > c.CountryCode = FCL.CountryCode ) > LEFT OUTER JOIN > ( regions r LEFT JOIN factor_region_lookup FRL ON FRL.regionID = > r.regionid ) > ON FRL.FactorID = #factorid# AND c.CountryCode = r.CountryCode > LEFT OUTER JOIN > ( cities ct LEFT JOIN factor_city_lookup FCTL ON FCTL.cityID = > ct.cityID > ) > ON FCTL.FactorID = #factorid# AND r.RegionID = ct.RegionID > WHERE FCL.FactorID = #factorid# order by c.countrycode > </cfquery> > > For some reason, the following bit of code that I have just added, alters > the resultset, and I get back wrong data and a bunch of NULL rows, I can't > see why as I am not really altering the query, just adding a new column > alias. > > --- new code --- > (c.countrycode + '_' + > CASE > WHEN isnumeric(r.regionID) = 1 THEN cast(r.regionID > AS varchar) > else '0' > END > +'_'+ > CASE > WHEN isnumeric(ct.cityID) = 1 THEN cast(ct.cityIDAS > varchar) > else '0' > END) > > > AS locationstring > --- end --- > Anyone? > > Russ > > > _______________________________________________ > > For details on ALL mailing lists and for joining or leaving lists, go to > http://list.cfdeveloper.co.uk/mailman/listinfo > > -- > CFDeveloper Sponsors:- > >- Hosting provided by www.cfmxhosting.co.uk -< > >- Forum provided by www.fusetalk.com -< > >- DHTML Menus provided by www.APYCOM.com -< > >- Lists hosted by www.Gradwell.com -< > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help > -< > _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
