topic Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters. in Data Management Questions

Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

FrankLahm_III — Thu, 10 Nov 2011 20:38:15 GMT

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

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

LanceShipman — Thu, 10 Nov 2011 21:07:58 GMT

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"?

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

FrankLahm_III — Thu, 10 Nov 2011 21:36:01 GMT

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

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

LanceShipman — Mon, 14 Nov 2011 19:13:12 GMT

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.

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

FrankLahm_III — Tue, 15 Nov 2011 20:05:11 GMT

Thanks Lance!

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

SystemmanagementGrün_Stadt_Zür — Tue, 07 Feb 2012 08:19:06 GMT

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

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

FelixSvoboda — Wed, 29 Feb 2012 12:24:50 GMT

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

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

MegSouthee — Fri, 20 Apr 2012 18:42:03 GMT

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

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

RobWillson — Tue, 28 Aug 2012 16:38:48 GMT

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

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

LanceShipman — Tue, 11 Sep 2012 17:27:47 GMT

It's fixed in 10.1.

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

KevinMayall — Mon, 13 May 2013 16:47:06 GMT

It is still not fixed in Desktop 10.1. I have a field with length 297 and it gives this error when sorting by the column heading in ArcMap. The other (shorter) fields sort fine.

Re: Problem Sorting Geodatabase Table on Fields Longer than 255 caracters.

LanceShipman — Mon, 13 May 2013 18:26:46 GMT

I just tested this with 10.1 and it's working for me. I added a 300 width field and calculated values into it and it sorts without an error. Can you post a copy of your data?