Order by is not effective. please provide solution

Question

Order by is not effective. please provide solution

28.kanikasoni

SSC-Addicted

Points: 476
More actions
November 2, 2012 at 6:01 am

#266508

IF ISNUMERIC(SUBSTRING(@FilterString,1,1))=1
BEGIN
select top 3 1 InternalID, CityName = (ci.CityZipCodes + ',' + cit.CityName + ',' + prt.ProvinceName + ',' + cot.CountryName), NULL ParentInternalID, GeoLocation = (cast(ci.CityLatitude as varchar(20))+ ',' + cast(ci.CityLongitude as varchar(20)))
from GeoData.TB_CityTranslation cit
inner join GeoData.TB_City ci on ci.CityInternalID = cit.CityInternalID
inner join GeoData.TB_ProvinceTranslation prt on prt.ProvinceInternalID = ci.CityProvinceID
inner join GeoData.TB_Country co on co.CountryInternalID = ci.CityCountryID
inner join GeoData.TB_CountryTranslation cot on cot.CountryInternalID = co.CountryInternalID
inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalID
where cit.CityTranslationStatusID = 1 and ccm.ContractCountryMappingStatusID = 1 and co.CountryStatusID = 1
and cit.LanguageInternalID = cot.LanguageInternalID and cit.LanguageInternalID = prt.LanguageInternalID
and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID))) and cit.LanguageInternalID = ltrim(rtrim(str(@LanguageInternalID)))
and ci.CityZipCodes like + SUBSTRING(@FilterString,1,3) +'%' order by str(isnull(@SortFieldIndex,2)) + @OrderBy
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 112965 More actions · Answer 1

How have you definied that it is the order by which is causing the problem?

Please follow the links in my signature on posting performance problems and data and code for the best help so that we can assist better.

BriPan SSCrazy Points: 2061 More actions · Answer 2

is there any error

if yes what's the error

looking to your query

if you are ordering from index and then apped @orderby which is varchar then error will come

like order by 2+ @orderby (this will give error)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

what is inside of

@SortFieldIndex and @OrderBy ?

Looks like you are mixing dynamic and static sql together, it's not going to work...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Materalise the ORDER BY expression in the output to see what's happening:

select top 300 -- widen the range to observe the effect of the ORDER BY

OrderBy = str(isnull(@SortFieldIndex,2)) + @OrderBy,

InternalID = 1,

CityName = (ci.CityZipCodes + ',' + cit.CityName + ',' + prt.ProvinceName + ',' + cot.CountryName),

ParentInternalID = NULL,

GeoLocation = (cast(ci.CityLatitude as varchar(20))+ ',' + cast(ci.CityLongitude as varchar(20)))

from GeoData.TB_CityTranslation cit

inner join GeoData.TB_City ci on ci.CityInternalID = cit.CityInternalID

inner join GeoData.TB_ProvinceTranslation prt on prt.ProvinceInternalID = ci.CityProvinceID

inner join GeoData.TB_Country co on co.CountryInternalID = ci.CityCountryID

inner join GeoData.TB_CountryTranslation cot on cot.CountryInternalID = co.CountryInternalID

inner join Config.TB_ContractCountryMapping ccm on ccm.ContractCountryMappingCountryInternalID = co.CountryInternalID

where cit.CityTranslationStatusID = 1

and ccm.ContractCountryMappingStatusID = 1

and co.CountryStatusID = 1

and cit.LanguageInternalID = cot.LanguageInternalID

and cit.LanguageInternalID = prt.LanguageInternalID

and ccm.ContractCountryMappingContractInternalID = ltrim(rtrim(str(@ContractID)))

and cit.LanguageInternalID = ltrim(rtrim(str(@LanguageInternalID)))

and ci.CityZipCodes like + SUBSTRING(@FilterString,1,3) +'%'

order by str(isnull(@SortFieldIndex,2)) + @OrderBy

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden