CASE Statements in Definition Query

[MatthewGeorge]

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.

[Bud]

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

[MatthewGeorge]

@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

[Bud]

1. 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.
2. Would putting your logic into a subquery help?
3. What database are you using?
4. Doesn't the definition query use native SQL, meaning, it would support any SQL functionality that the database supports? 
5. 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

[MatthewGeorge]

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

[Bud]

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.

[MatthewGeorge]

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

[Bud]

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

1. You are using software that has a limitation where using the CASE in the predicate directly in the WHERE clause expression doesn't work.
2. 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,.

[MatthewGeorge]

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

[Bud]

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

[Bud]

Related post: Definition Query SQL shouldn't autocomplete WHEN to WHENEVER