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
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?