>- see footer for list info -< Nevermind, I found the problem. I was actually building a custom query later in the code using QuerySetCell() and I was inserting into the wrong bloody query.
DUH! -----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 -- 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 -- 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 -<
