Procedure Run-Time Processing Essentials

In this section:

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.


Top of page

x
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:


Top of page

x
Dialogue Manager Commands and Processing

In this section:

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.



x
Dialogue Manager Command Processing in a Procedure

A procedure processes as follows:



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:

  1. 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.)

  2. 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.
  3. 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.

  4. The Dialogue Manager command -RUN forces the stacked commands to execute.
  5. The Dialogue Manager command -EXIT terminates the procedure.

Top of page

x
Using Variables

In this section:

This section describes how to use variables in a procedure.

Variables fall into two categories:

The following features apply to all variables:



x
Naming Conventions

This section describes how to use variables in a procedure.

Variables fall into two categories:

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:



x
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.



x
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.



x
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
   .
   .
   .


x
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 
&DATEfmt

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

Top of page

x
Supplying Values for Variables

In this section:

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:

This section describes these methods.



x
General Rules

The following general rules apply to values for variables:



x
Supplying Values in the EXEC Command

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



x
Syntax: How to Pass Keyword Parameters
EX[EC] procedurename=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.



x
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


x
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


x
Displaying Execution Flow

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.



x
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.



x
-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.



x
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.



x
-SET Command

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



x
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



x
Branching

In this section:

How to:

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



x
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.



x
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.



x
Screening Values With -IF Tests

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

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



x
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.



x
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.



x
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 109–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.


Top of page

x
Calling Another Procedure

In this section:

How to:

One procedure calls another procedure using:



x
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.



x
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.



x
The EXEC 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.



x
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.


Top of page

x
Creating Expressions

In this section:

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:



x
Arithmetic Expressions

An arithmetic expression is:



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);




x
Alphanumeric Expressions

An alphanumeric expression is:



x
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


x
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...
  
expressionoperator2expression 
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.



x
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...


x
Compound Expressions

A compound expression has the following form:

-IF expression THEN expression ELSE expression;

The following restrictions apply:



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.


Top of page

x
ON TABLE HOLD

How to:

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



x
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.


Top of page

x
ON TABLE PCHOLD

How to:

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.



x
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.


iWay Software