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

1430
6
Jump to solution
10-08-2021 11:45 AM
MichaelMorgan4
Occasional Contributor

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.

1 Solution

Accepted Solutions
Asrujit_SenGupta
MVP Regular Contributor

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

Bug BUG-000085841

View solution in original post

6 Replies
MatthewDriscoll
MVP Alum

_ Underscore is a wild card in SQL.  

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

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
Occasional Contributor

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
MVP Regular Contributor

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

Bug BUG-000085841

MichaelMorgan4
Occasional Contributor

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
by
Notable Contributor

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.

0 Kudos