>- 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 -<
