idea CASE Statements in Definition Query in ArcGIS Pro Ideas

CASE Statements in Definition Query

MatthewGeorge — Fri, 18 Nov 2022 22:22:04 GMT

I'd like to see support for CASE statements in a Definition Query, particularly where the data is in an Enterprise Geodatabase (SDE) with a database engine that does support this sort of query.

Re: CASE Statements in Definition Query

Bud — Sun, 20 Nov 2022 00:03:30 GMT

Can you give us a hypothetical example?

When I’ve used CASE in Oracle SQL, it’s usually in the SELECT clause in a full-blown query, not in the WHERE clause expression (a definition query essentially being a WHERE clause).

Re: CASE Statements in Definition Query

MatthewGeorge — Sat, 19 Nov 2022 23:01:26 GMT

@Bud

Example as follows:

SomeField = CASE
    WHEN MONTH(GETDATE()) >= 8 THEN CONCAT(YEAR(GETDATE()), "-", YEAR(GETDATE())+1)
    ELSE CONCAT(YEAR(GETDATE())-1, "-", YEAR(GETDATE()))
END

Re: CASE Statements in Definition Query

Bud — Sun, 20 Nov 2022 01:31:51 GMT

I don't think I understand how you'd use that in a definition query. It doesn't have a predicate, does it? For example: WHERE A = B, or something like that.
Would putting your logic into a subquery help?
What database are you using?
Doesn't the definition query use native SQL, meaning, it would support any SQL functionality that the database supports?
I'd be interesting in knowing what the full use case is.

Unrelated, but the notes in this idea might interest you: Training course on SQL in ArcGIS Pro

Re: CASE Statements in Definition Query

MatthewGeorge — Sat, 19 Nov 2022 23:39:30 GMT

@Bud

We have a dataset with a field that defines the season of operations which is basically the years that a season spans concatenated plus a suffix, i.e. 2022-2023_<suffix>. A season starts in August, hence the month check in my example. That exact query works from SSMS (obviously this means we are using SQL Server) when I query the versioned view or business table, for example:

SELECT Field1, Field2, Field3

FROM SomeTable

WHERE <see previous example>

It does have a predicate because the case statement returns the season code which is then checked against SomeField.

Re: CASE Statements in Definition Query

Bud — Sun, 20 Nov 2022 00:15:52 GMT

Interesting. I think I get it now. I hadn't seen CASE used that way before.

I wonder why that doesn't currently work in ArcGIS Pro definition queries. If it's native SQL Server SQL, you'd think it would work. I don't see why ArcGIS Pro would treat it any differently than [WHERE] 1 = 1, or some other typical predicate.

Re: CASE Statements in Definition Query

MatthewGeorge — Sat, 19 Nov 2022 23:45:31 GMT

@Bud

I should add that the idea is to dynamically return the current season rather than having to explicitly set the current season as it changes.

Re: CASE Statements in Definition Query

Bud — Sun, 20 Nov 2022 01:33:25 GMT

I wonder if you could post a question on Stack Overflow or DBA Stack Exchange, with details like this:

You are using software that has a limitation where using the CASE in the predicate directly in the WHERE clause expression doesn't work.
So, is there a way to do what you want, using CASE in the predicate, but not directly in the WHERE clause? For example, could you wrap it in a subquery?

A long shot:
I wonder, is it actually a bug in ArcGIS Pro -- specifically a bug with how it handles definition queries on versioned data? Does it work on an unversioned table?

I ask because I had a similar issue:
Using Oracle SDE.ST_GEOMETRY functions in a defection query or attribute table doesn't work -- due to a bug with versioning in ArcGIS Pro. But if I wrap it in a subquery, then it works fine. Or if the data is unversioned, it works fine too.

Use SDE.ST_GEOMETRY functions in Select by Attributes window
Find multi-part features using SQL (ST_GEOMETRY)
Esri Canada Case: 03080875
Bug Report: BUG-000150273

If you submit a case to support, and it turns out to be a bug, I suspect a bug would get fixed before an idea would get implemented,.

Re: CASE Statements in Definition Query

MatthewGeorge — Sun, 20 Nov 2022 00:23:01 GMT

@Bud

Thanks for that, I hadn't thought to test out wrapping it in a sub query. Will give that a go when I'm back in the office.

I haven't tested it on nonversioned tables either so will give that a go as well to confirm if it's a bug or not.

Re: CASE Statements in Definition Query

Bud — Sun, 20 Nov 2022 00:54:33 GMT

For what it's worth, your original syntax seems to work fine on Oracle tables in ArcGIS Pro, both on versioned and unversioned tables:

FIELD1 = (case WHEN 1=1 THEN FIELD1 end)

The brackets were just for legibility. They're not a subquery. It works without the brackets too:

FIELD1 = case WHEN 1=1 THEN FIELD1 end

Oracle 18c; 10.7.1 EGDB; ArcGIS Pro 2.6.8

Re: CASE Statements in Definition Query

Bud — Sun, 20 Nov 2022 01:04:23 GMT

Re: CASE Statements in Definition Query

Bud — Sun, 20 Nov 2022 01:13:00 GMT

For our notes, here's an example of your syntax working in SQL Server (the test has nothing to do with ArcGIS Pro):

https://dbfiddle.uk/shBxoaUj

Re: CASE Statements in Definition Query

Bud — Tue, 29 Nov 2022 02:14:19 GMT

How'd it turn out?

Re: CASE Statements in Definition Query

MatthewGeorge — Tue, 29 Nov 2022 11:05:32 GMT

@Bud

Sub query didn't work but I just realised I didn't test it against non-versioned data so will give that a try tomorrow.