Select to view content in your preferred language

SQL commands for calculating a field with python

596
5
Jump to solution
10-11-2023 02:37 AM
Labels (2)
RichardHowe
Occasional Contributor III

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

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
ChrisUnderwood
Esri Contributor
5 Replies
ChrisUnderwood
Esri Contributor
RichardHowe
Occasional Contributor III

@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?

0 Kudos
MobiusSnake
MVP

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?

ChrisUnderwood
Esri Contributor

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

ChrisUnderwood_0-1697021631137.png

 

0 Kudos
RichardHowe
Occasional Contributor III

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

0 Kudos