topic Re: Loss of division when creating a database view in a file geodatabase in ArcGIS Pro Questions

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

MichaelMorgan4 — Fri, 08 Oct 2021 21:07:28 GMT

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.

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

MatthewDriscoll — Fri, 08 Oct 2021 20:58:04 GMT

_ Underscore is a wild card in SQL.

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

Asrujit_SenGupta — Sat, 09 Oct 2021 05:11:22 GMT

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.

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

MichaelMorgan4 — Mon, 11 Oct 2021 17:08:48 GMT

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.

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

Asrujit_SenGupta — Tue, 12 Oct 2021 04:30:49 GMT

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

Bug BUG-000085841

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

MichaelMorgan4 — Tue, 12 Oct 2021 15:33:29 GMT

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

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

Bud — Mon, 05 Dec 2022 17:09:58 GMT

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.

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

KarimBaklouti — Fri, 28 Jun 2024 14:53:01 GMT

And still there in ArcGIS Pro 3.2.2

Crazy 😞