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?
Solved! Go to Solution.
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.
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.
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 ....