topic Division symbol not working in CreateDatabaseView SQL in ArcGIS Pro Questions

Division symbol not working in CreateDatabaseView SQL

DonMorrison1 — Thu, 04 Nov 2021 04:36:56 GMT

I'm trying to create a database view using the CreateDatabaseView tool. The SQL statement includes a forward slash as a division symbol and it appears to be causing an error. For simplicity let's say "SELECT 5/2 as x". The SQL statement is valid and when I change from division to any other math operator it works. I've tried every trick I can think of to get it accepted but no luck. Any idea?

The error from ArcGIS Pro is: ERROR 999999: Something unexpected caused the tool to fail. Contact Esri Technical Support (http://esriurl.com/support) to Report a Bug, and refer to the error help for potential solutions or workarounds

From Python it is a little more explicit: [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '2'.]

It appears that the tool is stripping out the division symbol.

ArcGIS Pro 2.8.3

SQL Server DB

Re: Division symbol not working in CreateDatabaseView SQL

DanPatterson — Thu, 04 Nov 2021 09:02:21 GMT

from the answer here

Solved: Loss of division when creating a database view in ... - Esri Community

the link

Create Database View (Data Management)—ArcGIS Pro | Documentation

Re: Division symbol not working in CreateDatabaseView SQL

DonMorrison1 — Thu, 04 Nov 2021 12:25:01 GMT

Thanks Dan - indeed there it is right in front of my eyes the whole time. It's tough to understand the reason for this limitation. It sure messes up my automation. I wrote a SQL Server scalar user defined function (eg sde.dons_div(dividend, divisor)) that my views can call when division is needed. That works OK but I've been reading that scalar UDFs can lead to very poor performance on queries of large tables.