topic Re: Are Def Query using SQL now different? in ArcGIS Pro Questions

Are Def Query using SQL now different?

DougBrowning — Mon, 17 Jun 2019 19:55:06 GMT

I am trying a simple query to look for accidental lower case errors. For example Pf098788 should be PF098788.

I made a simple query UPPER(UnknownCode) <> UnknownCode and I get no results. If I try UPPER(UnknownCode) = UnknownCode I get everything.

Layer us a AGOL Hosted feature service.

What am I missing here?

Thanks

Re: Are Def Query using SQL now different?

JoshuaBixby — Mon, 17 Jun 2019 20:06:34 GMT

You have tried this against AGOL hosted feature services in the past and it worked?

Re: Are Def Query using SQL now different?

DougBrowning — Mon, 17 Jun 2019 20:10:23 GMT

Nope never tried this exact query. Used others just fine though.

Re: Are Def Query using SQL now different?

JoshuaBixby — Mon, 17 Jun 2019 20:43:43 GMT

Not to ask the obvious, but you know there are accidental lowercase records?

Re: Are Def Query using SQL now different?

DougBrowning — Mon, 17 Jun 2019 21:05:36 GMT

Yea I have been manually changing them all day. My guess is UPPER is not working right since if I use = it gives me AF and Af back. <> I get 0 records.

Re: Are Def Query using SQL now different?

JoshuaBixby — Mon, 17 Jun 2019 22:14:29 GMT

I just did some testing and see what you mean. I don't think the issue is UPPER because if I do something like UPPER(field) = 'XYZ', I get 'xyz' and 'XYZ' returned. I think the issue has something to do with referencing the field on the right-hand size of the SQL statement.

Re: Are Def Query using SQL now different?

MarisaClaggett — Mon, 17 Jun 2019 22:32:51 GMT

Hi Doug,

Are you doing this calculation via ArcGIS Pro or in AGOL? In AGOL, the calculation for SQL is simply UPPER(<field name>).

I used the following example SQL to calculated the hosted feature layer field in Pro as well.

UPPER(NOTES)

If using Select by attributes, I can certainly see the issue.

Best of luck,

Marisa

Re: Are Def Query using SQL now different?

DougBrowning — Mon, 17 Jun 2019 22:47:37 GMT

Not a field calc - this is a Def Query.

I did notice it keeps defaulting to SQL for field calc, which is weird. Is there a setting to make this default to Python.

Re: Are Def Query using SQL now different?

ThaddeusBara — Tue, 18 Jun 2019 12:32:05 GMT

By definition, SQL Server is case insensiitve. Is that perhaps what you are missing?

Re: Are Def Query using SQL now different?

DougBrowning2 — Tue, 18 Jun 2019 13:41:28 GMT

If you Def Query a SQL server then yes. If you Def Query a GDB then no it is case sensitive. The backend of AGOL is neither i think. I could see that it may act that way though. If I try to use COLLATE Latin1_General_CS_AS it says invalid SQL.