Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

[FrankLahm_III]

Hello,
Has anyone notice a problem sorting a geodatabase table that is longer than 255 characters?
We have a rather long field, much larger than 255 characters, which we occasionally use to sort a file geodatabase table. 

When I try in sort this field in both catalog and ArcMap, I get an error screen stating:
"An invalid SQL statement was used. [SELECT REMARKS, OBJECT FROM HI ORDER BY SUBSTRING(REMARKS, 1, 255) ASC]" 

I attached a screen shot of the error.

I have added other fields at lengths greater than 255 and I receive the same error on any field longer than 255.  We did not see this error until moving our process to Arc10.

Thank You,
-frank

[LanceShipman]

File Geodatabase did not support ORDER BY until 10.0. Prior to 10.0 the SQL would parse, but no sorting would occur. "SELECT REMARKS, OBJECT FROM HI ORDER BY SUBSTRING(REMARKS, 1, 255) ASC" will not work as SUBSTRING is not supported in the ORDER BY. The ORDER BY should work on fields greater that 255. Have you tried "SELECT REMARKS, OBJECT FROM HI ORDER BY REMARKS  ASC"?

[FrankLahm_III]

Lance,
Thanks for the response. We are actually seeing this error when we right click on the column and select sort ascending or sort descending.  I must admit I know little about the backend SQL and we are not really doing anything outside of simple queries.  Could you expand a bit more on you answer?
Thank you,
-f

[LanceShipman]

Prior to 10.0 ITableSort was used when sorting file geodatabase tables. At 10.0 we added SQL ORDER BY to the file geodatabase SQL and sort in the table grid was modified to use ORDER BY rather than ITableSort as this performs better with RDBMS data sources. This works fine with file geodatabase, but apparently slightly different SQL is used when the field is a text field and exceeds 255 characters. This is logged as NIM063655  and was fixed in 10.0 sp3.

[FrankLahm_III]

Thanks Lance!

[SystemmanagementGrün_Stadt_Zür]

Hi

I installed Service Pack 3 and still have the same sorting problem (I didn't found NIM063655 in the bug fix list). Did I miss something? Is there a workaround for this sorting issue?

thanks
felix

[FelixSvoboda]

I was told by ESRI support, that they weren't able to fix this bug in SP3, SP4, ... But in ArcMap 10.1 there it should be fixed :mad:

felix

[MegSouthee]

Any word on whether this be implemented in SP5 for Arc 10?

[RobWillson]

This problem was not fixed with SP5 for Arc 10 (i.e., an error about an invalid SQL Statement is still generated when you try to sort on a field with >255 characters). Given that one of the reasons I converted many of my shapefiles to geodatabase format was to take advantage of text fields that could store >255 characters I am reluctant to reduce the field sizes back to 255 characters.

any chance this will be fixed in Arc 10 with the next service pack?

Regards,
Rob

[LanceShipman]

It's fixed in 10.1.