>- 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.cityID AS
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.cityID AS
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 -<
