Variables (also referred to as prompts) can be added to a View1 and defined in the Variables tab. Variables prompt the user for input whenever the View is displayed, printed, executed to a file, or when a SEQUEL Report is run using the prompted View.
Variables can be placed anywhere in the SQL statement, but most typically can be found in the WHERE clause for record selection. Note that all variable names must be prefaced with an ampersand '&'.
The example below shows a variable (&state) that will prompt the end-user for a state value. The variable is defined as a QSTRING, one of five prompt types, with a length of 4. This is to allow for quotes. Prompt text is defined which will ask the user for input as well as a default value. (see the end user prompt below)
A step by step example on adding prompts begins on page 148.
variables_new.bmp
Options
Variable Name - The variable reference used in the SQL statement. It must be pre-
ceded by the ampersand (&).
Prompt Types:
QSTRING - Use for character data. Quotes are provided. NUMBER - Use for numeric data.
NAME - Use when prompting for field name, library, file name or SQL
clause.
EXPR - Will allow any SQL expression or fraction thereof, including
blanks, operators and functions.
DATE - Use for date data type values. Automatically defaults to the system
date. Use the syntax “&&variable” (double quotes and ampersands).
Variable Length - Specify a value from 1 to 1085 to indicate the allowed length of
the substitution value. If the variable type is NAME, the maximum length is 256. If the variable type is NUMBER, the maximum length is 29 with a maximum significand of 20. If the variable type is QSTRING, the quotes are included in the length. If you are passing a QSTRING variable up to 5 char- acters long, the length should be 7 to include the quotes.
Prompt Text - Up to 32 characters of text can be entered. The text will appear on the
prompt display. The special values, *NOPROMPT, *NP, and *NOPMT, will suppress the prompt dialog completely and use the default value.
Default Values - Specify a value that will appear on the prompt prior to the user's
entry. The value must conform to the type indicated by the prompt type ele- ment. Specify up to 80 characters for the default value but do not exceed the maximum length indicated by the variable length element. If a default value is not specified, a zero value will be used for NUMBER variables, the cur- rent date will be used for DATE values, and a blank will be used for other variable types.
Keywords can be used to retrieve system values for use as the default value. The keywords include the following:
In addition to keywords, view and sql derived expressions1 can be returned as a default value.
VIEW(lib/viewname) - The value of the first row and column returned by
the view will be used as a default value. For example: VIEW(sequelex/custlist)
SQL(expression) - Use this to return a specific column from a file or a
derived value for use as a default. For example: SQL(current date - 2 days), or SQL(select cname from sequelex/custmast)
Integrity Check -Specify one or more rules that must be satisfied by the value
entered when the view is run. The rule must conform to one of the following
Keyword Usage Length Comment
*JOBNBR Retrieve current job number 6
*JOB Retrieve current job name 10
*USER Retrieve current user name 10
*JOBDATE Retrieve current job date 6 In job date format
*SYSDATE Retrieve current system date 6 In job date format
*SYSTIME Retrieve current system time 6 As HHMMSS
*SYSTEM Retrieve current system name 8
1. To ensure Scripts will run in the green-screen environment, the resulting VIEW and SQL default values must be charac- ter. This is only a requirement for prompted Scripts in the green-screen environment.
DDS equivalent formats. SEQUEL will validate the user's entry according to the specified rule and issue an error if the rule is violated.
The Integrity Test Builder is available to assist in adding these tests to your variable definitions. See page 150.
COMP(rel-op value) - Choose one of six relational operators (EQ, NE, GT,
LT, GE, LE) and specify a value that conforms to the variable type and vari- able length elements. Ex. COMP(GT 0) or COMP(EQ "ABC")
[NOT] VALUES(value,value,value ...) - Specify a list of values separated
by commas, that will constrain the user's entry. Only a value matching one of the items in the list will be accepted when the view is run. If the keyword NOT precedes the VALUES keyword, then only values not included in the list will be accepted.
Ex. VALUES("Y","N") or NOT VALUES(0,1,2,3,4,5)
[NOT] RANGE(low-value high-value) - The user's entry must be between
the low value and high value (inclusive) indicated in the rule. If the keyword NOT precedes the RANGE comparison, then only values outside the range will be accepted when the view is run.
CHECK(len) - Forces the user's value to match the full length of the vari-
able.
CHECK(uc) - Automatically translates lowercase input to uppercase. CHECK(multi) - Use with DBLIST below to add check boxes to a list of
selectable values.
SPCVAL(value,value,value…) - Specify a list of values separated by com-
mas that will bypass any other checking. It is especially useful in the case of passing *ALL to a parameter on a command instead of using *ALL/*OMIT to remove text from the SQL statement as in a view.
SST(*LDA, mmmm, nnnn) - Write the prompted value to the local data
area (LDA) where mmmm is the starting position and nnnn is the length of the substring. Values in the LDA can be retrieved by high level programs, a calculation in SEQUEL Report Writer, or by using the DTAARA function in a view (page 116).
PASSWORD - This integrity test hides the prompted value as it is entered. DBLIST(QSYS-Object[,Field[;Description[,Filter]]]) [,Check(Multi)]-
can retrieve values from a database file or a non-prompted, non-tabling, SEQUEL view.
•The QSYS-Object can be a qualified or unqualified reference to a database file or non-prompted, not tabling SEQUEL view. For database files, specify a member using the format - lib/file(member).
•The Field is optional and, if specified, must match one of the fields in the file or view specified by the first value. If a field name is not specified, the first field or column in the file or view is used.
•The Description is optional and can be used as an alias list for the Field above. For example: show a customer name associated with a customer number.
•The Filter is also optional and is a test that is applied to the rows in the file or view. The syntax of the test is the same as the syntax of an expression in the WHERE clause. I.e. (AMTDU > 15000) If the qsys-object is a file or a SEQUEL view without a where clause, this test is concatenated with "WHERE" to become a where clause. If the object is a SEQUEL view having an existing WHERE clause, this test is concatenated with the existing where clause and "AND ", so that the existing WHERE clause is not lost. If a filter is specified, the field name must also be specified.
•The optional Check(Multi) integrity test will add multiselect check boxes to the drop-down list of values defined by the Field Parameter. The Variable Type must be set to EXPR.
Ex. DBLIST(sequelex/ordhead,cusno;cname,orval>15000), check(multi) You can enter multiple rules for a variable. Separate rules with commas. VALUES, RANGE, and COMP rules are mutually exclusive. That is, only one of these rules can be specified. CHECK(len) and CHECK(uc) can be used in conjunction with any other rule.
CHECK(len) and CHECK(uc) can be combined as CHECK(uc len) or CHECK(len uc).
A VALUES and CHECK can be combined by separating the two tests with a comma such as VALUES("IL","CA","TX"), CHECK(UC).
Comparisons involving NAME and EXPR variables are not case sensitive. The value(s) specified in the rule can be entered in upper, lower, or mixed case. When the view is run, the user's value is compared against the value(s) in the rule in a case independent manner. Values for QSTRING variables are
case sensitive and must be entered by the user in the exact form indicated by the rule.
Omit Leading / Trailing Values - Specify text preceding or following a variable to
remove when using the *OMIT or *ALL keyword.
Extended Help - Enter any ‘help’ text for end users to refer to when using the
prompted view.
The View with a run-time prompt will look as follows:
prompt view.bmp
When the view is run the following screen will display:
prompt_ILx.bmp
More example prompt displays are discussed in the ViewPoint Results chapter start- ing on page 82.