Division symbol not working in CreateDatabaseView SQL

251
2
Jump to solution
11-03-2021 09:36 PM
DonMorrison1
Occasional Contributor III

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.

DonMorrison1_1-1636000576401.png

 

 

ArcGIS Pro 2.8.3

SQL Server DB

 

0 Kudos
1 Solution
2 Replies
DonMorrison1
Occasional Contributor III

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. 

0 Kudos