Select by Attributes on Joined File Geodatabase Feature Classes Between Fields of Different Types

[Anonymous User]

I am using ArcMap 10.8.1.  I have two feature classes in a local file geodatabase.  Both have an ID field of type Long, which has been indexed.  Feature class A has a Long field (AFIELD) and feature class B has a Text field (BFIELD).  I have joined A and B on ID.  Now all I want to do is select records where A.AFIELD <> B.BFIELD.  The SELECT statement always yields an error.  I have tried using CAST, CONVERT, and other workarounds, but cannot get the statement to run.

I have assembled a small example in the form of a map document, file geodatabase, and two feature classes.  Each feature class is indexed on ID and a join has been performed from B to A (A is the target, B is the joined).  If executed correctly, ID 10 will not be in the selection set because 123 = '123' but ID 20 will be in the selection set because 456 <> '457'.

[DavidPike]

I don't really want to download a zip file, pictures might be better.

Can you just create a new field and run a field calculator set to python, something like: (untested and probably wrong)

def is_equal(value_str, value_int):
    if int(value_str) == value_int:
        result = 'True'
    else:
        result = 'False'
    return result 

is_equal(!yourStringField!, !yourIntField!) 

[JoshuaBixby · Accepted solution]

I have tried using CAST, CONVERT, and other workarounds, but cannot get the statement to run.

Instead of saying you tried CAST and CONVERT and they didn't work, try sharing specific examples you used with CAST and CONVERT and share the specific error messages.

For starters, CONVERT is not supported syntax with the file geodatabase, so you should not expect that to work.  I use CAST regularly with FGDB, so I know it can work, but I can't comment any more until I see how you are building your statements.

UPDATE:  I did some basic testing, and this appears to be either a defect or limitation of CAST with the file geodatabase, i.e., CAST doesn't work with joined tables.

[DavidPike]

I didn't see any issues with the CAST on either single or joined data ArcMap 10.7.1.

CAST( "Id" AS CHARACTER) <> "id_text"

Edit - actually tested on a shapefile... FGDB FC doesn't seem to work.  Maybe try a shapefile? 😕

[JoshuaBixby]

I just tried with 2 tables in a mobile geodatabase (SQLite) in Pro, and it works fine.  This is just another example of the long-standing poor SQL support with file geodatabases. 

[Anonymous User]

I appreciate all of your responses, and the time you put in to helping me.  I appreciate that @JoshuaBixby  tried to do the CAST in a query on a joined file geodatabase feature classes and also found that it didn't work either.  Validation. 🙂

The operation I was trying to perform was a microcosm of a project I need to do this week.  I was using file geodatabase feature classes to practice the operation I need to perform before the big day.  The actual, production feature classes are on SQL Server, and I have no doubt my query will work there.  If it's just a bug with file geodatabases, then I can live with that.  I did verify that the CAST operator works as expected when querying a feature class that is not involved in a join.  Thanks, everyone!