dbMap/Web - SQL Runtime Parameters
SQL queries are created and maintained in Petrosys Pro. Report and data selection queries can be used in dbMapWeb. This help topic explains the runtime parameters within those queries.
SQL queries in dbMapWeb can include 'runtime parameters' which result in the user being presented with an interactive dialogue when the query is used. This allows site specific interactive query dialogues to be developed.
Converting a single text parameter from being fixed to interactive is as simple as replacing it with a prompt in tildes. A select clause in a query looking for wells with logged depths greater than 2000, for example, might be
WHERE LOGGED_DEPTH > 2000
and this can be converted to presenting the user with a prompt be making it
WHERE LOGGED_DEPTH > ~Enter minimum logged depth for wells to be selected~
Note that the syntax for runtime parameters is specific to Petrosys. Queries incorporating these will not run in other SQL processing environments.
The construction of more complex forms of dialogue is described here.
SQL Syntax for Runtime Parameters
A runtime parameter is defined by including the parameter definition in tildes (~).
In the absence of anything more specific, the text within the tildes is used as a prompt, and the dialogue presented treats the required input as plain text.
To provide further control between one and three additional keywords can be placed in front of the prompt. Each keyword is identified by a colon (:) at the end.
The extended syntax for a runtime parameter is:
~[SEP:][OPER:][type_keyword:]prompt_text~
where each of the items in square brackets [] is optional, i.e. there may be one or no instances of any or each of them.
The SEP: Keyword
The optional 'SEP:' keyword is used to tell dbMapWeb to insert a horizontal separator line within the runtime parameter panel before the required parameter is displayed. The use of these separators helps in grouping parameters on the panel into logical areas.
The OPER: Keyword
The optional 'OPER:' keyword tells dbMapWeb to include an interactive selection of comparison operations, such as 'like' or 'greater than', at this point.
The comparison selector is available for text, number and date data types. If the 'OPER:' keyword is used on any other data type, it is ignored.
For the text data type, the user is presented with the following options for a comparison operator when the query is run:
=
Not equal to
Is NULL
Is not NULL
Between
Contains
Pattern match
Does not contain
Begins with
Does not begin with
Ends with
Does not end with
Prompts for the text data type are also automatically given a 'match case' check box for case-sensitive or case-insensitive string matching.
For a number or date data type, the user is given the following options for a comparison operator:
=
Not equal to
Is NULL
Is not NULL
<
<=
>
>=
Between
Note: When using the 'OPER:' keyword, do not include any SQL operators before the runtime parameter as this would double up operators. For example the following is NOT correct:
...WHERE POLY_GROUP_NAME = '~OPER:Polygon group name~'
whereas the following are correct:
...WHERE POLY_GROUP_NAME ~OPER:Polygon group name~
...WHERE POLY_GROUP_NAME = '~Polygon group name~'
Type Keywords
The text data type has no type keyword, so any parameters without a type keyword will be treated as a text data typed parameter. Type keywords like NUMBER, DATE, COLOR, SYMBOL etc. can be used to define many non-text data types including selection lists. They result in appropriate dialogue elements with input validation being presented to the user.
Some of the type keywords require 'arguments' which are parameters that modify their behaviour. Arguments are lists of information in brackets, for example
NUMBER( 0.0, 20.0 ):
defines a floating point numeric type that is limited to values between 0.0 and 20.0.
Type Keywords Without Arguments
DATE: |
A text box is used and the response is validated as a date. Numeric operators are allowed. |
COLOR: |
A colour-picker widget is used. |
COLOUR: |
A colour-picker widget is used. |
SYMBOL: |
A plot-symbol widget is used. |
NUMBER: |
With no arguments, a text box is used and the response is checked for a valid number. |
For example to define an 'Expiry date' date prompt with an operator, use:
~OPER:DATE:Expiry date~
To define a plot-symbol picker, use:
~SYMBOL:Plot symbol~
Type keywords with arguments
LOOKUP("lookup_query"): |
The user can only select a value from the named lookup_query - See 'Lookup Queries' section below. |
MULTI_LOOKUP("lookup_query"): |
The user can select multiple values from the named lookup_query - See 'Lookup Queries' section below. There is a limit of 254 items a user can select, but a MULTI_LOOKUP can also be used to select items not matching by: WHERE ... NOT IN (~MULTI_LOOKUP("A query"):Items not matching~) |
OPER:MULTI_LOOKUP("lookup_query", field_name): |
The user can select multiple values from named lookup_query or disable it to accept all values – See 'Lookup Queries' section below. Selection of 'IN' vs 'NOT IN' is also available on the runtime. Note, that syntax is slightly different for MULTI_LOOKUP with the OPER keyword. For example: WHERE ~OPER:MULTI_LOOKUP("A query", ...):Items~ |
LIST("'item_1','item_2',..."): |
The user is presented with a drop-down box to select an item from the given list. |
LINE(attribute_name): |
A line-style widget is used to get the response from the user. The parameter is then replaced with the given attribute of the line style. The attribute_name argument can be one of the following: *COLOR / COLOUR |
FILL(attribute_name): |
A fill-style widget is used to get the response from the user. The parameter is then replaced with the given attribute of the fill style. The attribute_name argument can be one of the following: *COLOR / COLOUR |
NUMBER(min_value,max_value):(optional) |
A text box is used and the response is checked for a valid number. The number is also checked to see if it is within the min/max bounds given (inclusive). If a decimal point is used in either the min_value or max_value arguments, dbMapWeb will allow the use of floating-point values, otherwise the field will be limited to integers. |
A report form with required parameter in dbMapWeb
Lookup Queries
For the LOOKUP and MULTI_LOOKUP data-types to function, queries must be specified to lookup from. These queries are administered from the Mapping/Admin/Reports/Edit... panel under the new Data type 'Lookups'.
A lookup query must contain (at least) two columns in order to function in both LOOKUP and MULTI_LOOKUP types. The first column contains the value replacing the runtime parameter in the user-defined query. The other columns will be displayed in the lookup window. Columns can be made "invisible" by adding the HIDDEN prefix to their names. The first visible column from the selected result is displayed on the lookup button of LOOKUP types. MULTI_LOOKUP types display the number of items selected on the lookup button.
The following query is valid for both LOOKUP and MULTI_LOOKUP types and will place ASSOC_ID values in the calling query.
SELECT ASSOC_ID HIDDEN,
ASSOC_NAME "Name"
FROM BUSINESS_ASSOC
ORDER BY ASSOC_NAME
If the above query were named "Business associates", for example, a data-selection query that uses this lookup as a runtime parameter might look like this:
SELECT DISTINCT TTL_UID OBJECT_UID
FROM PS_TTL_TITLE_VERSION A,
PS_TTL_JV B,
PS_TTL_JV_VERSION C,
PS_TTL_JV_MEMBERS D
WHERE B.JV_UID=A.JV_UID
AND C.JV_UID=B.JV_UID
AND D.JV_VSN=C.JV_VSN
AND D.BUS_ASSOC IN
(~MULTI_LOOKUP("Business associates"):Business associate(s)~)
When MULTI_LOOKUP is used without the OPER keyword the MULTI_LOOKUP should always be placed within an IN () or NOT IN() clause as MULTI_LOOKUP data-types return a comma-delimited list of values. If the OPER keyword is used, both the field name and the IN/NOT IN clause should be omitted and the query would look like this:
SELECT DISTINCT TTL_UID OBJECT_UID
FROM PS_TTL_TITLE_VERSION A,
PS_TTL_JV B,
PS_TTL_JV_VERSION C,
PS_TTL_JV_MEMBERS D
WHERE B.JV_UID=A.JV_UID
AND ~OPER:MULTI_LOOKUP("Business associates",D.BUS_ASSOC):Business associate(s)~