It does matter unfortunately. Anyways, I seperated them in different CASE statements for each type. all sorted, thanks..
On 4/16/07, Blair McKenzie <[EMAIL PROTECTED]> wrote: > > 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 > > > > > > > > > > -- 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 -~----------~----~----~----~------~----~------~--~---
