***the sql-instruction has been modified a lot, so whas was written here is now useless***
Edit
The reason is Float converts to Char better than Varchar because Char is a variable length while Varchar is fixed length and NVarchar is multi bytes. All your quantitative functions are in Float because that is where T-SQL implemented them but you can cast Float to decimal more stable and decimal to Varchar. If you don't have any reason for using int then it should be decimal. I know LOG is not included in your code but if you need to use LOG you have to tell SQL Server LOG(n) or LOG10(n), if not SQL Server will give you algebraic numbers when you are looking for calculus calculations. Run a search for cast,convert and LOG compare in SQL Server BOL(books online) Hope this helps.
|||sorry if im not too quick, but what you mean is that I have to change some of my type for it to work? because I have tried all the combinaison of char, varchar, nvarchar, int and float, and every times it give me the same result (well not exactly the same, since like I said the result always change at page 2...). Maybe I just didnt catch what you mean.
On a side-note, when I try to do a filter on the dist_km field (ie: dist_km > 200), this doesnt work at all...Maybe the answer lie within your post, but im still kinda confused...By the way, thanks a lot for your answer! And if you have any others suggestions, I'll be glad to hear them.
|||ok, I've made many, many tests, and I've come to the conclusion that the problem is not in the orthodromic formula and conversion itself. For my test, I just tried to sort my records on some INT fields, and guess what, at page 2, the same problem occur. Of course for my test, is use something like this in my sql instruction (Select *, dist_km = ad_viewed......ad_viewed being an INT field). So with that in light, does anybody know what is the problem...im sure its not just a conversion problem now... thanks again for your time|||You are using Unicode and Ascii in one code you cannot do that, you also have date as varchar500, why do you need that much for date? I also see Charindex why do you need a string function in a quantitative function stored proc? Look at it you will see what I am talking about try cast instead of convert if it is explicit in SQL Server it will work. Change all your float converts to decimal instead of Nvarchar, change int to Numeric and try the link below and download the first file there is a converter, it is the exe. If you are still having problems send me an email I have detailed Descriptive statistics files including linear regression, it may make your code mile long but it may help you. Hope this helps
http://www.novicksoftware.com/transact-sql-user-defined-functions.htm
ok, maybe the nvarchar for the date parameter was kinda high (even though it was 50 not 500), But I really need the charindex part. This is not a quantitative stored procedure only, the orthodromic part is only a small portion of the big picture. And I did try cast, but the effect was exactly the same. As you can see in my previous post (well, previous depending when the admin will posts it), I figured out that the problem was not the orthodromic formula itself.
But all these info really help me, since it will surely improve my stored procedure logic. So thanks a lot again for all your dedication. But before I made all these modifications to my sotored procedure, I need to know why it doesnt sort my record correctly...
|||Yes I understand you now try TOP10 to 20 play with it. Test drive this expensive tool it was created by the best minds in T-SQL. Hope this helps
http://shop.bmc.com/product_moreinfo.cfm?id=1E61087
|||Try this link , his paging is different. Hope this helps.
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=633&lngWId=5
|||well thanks for the link. Well, as I can see theres two paging methods on this page. The first one is too simple to satisfy my need (I now used a method that looks like this one, but have some difference). And as for the second one, its a method using subqueries. Now this was the one I was using when I write this topic for the first time. And it ended up that it was the real problem, because alias column CANT be used for sorting in the inner most subquery, but this is where I need it to sort. So this one is perfect, but only if you have a table without any alias colum to sort on. I found that out on an other topic I've made, and now im using another method of custom paging that works fine now. But thanks a lot for the feedback, and for all the previous help too!!!
No comments:
Post a Comment