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