The SQL statement that defines the source of a query layer is static. However, there are some situations in which parts of SQL statements are not known in advance. For example, when you display aggregated rainfall, you may not know if your map reader will want to aggregate rainfall station values by day, week, or month. Parameters in query layers make components of the SQL statement dynamic. You can think of a query parameter as an SQL statement variable for which the value is defined when the query is run. The following are the two types of query parameters:
- Range parameters connect numeric or temporal values dynamically to the range and time sliders. For example, consider an SQL query that includes a range parameter WHERE ::r:myRangeVar1. The SQL query that is sent to the database is WHERE aField = value or WHERE aField => value1 AND aField <= value2, depending on whether one value or two values is passed by the range slider.
- Discrete parameters update a query with literal values when the query is run. For example, consider an SQL query that defines a query layer and includes two discrete parameters WHERE city_population > ::population AND state_name = ::name. When values such as 10000 and California are specified for each parameter, respectively, the SQL query that is sent to the database is WHERE city_population > 10000 AND state_name = 'California'.
Note:
A query layer is defined by an SQL query that is specified when you create a query layer for data in a relational database or create a query layer for data in a cloud data warehouse. You can also create a query layer using the Make Query Layer geoprocessing tool, but you cannot define parameters using this tool. To use parameters in the SQL statement, alter the query layer on the Edit Query dialog box.
If you need a query layer that allows you to browse aggregated values by date, time, or numeric ranges, use the Make Aggregation Query Layer geoprocessing tool to build the query layer with dynamic aggregation enabled and define query parameters to display your results.
Define range parameters
Range parameters work with either numeric (integer or double) or date-time values to display data dynamically on the time or range slider. They are declared without a reference to a field or expression, and no logical operators are used in the SQL query. Instead, the field or expression is set as a property of the parameter. Range parameters are prefixed by the keyword ::r:. You use range parameters when the SQL query WHERE clause relies on fields that are not part of the SELECT list.
An example is: WHERE ::r:MyRangeParameter
When declaring a range parameter, you must specify a field or expression, the data type, and, optionally, default values and an alias to be used in the sliders. Click Default value to enter the minimum and maximum default values. When this box is checked, you must enter at least one value to validate the expression. The default values can be value literals (an actual number or date) or an expression that returns a value of the specified data type.
The following steps explain how to define a range parameter on a table named earthquakes to display quakes that occurred within the last seven days as a default time period and, optionally, use the time slider to change this time period:
- On the Map tab in the Layer group, open the Add Data menu and click Query Layer.
- On the Connection menu, choose a database connection, or create a connection using New Geodatabase Connection
. - In the Name field, type Earthquakes.
- In the Query box, type select * from EARTHQUAKES where ::r:EQDateRange.
- Hover over the ::r:EQDateRange parameter in the code and click Edit Query
to edit the properties. - Provide the following parameter properties:
- For Field or Expression, enter the name of a date-time field.
- For Data Type, choose Date.
- Check Default value.
- Provide default value expressions according to the underlying database.
Some examples are shown in the table below for a few databases. See Database and client configurations for query layers for the full list of databases that can be configured to use query layers.
Database From Value To Value Microsoft SQL Server
dateadd(dd, -7, cast(getdate() as date)) cast(getdate() as date) Oracle
trunc(CURRENT_DATE) - INTERVAL '7' DAY trunc(CURRENT_DATE) PostgreSQL
CURRENT_DATE - INTERVAL '7 days' CURRENT_DATE - Click Done.
- Click Validate to ensure that the SQL query and range definitions are valid.
- Click Next.
- Specify Unique Identifier Fields, Geometry Type, and Spatial Reference, and click Finish.
Now you can use the time slider to navigate to any time period and view the earthquakes that occurred. You can click the Disable Time button 
to view the earthquakes within the default time range established, which is within the last seven days in this example.
Caution:
You can declare more than one range parameter on a query layer, but only one of those range parameters can reference date-time values.
Advanced properties for range parameters
If validation of the query fails, you may need to specify some advanced properties for the range parameters. Expand the Advanced section of the parameter properties to specify the name of the table that the field belongs to. This helps retrieve the minimum and maximum values directly from the table. This improves efficiency, especially if the parameter is within a nested query. Also under the Advanced heading, if no default values have been provided, you must specify the expression to use when the value is missing for the parameter. Choose either 1=1 (the default), which will resolve the parameter to 'TRUE' in the expression, or 1=0, which will resolve the parameter to 'FALSE'.
Discrete parameters
Discrete parameters work with numeric (integer or double), date, or string values and are prefixed by the keyword ::. They are paired with a field or expression using SQL operators. You can update the field value to filter the data that is drawn on the map.
When declaring a discrete parameter, you must specify a data type and default values. To supply a list of choices in the layer at run time, use the SQL IN operator and check Allow multiple values. Optionally, specify an alias to be used in place of the parameter name. The following are examples:
- WHERE aFieldName = ::var1
- WHERE AdminField IN ::AdminAgency
Predefined discrete parameters
In addition to declaring your own discrete parameters, there are two predefined discrete parameters you can use in your queries. The view_extent parameter specifies a spatial filter for your query and can significantly improve performance when you work with very large datasets. The view_scale parameter allows you to access the current map scale within the query. These variables are only considered when you are in the context of a 2D map. They will be ignored in a 3D scene.
view_extent
When a layer is viewed on a map, the query that is sent to the database has a spatial filter appended at the end of the SQL query. This is sufficient, but you may want to limit the aggregation to only the features visible in the current extent. In these cases, you can add the view_extent parameter to the query to spatially limit it to the current extent.
You can add ::view_extent anywhere in your query, as many times as necessary. Unlike custom discrete parameters, there are no properties to set, and the default value of the parameter is NULL.
The SQL query that is sent to the database replaces ::view_extent with a polygon in well-known text (WKT) format in the same spatial reference as the layer. Use a database-specific function and a spatial reference ID (SRID) to create geometry from the WKT extent polygon. The following examples show this for SQL Server and PostgreSQL database types:
- In SQL Server: WHERE shape.STWithin(geometry::STGeomFromText(::view_extent, 4326)) = 1
- In PostgreSQL: WHERE ST_Within(shape, ST_GeomFromText(::view_extent, 4326))
view_scale
Use the view_scale predefined parameter when you want to vary the where clause or the selected fields based on the current map scale. The default value of zero is passed in when you use this parameter. There are no other properties to set. You can specify this parameter as many times as necessary in your SQL query. The following examples use the view_scale parameter in SQL Server and PostgreSQL database types:
- In SQL Server: WHERE IIF(::view_scale > 100000, <do this>, <do that>)
- In PostgreSQL: WHERE (CASE WHEN ::view_scale >= 100000 THEN <do this> WHEN ::view_scale >= 50000 AND ::view_scale < 100000 THEN <do that> ELSE <do the other thing> END)
Add parameters to a query layer
To add a parameter to the query, complete the following steps:
- Right-click the layer in the Contents pane and click Properties
. - On the Source tab, in the Query row of the table, click Edit Query .
The query is expressed in the Query text box.
Tip:
To make the query text easier to read, you can enlarge it by pressing the Ctrl key while rotating the mouse wheel button forward.
- Position the pointer in the code and type ::r:<name> to enter a range parameter or ::<name> to enter a discrete parameter.
Parameters appear in the SQL query in bold and include an Edit button
next to them. - Click the Edit button to access properties of the parameter.
- Set the parameter properties and click Done.
You must define Default Value and Data Type. Other properties are optional.
- On the Edit Query dialog box, click Validate to ensure there are no errors.
- Continue setting the spatial properties for the layer and click Finish when you're done.