Publishing Error with Definition query

[kapalczynski]

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))

[ChristopherCounsell]

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

[kapalczynski]

hmmm failed again... 

[ChristopherCounsell]

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

[kapalczynski]

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'))

[kapalczynski]

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

[kapalczynski]

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))

[kapalczynski]

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

[ChristopherCounsell]

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

[kapalczynski]

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...