Reporting Server > Stored Procedures > Procedure Run-Time Processing Essentials

Procedure Run-Time Processing Essentials

Topics:

Commands in a Stored Procedure
Dialogue Manager Commands and Processing
Using Variables
Supplying Values for Variables
Branching
Calling Another Procedure
Creating Expressions
ON TABLE HOLD
ON TABLE PCHOLD

A stored procedure is a file of commands that resides on a server and is also known as a FOCEXEC procedure. The procedure typically includes any combination of Dialogue Manager control statements, Dialogue Manager variables, SET commands, SQL syntax, TABLE syntax and other syntax that perform tasks such as report generation or file maintenance, or it simply generates messages.

This section is intended as a basic reference for Dialogue Manager and how it integrates to some basic TABLE tasks. complete information about Dialogue Manager, see the Stored Procedures Reference manual and Managing Flow of Control in an Application in the WebFOCUS Developing Reporting Applications manual.

Commands in a Stored Procedure

A procedure must reside on disk in an APP location, which may be an APP directory or an APP MAPped directory (or PDS on z/OS PDS deployment). Additionally, a procedure may reside elsewhere and be "sent" to the server by a client application such as the WebFOCUS mid-tier.

With the EXEC (or EX) command, a stored procedure may be called by another stored procedure or by a client application. This is illustrated below.

In addition to Dialogue Manager commands that control execution flow, and messages (such as the -TYPE above), the following may be included in a procedure:

SQL and TABLE statements allowed by the server platform.
Server commands, for example, CALLPGM, EXEC, and END. For details on CALLPGM and EXEC when used to call a compiled 3GL program, see the Stored Procedures Reference manual. This chapter discusses the use of EXEC to call other FOCEXEC procedures.
Commands allowed in a server profile, such as SET. For details on the profile and its allowable commands, see Server Introduction.
Commands that enable portions of a procedure to be executed on a remote alternate server. See the Stored Procedures Reference manual for details on the syntax and use of those commands. Also see the Server Administration manual for commands that connect to a target server, such as SQL EDA SET SERVER.
The ON TABLE HOLD command, which holds an answer set in a temporary file on a server. See ON TABLE HOLD for details on syntax and use.
The ON TABLE PCHOLD command, which sends an answer set to a client application. See ON TABLE PCHOLD for details on the syntax and use.
Platform-specific commands such as file allocation (that is, DYNAM on z/OS or FILEDEF on other platforms).

Dialogue Manager Commands and Processing

Topics:

Dialogue Manager Command Processing in a Procedure

The following table summarizes the available Dialogue Manager commands. Note that every command begins with a hyphen (-), and this is what differentiates Dialogue Manager syntax from other syntax within a procedure.

The following sections describe the syntax and use of the commands. For an alphabetical list, see the Stored Procedures Reference manual.

