Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 10 of 11 total |
Problem with Sort Order |
Tue, Mar 30 2010 6:36 PM | Permanent Link |
Scott Rowat | Hi Tim,
I have the following query: SELECT LoanNo FROM Loans ORDER BY LoanNo The query returns the following result: LoanNo ----------- BOV -13 BOV -17 BOV 103 The problem is that the result is not in the proper order based on ANSI collation. If you do a AnsiCompareStr or WideCompareStr on these value, you will find that BOV 103 is considered less than both BOV -17 and BOV -13. Because the query is not properly ordered, it cause the report builder that we use to not work properly. I assume that the sort order is (or should be) based on ANSI collation and not ASCII collation. Please correct me if I'm wrong. Is this an issue or am I doing something wrong or made a bad assumption on the sort order collation. I am using DBISAM 4.28 Build 6. Any assistance you can provide is greatly appreciated. Thanks, Scott |
Wed, Mar 31 2010 1:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Scott,
<< The problem is that the result is not in the proper order based on ANSI collation. If you do a AnsiCompareStr or WideCompareStr on these value, you will find that BOV 103 is considered less than both BOV -17 and BOV -13. Because the query is not properly ordered, it cause the report builder that we use to not work properly. >> The ANSI Standard locale in DBISAM is not the same thing as the English (US) locale. The ANSI Standard locale is a binary comparison based upon the Windows ANSI character set (Windows 1252). If you want the sorting to be done the same as in Windows, then you need to use the English (US) locale with the LOCALE or LOCALE CODE clauses: SELECT LoanNo FROM Loans ORDER BY LoanNo LOCALE CODE 1033 http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=d&version=7&topic=SELECT_Statement -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Mar 31 2010 2:44 PM | Permanent Link |
Scott Rowat | Hi Tim,
I ran the following two queries against the Loans table and though a few Loans in the result set where ordered different both queries returned the same ordering for: BOV -13 BOV -17 BOV 102 BOV 103 SELECT LoanNo FROM Loans ORDER BY LoanNo SELECT LoanNo FROM Loans ORDER BY LoanNo LOCALE CODE 1033 Setting the Locale Code to 1033 did not seem to change the ordering of those Loans but WideCompareStr and AnsiCompareStr say that BOV 103 and BOV 102 are less than BOV -13 and BOV -17. Is LOCALE CODE 1033 the same as ANSI in Windows? Thanks, Scott "Tim Young [Elevate Software]" wrote: Scott, << The problem is that the result is not in the proper order based on ANSI collation. If you do a AnsiCompareStr or WideCompareStr on these value, you will find that BOV 103 is considered less than both BOV -17 and BOV -13. Because the query is not properly ordered, it cause the report builder that we use to not work properly. >> The ANSI Standard locale in DBISAM is not the same thing as the English (US) locale. The ANSI Standard locale is a binary comparison based upon the Windows ANSI character set (Windows 1252). If you want the sorting to be done the same as in Windows, then you need to use the English (US) locale with the LOCALE or LOCALE CODE clauses: SELECT LoanNo FROM Loans ORDER BY LoanNo LOCALE CODE 1033 http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=d&version=7&topic=SELECT_Statement -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 1 2010 12:48 AM | Permanent Link |
Abdon | Scott,
I think its the minus symbol. It comes right before the numeric symbols. so -17 should come before 103. BOV -13 BOV -17 BOV 103 |
Thu, Apr 1 2010 7:29 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Scott,
<< Setting the Locale Code to 1033 did not seem to change the ordering of those Loans but WideCompareStr and AnsiCompareStr say that BOV 103 and BOV 102 are less than BOV -13 and BOV -17. >> We use the SORT_STRINGSORT attribute for the CompareStringA call that we use when you use a non-ANSI Standard locale like English (US). That may cause differing results from the AnsiCompareStr call. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 1 2010 10:51 AM | Permanent Link |
Scott Rowat | Hi Tim,
Just to be clear. DBISAM doesn't have a true ANSI locale that would base the collation on ANSI using an ANSI comparision but rather the ANSI Standard locale uses a binary comparision. And the English (US) locale is a non-ANSI locale. Based on this there is no way for me to use ANSI collation in the report builder but rather will need to switch to a Binary collation in the report builder and then it should work properly since the ANSI Standard locale uses a binary comparison for sorting. Does ElevateDB have a true ANSI collation or is it similiar to DBISAM? Thanks, Scott |
Thu, Apr 1 2010 10:58 AM | Permanent Link |
Scott Rowat | Hi Abdon,
When comparing strings using ANSI comparision methods (WideCompareStr and AnsiCompareStr), BOV 103 is less than BOV -13 and BOV -17 and therefore is displayed first in the list. Depending on the comparison method used will depend on where BOV 103 is located. Using CompareStr, which is a non-ANSI compare, BOV 103 is greater than BOV -13 and BOV -17. We've been setting the collation in our reporting engine to ANSI assuming that ANSI Standard in DBISAM was the same thing, but have been running into a few wierd situations in some reports. As Tim has pointed out the ANSI Standard locale in DBISAM does not use an ANSI comparison but rather a Binary comparison method. So, hopefully switching the reporting engine to use a Binary collation will resolve our issue. Thanks, Scott Abdon Ibarra wrote: Scott, I think its the minus symbol. It comes right before the numeric symbols. so -17 should come before 103. BOV -13 BOV -17 BOV 103 |
Thu, Apr 1 2010 2:44 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Scott,
<< Just to be clear. DBISAM doesn't have a true ANSI locale that would base the collation on ANSI using an ANSI comparision but rather the ANSI Standard locale uses a binary comparision. And the English (US) locale is a non-ANSI locale. >> No, DBISAM uses the *Windows API* CompareStringA call on any non-ANSI Standard locales, so you are gettting an ANSI comparison based upon the Windows 1252 (ANSI) character set. The difference is due to the SORT_STRINGSORT flag being used, which affects how punctuation characters such as '-' are sorted. http://msdn.microsoft.com/en-us/library/ms647476(VS.85).aspx -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 1 2010 2:45 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Scott,
<< When comparing strings using ANSI comparision methods (WideCompareStr and AnsiCompareStr), BOV 103 is less than BOV -13 and BOV -17 and therefore is displayed first in the list. Depending on the comparison method used will depend on where BOV 103 is located. Using CompareStr, which is a non-ANSI compare, >> You're confusing CompareStr, a native Delphi string comparison routine, with CompareStringA, which is a Windows API call and is what DBISAM uses. See my other reply for more information. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 1 2010 3:11 PM | Permanent Link |
Scott Rowat | Hi Tim,
Thanks for all the good information (and yes, I'm probably a bit confused... and confusing). Just to (re) confirm, the ANSI Standard locale in DBISAM uses a binary comparison, correct? I'm just trying to get ReportBuilder to work properly with DBISAM. In ReportBuilder when ANSI collation is selected it uses WideCompareStr or AnsiCompareStr for comparison and setting DBISAM to ANSI Standard and ReportBuilder to ANSI collation does not seem to work properly in a few instances. So, if the ANSI Standard locale in DBISAM uses a binary comparison, then I'm thinking that selecting Binary collation versus ANSI collation in ReportBuilder will fix the problem if I leave the locale of the DBISAM tables at ANSI Standard. Scott "Tim Young [Elevate Software]" wrote: Scott, << When comparing strings using ANSI comparision methods (WideCompareStr and AnsiCompareStr), BOV 103 is less than BOV -13 and BOV -17 and therefore is displayed first in the list. Depending on the comparison method used will depend on where BOV 103 is located. Using CompareStr, which is a non-ANSI compare, >> You're confusing CompareStr, a native Delphi string comparison routine, with CompareStringA, which is a Windows API call and is what DBISAM uses. See my other reply for more information. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |