The SQL statement that defines the source of a query layer is static. However, there are some situations where 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 query parameters 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, an SQL query 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, an SQL query that defines a query layer 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'.
A query layer is defined by an SQL query that is specified when you create a query layer. You can create a query layer using the Make Query Layer 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.
Range parameters work with either numeric (integer or double) or date-time values to display data dynamically on the time or range sliders. 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.
Example: 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.
Here is an example showing 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.
- In the Connection menu, choose a database connection, or create a new 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 to edit the properties.
- Enter the following parameter properties:
- For Field or Expression, enter the name of a date-time field.
- For Data Type, choose Date.
- Check Default value.
- Enter default value expressions, according to the underlying database.
- SQL Server: dateadd(dd, -7, cast(getdate() as date)) to CURRENT_TIMESTAMP.
- Oracle: DateAdd (Date( ), "1", -7) to CURRENT_TIMESTAMP.
- PostgreSQL: CURRENT_DATE - INTERVAL '7 days' to CURRENT_TIMESTAMP.
- Click Done.
- Click Validate to ensure that the SQL query and range definitions are valid.
- Click Next.
- Specify the 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.
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 would resolve the parameter to 'TRUE' in the expression, or 1=0, which would resolve the parameter to 'FALSE'.
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.
- WHERE aFieldName = ::var1
- WHERE AdminField IN ::AdminAgency
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.
To make the query text easier to read, you can enlarge it by pressing the Ctrl key while spinning the mouse wheel forward.
- Position the cursor 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 icon next to them.
- Click the icon to access properties of the parameter.
- Set the parameter properties and click Done.
You must define a 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.