The order by value doesn't get returned, only the values defined in the select list do. As far as I know the data type of your dynamic order by shouldn't matter.
Blair On 4/14/07, Taco Fleur <[EMAIL PROTECTED]> wrote: > > 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 > > > > 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 cfaussie@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---