Command	Function
-*[`text`]	Comments within a procedure.
-?	Displays the value of local variables.
-UNIX `cmd` -WINNT `cmd` -DOS `cmd` -VMS `cmd` -AS/400 `cmd` -TSO RUN `cmd` -SYSTEM `cmd`	Executes an operating system command for the respective operating system, and the command is ignored when encountered on a non-applicable operating system, except -SYSTEM which runs on all platforms. Note that some platforms use a prior version of the platform name (that is, -AS/400 is for IBM i and -WINNT is for Windows). There are also non dash versions of these commands (and ! is the equivalent of -SYSTEM), but they are not ignored when run on a platform for which they are not relevant.
-CLOSE	Closes an external file opened for reading or writing (an external file is a sequential file in the platform's file system).
-DEFAULT -DEFAULTS	Sets a variable to an initial value.
-EXIT	Executes stacked commands and terminates the procedure. For a definition of stacked commands, see Commands in a Stored Procedure.
-GOTO	Forces an unconditional branch to a label.
-IF	Determines the execution flow based on the evaluation of an expression (a conditional branch).
-INCLUDE	Calls another Dialogue Manager FOCEXEC procedure.
-label	Identifies a section of code that is the target of a -GOTO or -IF.
-PASS	Directly issues and controls passwords.
-PROMPT	Types a message to the terminal (if edastart -t is in use) or creates an input window with the message in a browser if the connection type is HTTP and reads the reply from the user. This reply assigns a value to the variable named.
-QUIT	Terminates the procedure without executing stacked commands.
-READ	Reads data from an external file.
-REMOTE BEGIN	Signals the start of commands on an originating server that are to be sent to a target server. Only available with Hub Services.
-REMOTE END	Signals the end of commands from an originating server.
-REPEAT	Executes a loop.
-RUN	Executes stacked commands and closes any external files opened with -READ or -WRITE.
-SET	Sets a variable to a literal value or to a value computed in an expression.
-TYPE	Sends a message to a client application.
-WRITE	Writes data to an external file.
-	Line continuation of a prior Dialogue Manager command.

Dialogue Manager Command Processing in a Procedure

A procedure processes as follows:

Dialogue Manager reads each line of the procedure, one by one. Values are substituted for variables encountered in any line.
All Dialogue Manager commands (commands that start with a "-") execute as they are encountered.
Other commands are temporarily stored (stacked) for subsequent execution and are referred to as stacked commands.
The Dialogue Manager commands -RUN and -EXIT force the execution of any stacked commands.

Example: Issuing an EXEC (or EX) Call

The following is an example of executing a procedure (SLRPT), with an explanation of the way it processes.

To execute this procedure, the requesting application issues an EXEC (or EX) with the procedure name and supplies values for the required parameters. For example:

EX SLRPT COUNTRY=ENGLAND,CAR=JAGUAR

The following procedure has been enumerated into sections, and its behavior explained (the numbers on the left are not part of the actual procedure, but are simply supplied as reference points for the explanations):

               
1. -IF &COUNTRY EQ 'DONE' THEN GOTO GETOUT;
  
2.  SQL
    SELECT COUNTRY,CAR,MODEL,BODY
    FROM CAR
    WHERE COUNTRY='&COUNTRY' AND CAR='&CAR'
    ORDER BY CAR;
  
3.  TABLE
    ON TABLE PCHOLD
    END
  
4.  -RUN
  
5.  -EXIT
 
    -GETOUT
    -TYPE NO PROCESSING DONE: EXITING SP

The procedure processes as follows:

Values for the variables &COUNTRY and &CAR are passed to the procedure before the first line executes. Dialogue Manager substitutes the value ENGLAND for the variable &COUNTRY in the first line and tests for the value DONE. The test fails, so Dialogue Manager proceeds to the next line.
If the value were DONE instead of ENGLAND, control would pass to the label -GETOUT, and the message NO PROCESSING DONE: EXITING SP would be sent to the client application. (Dialogue Manager would skip the intervening lines of code.)
The next five lines are SQL. Dialogue Manager scans each for the presence of variables, substituting the value ENGLAND for &COUNTRY and the value JAGUAR for &CAR (remember, those values were passed by EDARPC). As each line is processed, it is placed on a stack to be executed later by the server.
The command ON TABLE PCHOLD sends the answer set to the client application.
The command END delimits ON TABLE PCHOLD.

After Dialogue Manager processes the command END, the stacked commands look like this:
```
SQL
SELECT COUNTRY,CAR,MODEL,BODY
FROM CAR
WHERE COUNTRY='ENGLAND' AND CAR='JAGUAR'
ORDER BY CAR;
TABLE
ON TABLE PCHOLD
END
```
The next line is then processed by Dialogue Manager.
The Dialogue Manager command -RUN forces the stacked commands to execute.
The Dialogue Manager command -EXIT terminates the procedure.

Using Variables

Topics:

Naming Conventions
Local Variables
Global Variables
Predefined System Variables

This section describes how to use variables in a procedure.

Variables fall into two categories:

Local and global variables, whose values must be supplied by the procedure at run time.
System and statistical variables, whose values are automatically supplied by the system when referenced.

The following features apply to all variables:

A variable stores numbers or a string of text, and is placed anywhere in a procedure.
A variable refers to a command, a database field, a verb, or a phrase.
The maximum number of variables allowed in a procedure is 1,024. Because approximately 30 are reserved for server use, the maximum number of user-named variables allowed in a procedure is 994.

Naming Conventions

How to:

Use Variables in a Procedure

This section describes how to use variables in a procedure.

Variables fall into two categories:

Local and global variables, whose values must be supplied by the procedure at run time.
System and statistical variables, whose values are automatically supplied by the system when referenced.

Local and global variable names are user-defined, while system and statistical variables have predefined names.

The following rules apply to the naming of local and global variables:

A local variable name is always preceded by an ampersand (&).
A global variable name is always preceded by a double ampersand (&&).
Embedded blanks are not permitted.
If an anticipated value for a variable might contain an embedded blank, enclose the variable in single quotation marks when you refer to it.
A variable name may be any combination of the characters A through Z, 0 through 9, and the underscore (_). The first character of the name must be A through Z.
Assign a number to a variable, instead of a name, to create a positional variable.

Syntax: How to Use Variables in a Procedure

                &[&]name

where:

&name: Is the user-defined name of a local variable. The first character of name must be A through Z.
&&name: Is the user-defined name of a global variable. The first character of name must be A through Z.

The following variables are properly named:

&WHICHPRODUCT
&WHICH_CITY
'&CITY'
&&CITY

The following variables are improperly named for the reason given:

Invalid	Reason
&WHICH CITY	Contains embedded blank.
&WHICH-CITY	Contains a hyphen (-).
WHICHCITY	Leading ampersand(s) is missing.

Local Variables

Once supplied, values for local variables remain in effect throughout a single procedure. The values are lost after the procedure finishes processing and are not passed to other procedures that contain the same variable name.

Example: Using Local Variables

Consider the following procedure in which &CITY, &CODE1, and &CODE2 are local variables.

  .
  .
  .
SQL
SELECT SUM (UNIT_SOLD),
       SUM (RETURNS)
FROM SALES
WHERE CITY = '&CITY'
AND PROD_CODE >= '&CODE1'
AND PROD_CODE <= '&CODE2'
  .
  .
  .

Assume you supply the following values when you call the procedure:

CITY=STAMFORD, CODE1=B10, CODE2=B20

Dialogue Manager substitutes the values for the variables as follows:

  .
  .
  .
SQL
SELECT SUM (UNIT_SOLD),
       SUM (RETURNS), CITY
FROM SALES
WHERE CITY = STAMFORD
AND PROD_CODE >= B10
AND PROD_CODE <= B20
GROUP BY CITY, PROD_CODE
  .
  .
  .

After the procedure executes and terminates, the values STAMFORD, B10, and B20 are lost.

Global Variables

Once a value is supplied for a global variable, it remains in effect throughout the session of a processing service, unless cleared by the server. All procedures that contain the same global variable name receive the supplied value until you terminate the session.

Example: Using Global Variables

The following example illustrates the use of three global variables: &&CITY, &&CODE1, &&CODE2.

  .
  .
  .
SQL
SELECT SUM (UNIT_SOLD),
       SUM (RETURNS)
FROM SALES
WHERE CITY = &&CITY
AND PROD_CODE >= &&CODE1
AND PROD_CODE <= &&CODE2
   .
   .
   .

Predefined System Variables

There are predefined run-time system variables that you can use in a procedure. Dialogue Manager automatically supplies values for these variables whenever the variables are encountered.

The list within this section shows only the commonly used variables. Additional variables exist for other specific features and are documented in the sections for those features.

Unless otherwise noted in the table, you may override run time values by replacing or adding the parameter values specified.

System Variable	Description	Format or Value
&APPROOT	Physical location of the APPROOT directory.	Directory name.
&DATE	Current date.	MM/DD/YY
&DATE`fmt`	Current date.	fmt is any combination of YYMD, MDYY, and so on.
&MDY	Current date. Useful for numerical comparisons.	MMDDYY
&MDYY	Current date (four-digit year).	MMDDCCYY
&DMY	Current date.	DDMMYY
&DMYY	Current date (four-digit year).	DDMMCCYY
&YMD	Current date.	YYMMDD
&YYMD	Current date (four-digit year).	CCYYMMDD
&FOCFOCEXEC	Current running procedure.	Manages reporting operations involving many similarly named requests that are executed using EX. &FOCFOCEXEC enables you to easily determine which procedure is running. &FOCFOCEXEC is specified within a request or in a Dialogue Manager command to display the name of the currently running procedure.
&FOCINCLUDE	Current included procedure.	Manages reporting operations involving many similarly named requests that are included using -INCLUDE. &FOCINCLUDE is specified within a request or in a Dialogue Manager command to display the name of the current included procedure.
&ECHO	Current echo tracing value.	ON, OFF, or ALL
&FOCMODE	Operating environment.	AS/400, MSO, UNIX, VMS or WINNT. You cannot override the system-supplied value. MSO is the value returned on z/OS. If it is necessary to further test for server PDS verses HFS Deployment. The following may be used: -? SET TEMP &XYZ -SET &XYZ = IF &XYZ EQ 'MVS' THEN 'PDS' ELSE 'HFS' ;
&FOCPRINT	Current print setting.	ONLINE OFFLINE
&FOCREL	Source code release number.	Release number (for example, R727706D).
&IORETURN	Value returned after the last Dialogue Manager -READ or -WRITE operation.	`0` Successful operation. `1` End or failure.
&RETCODE	Return code value from execution of a server or operating system command. Referencing &RETCODE forces the execution of all stacked commands, like the command -RUN.	Any value returned by a command is valid (for example, CALLPGM flag values), but zero is considered normal (successful) execution. The one exception is the &RETCODE value of dash operating system commands, such as -DOS, -UNIX, -VMS, -AS/400, and -WINNT, represent the success, not of the command they are running, but of the ability of the server to spawn out to the OS and run the command. In this case, the &RETCODE value is normally zero because it reflects that the spawn executes normally regardless of the results of the specific command. For this case, the amper variable &EXITRC should be used to check the command result or the non-dash version of the command should be used.
&EXITRC	Return code value from execution of an operating system command. Referencing &EXITRC forces the execution of all stacked commands, like the command -RUN.	Any value returned by a command is valid, but zero is considered normal (successful) execution.
&TOD	Current time. When you enter FOCUS, this variable is updated to the current system time only when you execute a MODIFY, SCAN, or FSCAN command. To obtain the exact time during any process, use the HHMMSS subroutine.	HH.MM.SS
&FOCCODEPAGE	Code page of the server when NLS is configured else varies by platform when not configured.	Numeric, such as 297, not changeable.
&FOCLANGCODE	Language of the server when NLS is configured, else blank.	String, such as FRE, not changeable.
&LINES	Number of lines printed in last TABLE.	Numeric, not changeable.
&RECORDS	Number of records retrieved for last TABLE/GRAPH.	Numeric, not changeable.
&TRANS	Number of transactions processed.	Numeric, not changeable.
&ACCEPTS	Accepted transactions.	Numeric, not changeable.
&NOMATCH	Nomatch rejects.	Numeric, not changeable.
&FORMAT	Format errors.	Numeric, not changeable.
&INVALID	Invalid conditions.	Numeric, not changeable.
&DUPLS	Duplicates rejected.	Numeric, not changeable.
&INPUT	Segments input.	Numeric, not changeable.
&CHNGD	Values updated.	Numeric, not changeable.
&DELTD	Segments deleted.	Numeric, not changeable.
&RETCODE	Value after DOS command.	Numeric, not changeable.
&BASEIO	Number of input/output operations.	Numeric, not changeable.
&READS	Read operations from external file.	Numeric, not changeable.
&REJECTS	Number of rejected transactions.	Numeric, not changeable.

Example: Using Predefined Run-Time System Variables

The following example incorporates the system variable &DATE into an SQL request, testing a user-supplied variable (IDATE) against it.

SQL
 SELECT '&DATE',IDATE
 FROM filename 
 WHERE IDATE < '08/08/2004'
-EXIT

Supplying Values for Variables

Topics:

General Rules
Supplying Values in the EXEC Command
Displaying Execution Flow
-DEFAULT[S] Command
-SET Command

You must supply values for variables in a procedure even if the value is a blank. For instance, some server commands are invalid without values but process normally with blanks.

Supply values for variables in the following ways:

With the EXEC command as parameters.
With a command such as -DEFAULTS, -SET, or -READ.

This section describes these methods.

General Rules

The following general rules apply to values for variables:

The maximum length is 32,000 characters when using the -TYPE or -WRITE command.
Once a value is supplied for a local variable, it is used throughout the procedure unless it is changed with a command such as -SET or -READ.
Once a value is supplied for a global variable, it is used throughout the session in all procedures unless it is changed with -SET, -READ, or another command.

Supplying Values in the EXEC Command

How to:

Pass Keyword Parameters
Pass Positional Parameters

Reference:

Combining Positional and Keyword Parameters

The command EXEC enables you to call one procedure from another and set values for variables in the called procedure, using:

Keyword parameters.
Positional parameters.
A combination of keyword and positional parameters.

Syntax: How to Pass Keyword Parameters

EX[EC] procedure                name=value[,...]

where:

procedure

Is the name of the called procedure.

name=value

Is a keyword parameter.

If value contains an embedded comma, blank, or equal sign, it must be enclosed in single quotation marks. For example:

EX SLRPT AREA=S, CITY='NY, NY'

Name=value pairs must be separated by commas. You do not need to enter pairs in the order in which they are encountered in the procedure.

A procedure with a long list of variables or long values for parameters may be broken onto multiple lines by inserting a comma as the last character on the line and entering the rest of the list on the following line.

Syntax: How to Pass Positional Parameters

EX[EC] procedure                 parm1[,...]

where:

procedure

Is the name of the called procedure.

parm1

Is a positional parameter. You do not need to specify the number in the parameter list. Dialogue Manager matches the values, one by one, to the positional variables as they are encountered in the called procedure.

However, you must specify the parameters in the order in which the numeric ampers are used in the procedure.

Consider the following procedure:

SQL
SELECT SUM(UNIT_SOLD),SUM(RETURNS),RETURNS/UNIT_SOLD FROM SALES
WHERE PROD_CODE BETWEEN '&1' AND '&2' AND CITY = '&3' ;
END

The calling procedure would be:

EX SLRPT B10,B20,STAMFORD

Reference: Combining Positional and Keyword Parameters

Consider a procedure with:

SQL
SELECT &1, &2, &FIELD1, &3 FROM CAR;
END

If it is called from another procedure using EXEC, the EXEC command and parameters might look like:

EXEC PPARM1 MODEL,MPG,FIELD1=CAR,COUNTRY

Displaying Execution Flow

How to:

Display Command Lines While Executing

Dialogue Manager implements IF THEN ELSE and other flow logic such as -GOTO. The flow logic and other syntax may be displayed (typically for debugging purposes) by using the &ECHO variable.

Syntax: How to Display Command Lines While Executing

EX xxx ECHO=value

-DEFAULTS &ECHO = value

-SET &ECHO = value

Valid values are:

ON: Displays lines that are expanded and stacked for execution.
ALL: Displays Dialogue Manager commands as well as lines that are expanded and stacked for execution.
OFF: Suppresses display of both stacked lines and Dialogue Manager commands. OFF is the default value.

Note that if the procedure is encrypted, &ECHO automatically receives the value OFF, regardless of the value that is assigned explicitly.

-DEFAULT[S] Command

How to:

Supply Values With the -DEFAULT[S] Command

The Dialogue Manager command -DEFAULTS supplies an initial (default) value for a variable that had no value before the command was processed. It ensures that values are provided for variables whether or not they are provided elsewhere.

Syntax: How to Supply Values With the -DEFAULT[S] Command

-DEFAULT[S] &[&]name=value [...]

where:

&name: Is the name of the variable.
value: Is the default value assigned to the variable.

-SET Command

How to:

Supply Values With the -SET Command

The -SET command is used to assign a value or computed value to a variable.

Syntax: How to Supply Values With the -SET Command

-SET &[&]name={expression|value};

where:

&name: Is the name of the variable. A double amper indicates that the variable is global.
expression: Is a valid expression. An expression may occupy several lines. Each continuation line requires a leading dash (-) in the first character position to indicate that it is a continuation. A -SET always end with a semi-colon.
value: Is a value such as a number or string (enclose the string in single quotation marks if contains an embedded space).

The following are some examples:

Example	Purpose
-SET &NUM = 1 ;	Set value
-SET &FN = JOHN ;	Set value
-SET &LN = 'JOHN';	Set value with quotes
-SET &NAME = 'JOHN DOE';	Set value with quotes required by embedded space
-SET &NAME = &FN \| ' ' \| &LN ;	Set value as concatenated string
-SET &NAME = &LN \|\| ', ' \| &LN ;	Set value using hard concatenated string
-SET &NAME = IF &NAME EQ 'JOHN DOE' THEN 'JOHNATHAN DOE' ELSE &NAME ;	Set value using expression

Branching

Topics:

Screening Values With -IF Tests

How to:

Use the -GOTO Command for Unconditional Branching
Use the -IF...GOTO Command for Conditional Branching

The execution flow of a procedure is determined using the following commands:

–GOTO. Used for unconditional branching, –GOTO transfers control to a label.
–IF...GOTO. Used for conditional branching, –IF...GOTO transfers control to a label depending on the outcome of a test.

Syntax: How to Use the -GOTO Command for Unconditional Branching

-GOTO label 
  .
  .
  .
-label [TYPE text]

where:

label: Is a user-defined name of up to 64 characters. Embedded blanks in labels or using other Dialogue Manager commands as labels is not allowed. It is also advised to not use words that may be confused with functions or arithmetic or logical operations. If a label is not found by search downward in the procedure, it is searched from the beginning a single time (if still not found a user error will occur).
TYPE text: Optionally sends a message to the application.

Syntax: How to Use the -IF...GOTO Command for Conditional Branching

-IF expression [THEN] GOTO label ;
-IF expression [THEN] GOTO label  ELSE GOTO label ; 
-IF expression [THEN] GOTO label  ELSE IF... ; 
.
.
.
-label  [TYPE text]

where:

label: Is a user-defined name of up to 64 characters. Embedded blanks in labels, or using other Dialogue Manager commands as labels is not allowed. It is also advised to not use words that may be confused with functions or arithmetic or logical operations. If a label is not found by search downward in the procedure, it is searched from the beginning a single time (if still not found a user error will occur).
expression: Is a valid expression. Literals need not be enclosed in single quotation marks unless they contain embedded blanks or commas.
THEN: Is an optional keyword that increases readability of the command.
ELSE GOTO: Optionally passes control to label2 when the -IF test fails.
ELSE IF: Optionally specifies a compound -IF test.
TYPE text: Optionally sends a message to a client application.

Continuation lines must begin with a hyphen (-) and lines must break between words. A space after the hyphen is not required, but it adds to readability.

Screening Values With -IF Tests

How to:

Test for the Presence of a Value
Test for the Length of a Value
Test for the Type of a Value

To ensure that a supplied value is valid in a procedure, test for its:

Presence
Type
Length

For instance, you would not want to perform a numerical computation on a variable for which alphanumeric data has been supplied.

Syntax: How to Test for the Presence of a Value

-IF &name.EXIST GOTO label...;

where:

&name: Is a user-supplied variable.
.EXIST: Indicates that you are testing for the presence of a value. If a value is not present, a zero (0) is passed to the expression. Otherwise, a non-zero value is passed.
GOTO label: Specifies a label to branch to.

Syntax: How to Test for the Length of a Value

-IF &name.LENGTH expression GOTO label...;

where:

&name: Is a user-supplied variable.
.LENGTH: Indicates that you are testing for the length of a value. If a value is not present, a zero (0) is passed to the expression. Otherwise, the number of characters in the value is passed.
expression: Is the remainder of a valid expression, such as GT 8.
GOTO label: Specifies a label to branch to.

Syntax: How to Test for the Type of a Value

-IF &name.TYPE expression GOTO label...;

where:

&name: Is a user-supplied variable.
.TYPE: Indicates that you are testing for the type of a value. The letter N (numeric) is passed to the expression if the value is interpreted as a number up to 10^9–1 and is stored in four bytes as a floating point format. In Dialogue Manager, the result of an arithmetic operation with numeric fields is truncated to an integer after the whole result of an expression is calculated. If the value could not be interpreted as numeric, the letter A (alphanumeric) is passed to the expression.
expression: Is the remainder of a valid expression, such as EQ A.
GOTO label: Specifies a label to branch to.

Calling Another Procedure

Topics:

Nesting
The EXEC Command

How to:

Use the -INCLUDE Command

One procedure calls another procedure using:

The -INCLUDE file command, which incorporates a whole or partial procedure into the current procedure and executes it immediately when encountered. Unlike EXEC, no parameters may be passed with -INCLUDE, but they may be -SET on lines prior to the -INCLUDE. A -INCLUDE procedure can be a partial procedure that may contain header text or code to include at run time based on a step in the originating procedure.
The command EXEC. The command is stacked and executed when the appropriate Dialogue Manager command is encountered. The called procedure must be fully executable.

Syntax: How to Use the -INCLUDE Command

Lines incorporated with a -INCLUDE are processed as though they had been placed in the calling procedure originally.

-INCLUDE filename

where:

filename: Is the name of the called procedure.

A calling procedure cannot branch to a label in a called procedure, and vice versa.

Nesting

Any number of different procedures may be invoked as a -INCLUDE from within a single calling procedure. Called procedures can also call (nest) additional procedures. The same procedure may even call itself recursively. However, recursive calls are limited to four levels deep.

The EXEC Command

How to:

Use the EXEC (or EX) Command

A procedure also calls another one with the command EXEC. The called procedure must be fully executable.

See Supplying Values for Variables for additional information about passing parameter values.

Syntax: How to Use the EXEC (or EX) Command

Procedure lines with EXEC (or EX) commands temporarily stop execution of the current procedure and call the new procedure. The new procedure may be passed current variables that are available as parameter values to the new procedure, but must do so explicitly unless those variables are global variables.

 EXEC [appname/]filename [parm[,...]]

where:

appname: Is the location of the called procedure.
filename: Is the name of the called procedure.
parm[,...]: Are one or more parameters being passed, either positionally or by name.

Creating Expressions

Topics:

Arithmetic Expressions
Alphanumeric Expressions
Logical Expressions
Compound Expressions

An expression consists of variables and literals (numeric or alphanumeric constants) that are combined arithmetically, logically, or in some other way to create a new value.

This section describes how to create:

Arithmetic Expressions

Reference:

Guidelines for Using Arithmetic Expressions

An arithmetic expression is:

A numeric constant, for example, 1.

Two variables joined by one of the following arithmetic operators:

Operator	Meaning
+	Addition
-	Subtraction
*	Multiplication
/	Division
**	Exponentiation

An example is:

&DELIVER_AMT / &OPENING_AMT

Two or more arithmetic expressions, joined by one of the operators in the preceding list. An example is:
```
(&RATIO - 1) ** 2
```
A compound expression or function that gives an arithmetic result.

Example: Using Arithmetic Expressions

Following are some arithmetic expressions used in the command -SET:

-SET &COUNT = 1;
-SET &NEWVAL = (&RATIO - 1) ** 2; 
-SET &RATIO = (&DELIVER_AMT * 100) / (&OPENING_AMT);

Reference: Guidelines for Using Arithmetic Expressions

Keep the following in mind as you create arithmetic expressions:

If you attempt to divide by 0, Dialogue Manager sets the result to 0.

Arithmetic operations are performed before logical operations, in the following order:

Operator	Meaning
**	Exponentiation
/ *	Division and multiplication
+ -	Addition and subtraction

For operations on the same level (for example, division and multiplication), the evaluation is performed from left to right.
An expression in parentheses is evaluated before any other expression.
Values for local and global variables (amper variables) are stored internally as character strings, including numeric values. If a calculation is performed on an amper variable, the variable is first converted from a character string into a numeric. After the whole result is calculated, the result of arithmetic operations with numeric fields is truncated to the integer field. Finally, the result is converted back into a character string.

Alphanumeric Expressions

How to:

Concatenate Alphanumeric Variables and Literals

An alphanumeric expression is:

A literal enclosed in single quotation marks, for example 'Smith John'.
A logical expression that yields an alphanumeric result.
A function that yields an alphanumeric result.
Two or more alphanumeric variables or literals combined into a single string. See Concatenate Alphanumeric Variables and Literals for the syntax and an example.

Syntax: How to Concatenate Alphanumeric Variables and Literals

variablename = {alphaexp1|'literal'} concatenation 
{alphaexp2|'literal'} [...]

where:

variablename

Is the name of the variable assigned to the result of the concatenation.

alphaexp1, alphaexp2

Are local or global variable that forms part of the concatenation.

literal

Is a literal that forms part of the concatenation. It must be enclosed in single quotation marks.

concatenation

Is one of the following symbols:

\|\|	Indicates strong (also known as hard) concatenation, which suppresses trailing blanks.
\|	Indicates weak concatenation, which preserves individual field lengths, including trailing blanks.

Example: Concatenating Alphanumeric Variables and Literals

-SET &NAME = &LASTNAME || ',' || &FIRST_INIT;

If &LASTNAME is equal to Doe and &FIRST_INIT is equal to J, &NAME is set to:

Doe,J

Logical Expressions

Reference:

Guidelines for Alphanumeric and Logical Expressions

A logical expression contains logical and relational operators and is evaluated to a value that is true or false.

Example: Forming a Logical Expression

This example shows various elements that are used to form a logical expression. The abbreviation exp stands for expression.

{arithmetic exp|alphanumeric exp} operator1 {numeric lit|alphanumeric lit} OR...
  
expression              operator2              expression               
logical exp {AND|OR} logical exp 
 
NOT logical exp

where:

operator1: Is one of the following: EQ, NE, OMITS, or CONTAINS.
expression: Is either an arithmetic, alphanumeric, or logical expression.
operator2: Is one of the following: EQ, NE, LE, LT, GE, or GT.

The following table defines valid operators (EQ, NE, and so on) used in this example.

Operator	Description
EQ	Tests for a value equal to another value.
NE	Tests for a value not equal to another value.
OMITS	Tests for a value that does not contain a matching character string.
CONTAINS	Tests for a value that does contain a matching character string.
LE	Tests for a value less than or equal to another value.
LT	Tests for a value less than another value.
GE	Tests for a value greater than or equal to another value.
GT	Tests for a value greater than another value.
AND	Returns a value of true if both of its operands are true.
OR	Returns a value of true if either of its operands is true.
NOT	Returns a value of true if the operand is false.

Reference: Guidelines for Alphanumeric and Logical Expressions

Keep the following in mind:

An alphanumeric literal with embedded blanks or commas must be enclosed in single quotation marks. For example:
```
-IF &NAME EQ 'JOHN DOE' GOTO QUIT;
```
To produce a single quotation mark within a literal, place two single quotation marks where you want one to appear:
```
-IF &NAME EQ 'JOHN O''HARA' GOTO QUIT;
```
A computational field may be assigned a value by equating it to a logical expression. If the expression is true, the field has a value of 1; if the expression is false, the field has a value of 0.
Use OR to connect literals or other expressions. You must also use parentheses to separate expressions connected with OR.
Logical operations are done after arithmetic operations, in the following order:
```
EQ NE LE LT GE GT NOT CONTAINS OMITS
```
```
AND
```
```
OR
```
Separate a collection of test values with OR:
```
-IF &STATE EQ 'NY' OR 'NJ' OR 'WA' GOTO QUIT;
```
In this case, OR and EQ are evaluated at the same level.
Use parentheses to specify a desired order. An expression in parentheses is evaluated before any other expression. For example, the command
```
-IF &STATE EQ 'NY' AND &COUNTRY EQ 'US' OR 'UK' THEN...
```
is evaluated as:
```
IF &STATE EQ 'NY' IF &COUNTRY EQ 'US'...
```
Dialogue Manager then evaluates the phrase OR UK and indicates that it is a syntax error.

To write the command correctly, add parentheses:
```
-IF ((&STATE EQ 'NY') AND (&COUNTRY EQ 'US' OR 'UK')) THEN...
```

Compound Expressions

A compound expression has the following form:

-IF expression THEN expression ELSE expression;

The following restrictions apply:

Each of the expressions may itself be a compound expression, although the expression following -IF may not be a -IF...THEN...ELSE expression (for example, -IF...-IF...).
If the expression following THEN is itself a compound expression, it must be enclosed in parentheses; this rule does not apply to an expression following ELSE.
Compound expressions only have up to 16 -IF commands.

Example: Using Compound Expressions

If the following example is executed without an input parameter list, the client application receives the message NONE. If it executes with the parameter BANK='FIRST NATIONAL', the client application receives the message FIRST NATIONAL.

-DEFAULTS &BANK = ' '
-SET &BANK = IF &BANK EQ ' ' THEN 'NONE'
-ELSE &BANK;
-TYPE &BANK

The next example uses a compound expression to define a truth condition (1 is true and 0 is false).

-DEFAULTS &CURR_SAL = 900,&DEPARTMENT=MIS
-SET &MYTEST = (&CURR_SAL GE 1000) OR (&DEPARTMENT EQ MIS);
-IF &MYTEST EQ 1 THEN GOTO YES ELSE GOTO NO;
-YES
-TYPE YES
-EXIT
-NO
-TYPE NO

When this code is executed, the client application receives the message YES.

ON TABLE HOLD

How to:

Use the ON TABLE HOLD Command

When a server receives the results of an SQL request (an answer set) from another server, the answer set will either:

Be returned to the client application using ON TABLE PCHOLD. That command is described in ON TABLE PCHOLD.
Be held on the initiating server, without sending it back to the client application, using ON TABLE HOLD. A corresponding Master File for the file that holds the answer set is also created.

Syntax: How to Use the ON TABLE HOLD Command

SQL 
SQL request;
ON TABLE HOLD [AS filename] FORMAT format 
END

where:

filename

Is the name of the file that holds the answer set. If filename is omitted, the name of the held file on the server is HOLD, and subsequent creations of HOLD files overlay each other. The file name is a symbolic name known to the operating system for the server environment.

format

A valid format option for the server. The following is a list of commonly used values are:

ALPHA, BINARY, COMMA, COMT, DB2, DIF, DOC (WebFOCUS ONLY),
EXCEL, EXL2K (WebFOCUS ONLY), EXL2K PIVOT (WebFOCUS ONLY), 
FOCUS, HTML, HTMTABLE, INGRES, INTERNAL, LOTUS, PDF, POSTSCRIPT, REDBRICK, SQL, SQLDBC, SQLINF, SQLMAC, SQLMSS, SQLODBC, SQLORA, 
SQLSYB, SYLK, TABT, WK1, and WP.

The above list is not intended to be a complete list, and the use of some of these formats is limited to use with a configured adapter.

END

Is required on a separate line.

ON TABLE PCHOLD

How to:

Use the ON TABLE PCHOLD Command

In order for a Dialogue Manager procedure to return an answer set to a client application, a certain set of commands must be issued directly after the SQL request in the syntax of the procedure.

Syntax: How to Use the ON TABLE PCHOLD Command

SQL  
SQL request;
TABLE
ON TABLE PCHOLD [FORMAT ALPHA]
END

where:

SQL request;: Is a valid SQL request, ending with a semicolon.
FORMAT ALPHA: Optionally specifies that the hold file on the client is a text file. Use any valid format available on the client, but the underlying transfer is in alpha format. FORMAT ALPHA is the default value.
END: Is required on a separate line.

Example: Using the ON TABLE PCHOLD Command

This example shows how the ON TABLE PCHOLD command requests information from a table in a catalog.

SQL
SELECT NAME, CREATOR, COLCOUNT, RECLENGTH FROM SYSTABLE;
TABLE
ON TABLE PCHOLD FORMAT ALPHA
END

The result of the request is an answer set sent to the client application by the server.

	WebFOCUS

Feedback