 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / General / March 2007Recordset.Sort
I have a stored procedure with Order By as: CASE WHEN CAST((Left(dbo.[Partial].PartialNumber, 2)) AS int)<50 then CAST(1 AS varchar)+(dbo.[Partial].PartialNumber) else CAST(0 AS varchar)+(dbo.[Partial].PartialNumber) end Why do I use this? It's because PartialNumber field contains values structured as YYMM-SequentialNumber, for instance 9903-0123 means that it was created in March 1999. 0101-0252 was created in January 2001. 0702-0111 was created in February 2007. When I display those values in the grid I need them to be sorted by PartialNumber related to the date, for instance ASC order should display 9903-0123 first, and 0101-0252 after that and 0702-0111 after that . After recordset is populated by SP I need to give the user the ability to resort by other fields. I have no problem with resorting by any other field by using Sort property of the recordset. But with PartialNumber field when I use RS.Sort = "PartialNumber ASC" it gives: 0101-0252, 0702-0111, 9903-0123 With RS.Sort = "PartialNumber DESC" it gives: 9903-0123, 0702-0111, 0101-0252 Both of them are wrong. I need them to be: 9903-0123, 0101-0252, 0702-0111 and 0702-0111, 0101-0252, 9903-0123 The most obvious solution is rewriting SP. But I was wondering if I could use Sort property of the recordset to achive that result. If it's possible then how should Sort property look? Thank you vovan ¤ I have a stored procedure with Order By as: ¤ CASE WHEN CAST((Left(dbo.[Partial].PartialNumber, 2)) AS int)<50 then ¤ CAST(1 AS varchar)+(dbo.[Partial].PartialNumber) ¤ else ¤ CAST(0 AS varchar)+(dbo.[Partial].PartialNumber) ¤ end ¤ ¤ Why do I use this? It's because PartialNumber field contains values ¤ structured as YYMM-SequentialNumber, for instance 9903-0123 means that it ¤ was created in March 1999. ¤ 0101-0252 was created in January 2001. ¤ 0702-0111 was created in February 2007. ¤ When I display those values in the grid I need them to be sorted by ¤ PartialNumber related to the date, for instance ASC order should display ¤ 9903-0123 first, and 0101-0252 after that and 0702-0111 after that . ¤ After recordset is populated by SP I need to give the user the ability to ¤ resort by other fields. ¤ I have no problem with resorting by any other field by using Sort property ¤ of the recordset. ¤ But with PartialNumber field when I use RS.Sort = "PartialNumber ASC" it ¤ gives: ¤ 0101-0252, 0702-0111, 9903-0123 ¤ With RS.Sort = "PartialNumber DESC" it gives: ¤ 9903-0123, 0702-0111, 0101-0252 ¤ Both of them are wrong. I need them to be: ¤ 9903-0123, 0101-0252, 0702-0111 and 0702-0111, 0101-0252, 9903-0123 ¤ ¤ The most obvious solution is rewriting SP. But I was wondering if I could ¤ use Sort property of the recordset to achive that result. ¤ If it's possible then how should Sort property look? If you want to sort this column properly as a date value then you need to return the column as a date data type instead of a standard numeric integer value. That will likely require separating the date from the sequence number. The alternative is not to use Sort but call a stored procedure which handles the ORDER BY for you. Sort is rather limited in this respect. Paul ~~~~ Microsoft MVP (Visual Basic) Thank you Paul. This field is not Date type field, so I cannot return its value as Date. SP doesn't give any problem, but spead. When the recordset is created initially it takes sometimes up to 10 seconds. Then any change of Sort is done within a portion of a second. So I was looking for the way to sort the recordset, not to repopulate it. vovan > ¤ I have a stored procedure with Order By as: > ¤ CASE WHEN CAST((Left(dbo.[Partial].PartialNumber, 2)) AS int)<50 [quoted text clipped - 45 lines] > ~~~~ > Microsoft MVP (Visual Basic) > This field is not Date type field, so I cannot return its value as Date. > SP doesn't give any problem, but spead. When the recordset is created > initially it takes sometimes up to 10 seconds. Then any change of Sort is > done within a portion of a second. > So I was looking for the way to sort the recordset, not to repopulate it. It's not going to happen. YOU know that 99 < 01, but there is absolutely no way to tell the Recordset that. You'd have to implement your own sorting through code, plain and simple. ¤ Thank you Paul. ¤ This field is not Date type field, so I cannot return its value as Date. ¤ SP doesn't give any problem, but spead. When the recordset is created ¤ initially it takes sometimes up to 10 seconds. Then any change of Sort is ¤ done within a portion of a second. ¤ So I was looking for the way to sort the recordset, not to repopulate it. ¤ Ditto what Jeff said. Sort operates on the data already present in the Recordset, and what you have for that column is not suitable for a proper date sort. Paul ~~~~ Microsoft MVP (Visual Basic) 27.03.2007 17:15, vovan schrieb: > Thank you Paul. > This field is not Date type field, so I cannot return its value as Date. > SP doesn't give any problem, but spead. When the recordset is created > initially it takes sometimes up to 10 seconds. Then any change of Sort is > done within a portion of a second. > So I was looking for the way to sort the recordset, not to repopulate it. If you'd query it as an UNION you'd get rid of the sluggish CAST/CASE WHEN statement. Consider somthing like: Select '19' || dbo.[Partial].PartialNumber As PN From .... WHERE dbo.[Partial].PartialNumber LIKE '9%' UNION Select '20' || dbo.[Partial].PartialNumber As PN From .... WHERE dbo.[Partial].PartialNumber LIKE '0%' ORDER BY PN I have no T-SQL expertise at all so I do not if || is supported for string-concatenation in T-SQL, and if the other syntax-details match, afaik in some RDBMS ORDER BY is only valid as part of the first major SELECT in a UNION, not at the end, so try it out. MfG, Alex CASE WHEN CAST((Left(dbo.[Partial].PartialNumber, 2)) AS int)<50 >> then >> € CAST(1 AS varchar)+(dbo.[Partial].PartialNumber) >> € else >> € CAST(0 AS varchar)+(dbo.[Partial].PartialNumber) >> € end >> € I have a stored procedure with Order By as: >> € CASE WHEN CAST((Left(dbo.[Partial].PartialNumber, 2)) AS int)<50 [quoted text clipped - 5 lines] >> € >> € Why do I use this? It's because PartialNumber field contains values |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.