topic Re: SQL commands for calculating a field with python in ArcGIS Online Questions

SQL commands for calculating a field with python

RichardHowe — Wed, 11 Oct 2023 09:37:27 GMT

I'm currently trying to write a python notebook to live in AGOL that will calculate a field based on other fields.

For whatever reason, the expression has to be written in SQL

calc_expression=[{"field": "Fieldname", "sqlExpression" : ""}]

Is there a list anywhere of which SQL commands will work? From experimenting I can see that CONCAT works fine, but I have a need to add leading zeros to a number and the RIGHT, LPAD and FORMAT commands all throw errors

Re: SQL commands for calculating a field with python

ChrisUnderwood — Wed, 11 Oct 2023 09:57:33 GMT

Hello @RichardHowe , does something like this help ?

https://doc.arcgis.com/en/arcgis-online/reference/sql-agol.htm

Re: SQL commands for calculating a field with python

RichardHowe — Wed, 11 Oct 2023 10:19:01 GMT

@ChrisUnderwood Very useful Thank you! 🙂

Seems I'm not going to be able to pad with leading zeros then, unless you have any clever tricks?

Re: SQL commands for calculating a field with python

MobiusSnake — Wed, 11 Oct 2023 10:43:41 GMT

Why not do a query() to get all the records you need to edit, edit the features using regular Python functions, then use edit_features() to post the edits?

Re: SQL commands for calculating a field with python

ChrisUnderwood — Wed, 11 Oct 2023 10:55:16 GMT

@RichardHowe what data Type is the Field you want to pad, and what is a typical item of data that is to be padded ?

Re: SQL commands for calculating a field with python

RichardHowe — Wed, 11 Oct 2023 11:05:01 GMT

I'm writing a script to generate an ID that will run once offline data is sync'd back to the hosted feature layer. I'm using ObjectID as the initial numeric to concatenate with a custom string prefix.

I seem to have got around it by querying for length of ObjectID and padding accordingly

Thanks for your help