Loss of division when creating a database view in a file geodatabase

[MichaelMorgan4]

When creating a view in a file geodatabase, using the Create Database View tool, in the view definition, any / character I use for division disappears when I run the process. This of course throws a generic "invalid SQL statement" error (160195). Leaving out the division problem works fine (in other words, running a query in a file gdb with summing and grouping are just fine).

The view definition would be:

select NEW_COMMDIST AS New_Comm_District, sum (pop2020) AS Pop_per_Comm_District, CAST (sum pop2020 AS float) / 32250.0 AS Deviation FROM pro2 GROUP BY new_commdist

Literally after running this, the query becomes:

select NEW_COMMDIST AS New_Comm_District, sum (pop2020) AS Pop_per_Comm_District, CAST (sum pop2020 AS float) 32250.0 AS Deviation FROM pro2 GROUP BY new_commdist (note the missing /).

This is in Arc Pro 2.8.3. I tried a CAST function because I thought maybe the / operator might require 2 floats. The problem exists with 2 integers also.

Are there any fancy character workarounds I can try? or is there a DIV () function maybe?

Thanks all.

[MatthewDriscoll]

_ Underscore is a wild card in SQL.  

[Asrujit_SenGupta]

The Esri WebHelp highlights this limitation: Create Database View (Data Management)

Views created in a geodatabase using this tool do not support a forward slash (/) character within the view definition. For example, if you have a column in your create view definition that is based on an expression such as Population1990 / 10, then you would need to create the view using SQL tools.

---

I don't see a workaround to this for File Geodatabases.

[MichaelMorgan4]

This seems like an unnecessary bug then. Division would seem like almost a routine part of view definitions for just this purpose, any time any sort of proportion or normalization is needed. Why ESRI would choose to disable a common arithmetic operator in view definitions is ... a little weird. I am also choosing to use a file geodatabase for this purpose because of the temporary nature of this operation. Hopefully I can push this as a bug report.

[Asrujit_SenGupta · Accepted solution]

There is already a Bug Logged for this, which was submitted in 2015:

Bug BUG-000085841

[MichaelMorgan4]

Thank you for this. Hopefully this can be fixed after 6 years. There are workarounds, but apparently not for file geodatabases, and workarounds are not functionality.

Which actually makes me wonder if there is a way to access a file geodatabase using a mySQL, Postgre, or even OGR interface ....

[Bud]

That's pretty crazy that you can't divide numbers in FGDB database views.

I checked in ArcGIS Pro 3.0.3 and it's still an issue there.

[KarimBaklouti]

And still there in ArcGIS Pro 3.2.2 

Crazy 😞