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

FinnianO_Connor

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.