Adding custom query parameters
In addition to the query parameters provided by application, you can also add custom query parameters to the CUSTOMQUERYPARAMETERS table in the database.
To add a custom parameter:
- Access your Microsoft SQL Server or Oracle database management tool.
- View the CUSTOMQUERYPARAMETERS table.
- Add a row to the table.
- Specify a unique ID value in the CUSTOMQUERYPARAMETERID column.
-
Specify a descriptive but short name in the NAME column. This is the name you
see in the Value Is list when adding or modifying a conditional statement in
Query Builder.
Note: The name can contain letters and number, but do not contain spaces or symbols. Do not include a leading colon.
- Specify the SQL query statement in the PARAMETERVALUE field.
- Specify the SeccodeID SYST00000001 in the SECCODEID column, to make the query parameter available to everyone, or specify the SeccodeID for a single user.
-
Specify the field type this parameter applies to in the DATATYPE column. The
supported data types are: DateTime, String, and Integer.
The query parameter displays in the Value Is list for the specified data type and supported operators.
-
Specify T for True or F for False in the ISSCALAR column.
- T: The query parameter is available for the operators
- = equal to
- <> not equal to
- > greater than
- < less than
- <= less than or equal to
- >= greater than or equal to
- F: The query parameter is only available for the operator In.
- T: The query parameter is available for the operators
-
Sepcify one of these in the DATABASETYPE column:
- MSSQL: Type MSSQL if the Parameter Value is specific to MSSQL.
- Oracle: Type Oracle if the Parameter Value is specific to Oracle.
- Both: Type Both if the Parameter Value is valid for MSSQL and Oracle.
-
Save your changes.
Once saved, the custom parameter is available in the Query Builder, Conditions, Value Is list for the data type specified in steps 8 and 9.