Its a stored procedure, and the INTEGER values need to be casted to a string as all values need to have the same data type.
I've found the solution; I am padding the string with zeros. I think it is affecting the execution time drastically though (talking about 500,000 records). Will do some more reseach, any better suggestions would be appreciated. On 4/14/07, Simon Haddon <[EMAIL PROTECTED]> wrote: > > Hi, > > Why do you have to cast at all. This is only in your order by clause. If > you have to do it this way then can you build you SQL dynamically using > coldfusion or whatever you are using to add in the order by rather than > using a case statement? > > On 14/04/07, Taco Fleur <[EMAIL PROTECTED]> wrote: > > > > Hi all, > > > > I am using a dynamic order by statement; > > > > ORDER BY CASE @sort > > WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 > > ) AS CHAR( 5 ) ) > > WHEN 1 THEN C.title > > WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance] ( > > @fromLatitude, @fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) > > ) > > WHEN 3 THEN ( C.locality + ' ' + C.state ) > > WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC > > > > The problem is with the numeric values, I have to cast them as a string > > (to make the dynamic order by work), but in the results 114km > > obviously is not between 1137km and 1144km. > > > > Anyone any ideas on this? > > Thanks in advance. > > -- > > Taco Fleur - http://www.pacificfox.com.au > > Web Design, Web development, Graphic Design and Complete Internet > > Solutions > > an industry leader with commercial IT experience since 1994 > > > > > > > > > -- > Cheers > Simon Haddon > > > -- Taco Fleur - http://www.pacificfox.com.au Web Design, Web development, Graphic Design and Complete Internet Solutions an industry leader with commercial IT experience since 1994 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to [EMAIL PROTECTED] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
