topic Re: Publishing Error with Definition query in ArcGIS Pro Questions

Publishing Error with Definition query

kapalczynski — Tue, 19 Nov 2024 20:12:39 GMT

I am getting an error when trying to publish am Oracle SDE Feature Class to AGOL. It works in CASE 1 below but when I add the date it fails to publish..

NOTE: It does not throw an error when I add the definition query. In fact the definition query works fine as you can see from the image below.. there are over 700 records in this dataset and it is only showing 1.

QUESTION: Why does it fail to publish with the date but does not throw an error in Pro

CASE 1

( PERMITSTATUS LIKE 'COMPLETED' OR PERMITSTATUS LIKE 'CANCELED' OR PERMITSTATUS LIKE 'DENIED' OR PERMITSTATUS LIKE 'Maintenance')

CASE 2

(( PERMITSTATUS LIKE 'COMPLETED' OR PERMITSTATUS LIKE 'CANCELED' OR PERMITSTATUS LIKE 'DENIED' OR PERMITSTATUS LIKE 'Maintenance') AND PERMITCHANGEDATE >= ADD_MONTHS(TO_DATE(SYSDATE),-10))

Re: Publishing Error with Definition query

ChristopherCounsell — Tue, 19 Nov 2024 20:18:13 GMT

Some oracle SQL may not function correctly in ArcGIS pro, even if it succeeds at a database level

Try using standard SQL, e.g.

(PERMITSTATUS IN ('COMPLETED', 'CANCELED', 'DENIED', 'Maintenance'))

AND PERMITCHANGEDATE >= SYSDATE - INTERVAL '10' MONTH

Similar discussion here

https://community.esri.com/t5/arcgis-pro-questions/definition-query-with-dates/td-p/1074642

Re: Publishing Error with Definition query

kapalczynski — Tue, 19 Nov 2024 20:38:42 GMT

hmmm failed again...

Re: Publishing Error with Definition query

ChristopherCounsell — Tue, 19 Nov 2024 20:40:15 GMT

Can you build it with the inbuilt ArcGIS pro query builder (not writing the SQL direct)?

Re: Publishing Error with Definition query

kapalczynski — Tue, 19 Nov 2024 20:57:03 GMT

The below worked when I built it... looking at the SQL it created,,, but this does not look correct

But not sure how to use NOW or SYSDATE and then minus 10 months

PERMITSTATUS = 'COMPLETED' Or (PERMITSTATUS = 'CANCELED' And PERMITCHANGEDATE < TO_DATE('2023-05-01 15:52:58', 'YYYY-MM-DD HH24:MI:SS'))

Re: Publishing Error with Definition query

kapalczynski — Tue, 19 Nov 2024 21:02:40 GMT

This seemed to work... trying to publish now

(PERMITSTATUS = 'COMPLETED' Or PERMITSTATUS = 'CANCELED' And PERMITCHANGEDATE < TO_DATE(SYSDATE - INTERVAL '10' MONTH, 'YYYY-MM-DD HH24:MI:SS'))

UPDATE: no go on the publish... error again

Re: Publishing Error with Definition query

kapalczynski — Tue, 19 Nov 2024 21:51:45 GMT

Think I got it with this...

(( PERMITSTATUS LIKE 'COMPLETED' OR PERMITSTATUS LIKE 'CANCELED'

OR PERMITSTATUS LIKE 'DENIED' OR PERMITSTATUS LIKE 'Maintenance')

AND PERMITCHANGEDATE >(CURRENT_DATE - 300))

Re: Publishing Error with Definition query

kapalczynski — Wed, 20 Nov 2024 17:34:07 GMT

@ChristopherCounsell --- OK the above definition query worked in Pro. It worked and gave NO errors on publishing... but when I go to AGOL and go to the data tab it says error and does not show data...

I created another service and REMOVED the AND PERMITCHANGEDATE >(CURRENT_DATE - 300) and everything worked...

I then created another with ONLY the date and it works great.

PERMITCHANGEDATE <= (CURRENT_DATE - 90)

BUT when I combine the 2 it breaks -- so it seems both work on their own but when I combine them it breaks

((PERMITSTATUS LIKE 'COMPLETED' OR PERMITSTATUS LIKE 'CANCELED' OR PERMITSTATUS LIKE 'DENIED' OR PERMITSTATUS LIKE 'Maintenance') AND PERMITCHANGEDATE <= (CURRENT_DATE - 90))

So it appears AGOL is still having issues with the combination of LIKE and DATE...

If published individually they work great.
Errors are NOT thrown if they are combined and published to hosted Feature Layer, Although AGOL errors when you try and look at the data.

Re: Publishing Error with Definition query

ChristopherCounsell — Wed, 20 Nov 2024 21:55:36 GMT

Given that you're publishing to ArcGIS online as a new layer

Can you export to a feature class first, then publish that layer (without the definition query)?

Re: Publishing Error with Definition query

kapalczynski — Wed, 20 Nov 2024 23:16:25 GMT

The thing is I have a script that reads the aprx file that has the FC in it and then uses that to overwrite the AGOL Feature Layer. And it runs multiple times a day...

The question is how does a software get to this point and something as simple as this does not work...typical esri... 3 steps to do one

I have to check the script again and maybe just run the original publishing with all the data and then modify the Python script to query the data and create a temp FC and use that to do the overwriting..

so have a bit of work ahead...