Using SQL CAST functionality in against a File Geodatabase

[DominickCisson]

There are a number of references in ESRI's documentation  (http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=SQL_reference) and other threads (http://forums.esri.com/Thread.asp?c=159&f=1707&t=280410) that seem to indicate that one can use the SQL "CAST" function in a where statement against a file geodatabase to convert field types on the fly.  I'm trying to pull records based on a set of integers that begin with a particular number.  To do so, I should cast the integer field to a character, and then perform a LIKE statement against the field.  By all rights, and per documentation, the following statement should work against a File GDB: CAST("Price" AS CHARACTER) LIKE '1%'   But... it does not.  It doesn't work in ArcMap 10, and it doesn't work in 9.3  What am I doing wrong here?

[LanceShipman]

Use:
CAST("Price" AS CHARACTER(12)) LIKE '1%'

I need to add this to the doc. The width of the character field must be set. It's consistent with other SQL implementations, but it's not obvious.

[DominickCisson]

Use:
CAST("Price" AS CHARACTER(12)) LIKE '1%'

I need to add this to the doc. The width of the character field must be set. It's consistent with other SQL implementations, but it's not obvious.

Hallelujah!  I tried every combination I could think of, including "varchar" with a width, but I never put a width on "character".  It's always fun navigating the vagaries of SQL across SQL Server, File GDB's and Personal GDB's

Thanks

[DougBrowning]

What about for DOUBLE or INT?  I can never get cast to work for numbers and had given up till I saw this post.

CAST(RockFragments AS DOUBLE)  is a no go

[DougBrowning]

Ok finally got it.  You need to use NUMERIC and give it a size.  You also need the field name in "".

So this worked

CAST("RockFragments" AS NUMERIC(38,9)) <> (CAST("ESD_FragVolPct" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct2" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct3" AS NUMERIC(38,9)))

This help says you can use double but it never works.  Crazy how bad this help page is.

SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop 

Edit: 

This worked great in a ArcMap Def Query but bombed in a search cursor in python with invalid SQL statement.

whereClause = 'CAST("RockFragments" AS NUMERIC(38,9)) <> (CAST("ESD_FragVolPct" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct2" AS NUMERIC(38,9)) + CAST("ESD_FragVolPct3" AS NUMERIC(38,9)))'

It is hitting a in_memory table so that may be it.  I have found that in_memory sets do not support all the SQL that geoDBs do.  Which is not documented or anything.  This may be what stopped me from cast in the past.  Bummer.

Any ideas?

thanks

[DougBrowning]

Yep found my own old post about it. I was hoping it was the numeric issue.  

https://community.esri.com/thread/218020-cast-does-not-work-in-searchcursor-queries 

[JoshuaBixby]

Doug Browning‌, I am confused by this last comment.  Are you working with in-memory geodatabases again and CAST isn't working?  Or, are you working with file geodatabases and CAST isn't working?

[DougBrowning]

It works in a GDB but does not work when hitting a in_memory set in Python.  Says invalid SQL statement.

thanks

[JoshuaBixby]

Ah, OK, now I get what you are saying.  Yes, in-memory workspaces in ArcMap do not support all the SQL statements that file geodatabases support.  I have not experimented yet with the new "memory" workspace in Pro, but I suspect it will also have even more limited SQL support.

UPDATE:  Doug Browning‌, good news, I just checked Pro and the new "memory" workspace works with SQL CAST while the older "in_memory" workspace does not.

[DougBrowning]

Ok I have a new one today that I cannot find any info on.

Using a GDB.

I need to a Definition Query for tab \t.  

This  (also tried CHAR)

SpeciesWoody LIKE '%' || CHR(9) || '%'

or this should work but it is not

SpeciesWoody LIKE '%' + CHR(9) + '%'

Also tried looking for a \ using

'%\\%'

Anyone have any ideas?

thanks