Query Layer with View_Scale Parameter using Oracle Data?

[FinnianO_Connor]

Using the blog, 'Scale dependent multiple datasources for a single feature layer', I am trying to create a query layer using the view_scale parameter to switch between different tables based on map scale. The data I am querying is stored in an Oracle database and the sql I am using for the query layer is:

SELECT * FROM (
SELECT 1 as t_id, OBJECTID, Geometry FROM Local
UNION ALL
SELECT 2 as t_id, OBJECTID, Geometry FROM Regional) x
WHERE x.t_id = CASE WHEN ::view_scale > 10000 THEN 2 ELSE 1 END

The query validates with no errors, but after selecting 'finish' in the New Query Layer dialog no layer is added to the map in ArcGIS Pro. If I remove the WHERE clause, the data loads as expected, however both feature classes load at all scales which is not desired.

Wondering have I an issue with the syntax of the query or are there other potential causes to the issue?

Cheers,

Finnian

[George_Thompson]

Should the "x" after "FROM REGIONAL" be inside the ")" and not outside?

[FinnianO_Connor]

Thanks for the reply George. If i more the 'x' inside that bracket I get a validation error with the 'where' clause, which I guess makes sense as the alias 'x' is now referring to the Regional table as opposed to the entire inner select query

[George_Thompson]

Can you try it with our the alias and use the table name for "x"?

I cannot remember if ArcGIS query layers work (or like) the alias's at all.

[FinnianO_Connor · Accepted solution]

Got it working - not by altering the query, but by creating the query in two steps. First I created the query layer without the 'where' clause. Then I went in to the query layer properties and edited the query by adding the 'where' clause. The data then displays as expected, with the Local table displaying at scales below 10000 and Regional data above scales of 10000. Not sure why the query wouldn't build in a single step though.

[MichaelVolz]

Finnian:

Is your Oracle database registered with SDE?  Or is the Oracle database just spatially enabled with SDE libraries that you needed to install onto the database?

[FinnianO_Connor]

In this particular case I'm working directly with the Oracle data (sdo geometry) from ArcGIS Pro so no connection is being made through Enterprise/Server etc