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'.

SQLCompareLongToTextInJoin.zip

KatieSmith_4-1712853379142.png

JoshuaBixby

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.