[cfaussie] Re: [OT] Dynamic order by
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 -~--~~~~--~~--~--~---
[cfaussie] Re: [OT] Dynamic order by
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[cfaussie] [OT] Dynamic order by
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
