Chapter 14: Online Express

This chapter contains information on navigating in Online Express, SQL database access, screens, and control points.

SQL Database Access Screen Navigation

Screens

Alternate Functions

Define teleprocessing and user-defined functions for character programs; this screen initially displays the function codes entered on the Program Definition screen.

Access from:	Online Express Menu or any primary screen, enter 2 in the Command field.
Transfer to:	Previous, or invoking, screen, press F3.
	Program Definition screen, enter 1 in the Command field.
	PF Key Functions screen, enter 3 in the Command field.
	Field Mapping screen, enter 4 in the Command field.
	Control Points screen, enter 5 in the Command field.
	Database Access screen, enter 6 in the Command field.
	Savekey Definition screen, enter 7 in the Command field.
	Specification Editor screen, enter E in the Function field.

Field

Description and Values

Program Input

Function codes from the Program Definition screen are automatically displayed. Add your own function code, 8-character maximum.

Function

Processing action. Valid codes are:

*	Execute Online Express predefined function.
$	Invoke user macro.
A	Alias. Specifies that the function code in adjacent field is equivalent to the function code in Function Name field.
C	Call subroutine (LINK in CICS).
E	Display Specification Editor for writing local stub. E changes to L after local stub is written.
G	Execute global stub.
L	Execute local stub.
M	For IMS or ISPF prototyping. MSG-SW. Transfer control to program.
P	Perform paragraph.
S	Send screen.
X	For CICS, ISPF Dialog, or ISPF prototyping. XCTL. Transfer control to program

Reserved Function
or Function Name

Object of action coded in function field. Valid actions and their objects are:

Code	Function/Name
*	*ADD\|ADD-ROW
	*BACKWARD
	*CLEAR
	*DELETE\|DELETE-ROW
	*FORWARD
	*NEXT
	*QUERY\|QUERY-ROW
	*TERM
	*UPDATE\|UPDATE-ROW
$	macroname [arguments]
A	functioncode
C	programname [userparms]
E	Invoke Specification Editor screen
G	stubname
L	Indicates local stub; value PAINTED or NOT PAINTED displays
M	programname [screenname] [keywords]
P	paragraphname
S	screenname [keywords]
X	programname [(NONAPS)] [userparms]

Row Input

Y	Function applies to a repeated block of records.
N	Function applies to non-repeated records.

Column Selection

Display and select from a list of columns for each database access on the Column Selection screen, and then update criteria by applying functions or specifying Into destinations on the Column Selection Update screen. View column information on the Column Information screen.

Access the Column Selection screen from:	Database Access Summary screen, enter C in the selection field.
	Subselect Specification screen, enter S in both the column and record selection fields.
	Union Summary Menu screen, enter C in the selection field.
Transfer to:	Column Selection Update, press F3.
	Column Information, enter ? in the column selection field.
Access Column Selection Update screen from:	Column Selection screen, press F3.
Transfer to:	Previous screen, press F3.
	Column Information screen, enter ? in the column selection field.
	SQL Functions, enter F in the selection field.
Access Column Information screen from:	Column Selection screen, enter ? in the column selection field.
	Column Selection Update, enter ? in the column selection field.
	SQL Qualification Specification, enter ? in the column selection field.
Transfer to:	Previous, or invoking, screen, press the Enter key.

Field

Description and Values

Distinct

Enter YES to eliminate duplicate rows.

Function

Built-in functions:

DA\|DATE	Current date
TI\|TIME	Current time
TS\|TIMESTAMP	Timestamp
*CURRENT	Automatically updates column with correct value

Or, enter a valid SQL function.

Or, enter F in the selection field to select a SQL function from the SQL Functions screen.

Column Name

Lists the columns to be acted on when call is executed. The columns are returned into corresponding host variables if the call is a SELECT, or they are updated with the values in the host variables if the call is an UPDATE or STORE.

COBOL Name

Override default host variable by typing INTO for SELECTs or a literal for UPDATEs or STOREs.

Control Points

Insert user-defined logic at standard program control points.

Access from:

Online Express Menu or any primary screen, enter 5 in the Command field.

Transfer to:

Previous, or invoking, screen, press F3.

Specification Editor, enter E in the Action field.

Field

Description and Values

Action

Processing action. See below for values.

Exit Name

Object of Action coded. Valid Actions and their Exit Names are:

Code	Exit Name
*	*ADD
	*BACKWARD
	*CLEAR
	*DELETE
	*FORWARD
	*NEXT
	*QUERY
	*TERM
	*UPDATE
$	macroname [arguments]
E	Invoke Specification Editor screen
G	stubname
L	Indicates local stub; value PAINTED or NOT PAINTED displays
P	paragraphname

Correlation Names

Override the Online Express default correlation name when you specify a JOIN on the Correlation Names screen. Choose your own names, with values up to four characters.

Access from:

Database Access Summary screen, enter N in the selection field.

Transfer to:

Database Access Summary screen, press F3.

Database Access Summary

This screen displays a composite picture of the database accesses and can be used as a menu to access other database access specification screens.

Access from:	Online Express Menu or any primary screen, enter 6 in the Command field.
Transfer to:	Previous screen, press F3.
	Any primary screen, enter its numeral in the Command Field.
	Column Selection screen, enter C in the selection field.
	Correlation Names screen, enter N in the selection field.
	Database Call Tailoring screen, enter T in the selection field.
	Database Qualification screen, enter K in the selection field.
	Database Record Selection screen, enter S in the selection field.
	Order By Columns screen, enter O in the selection field.
	SQL Command Review screen, enter V in the selection field.
	SQL Qualification Specification screen, enter Q in the selection field.
	Union Summary Menu screen, enter U in the selection field.
	User-Controlled Database Calls screen, enter *USER in the Command field.

Field

Description and Values

Selection Field

Enter code for line commands or for transferring to another screen:

A	Line command: After
B	Line command: Before
C	Transfer to Column Selection (SQL only)
D	Line command: Delete
I	Line command: Insert
M	Line command: Move
N	Transfer to Correlation Names (SQL only)
O	Transfer to Order By Columns (SQL only)
Q	Transfer to Database Qualification or SQL Qualification
U	Transfer to Union Summary Menu (SQL only)
V	Transfer to SQL Command Review (SQL only)

Function

Optionally associate the following functions with one of the adjacent Action values:

*QUERY
*UPDATE
*ADD
*DELETE
*USER

Action

O	Obtain
S	Store
M	Modify
L	Loop
E	Erase
P	Position (IMS or IDMS)

Qualifier

*BOOLEAN	Record is qualified on two or more fields or columns.
*CURRENT	Record access is based on current positioning.
*KEYQUAL	Record is qualified on a single key field or column.
*NO-QUAL	Record access is not qualified.

Nesting

Indicates whether an access is nested within a loop call. The default nesting level number displays:

0	Not nested; call is executed independently of other calls.
- 1	Call is nested within the preceding level 0 loop.
--2	Call is nested within the preceding level 1 loop.

You may override the nesting level with a lesser value.

Blk

Default is 1 assigned for first loop call only; indicates which database access loop is associated with the repeated block on the screen. To override default, blank it out; optionally assign 1 to another loop access.

Database Call Tailoring

Modify the processing of database calls. Each modifiable call has its own corresponding Call Tailoring screen.

Access from:

Database Access Summary screen, enter T in the selection field.

Transfer to:

Database Access Summary screen, press F3.

Specification Editor, enter E in the Action field.

Field

Description and Values

Action to be Tailored

O	Obtain
E	Erase
M	Modify
S	Store

Control Points

Access control points displayed for Modify, Store, Erase, and Obtain.

Action

$	Invoke user macro
E	Display Specification Editor for writing local stub. E changes to L after local stub is written
G	Execute global stub
L	Specify local stub
P	Perform paragraph

Control Point Name

Object of Action coded. Valid actions and their objects are:

Code	Name
$	macroname [arguments]
E	Press Enter to invoke Specification Editor
G	stubname
L	Value PAINTED or NOT PAINTED displays
P	paragraphname

Status Matrix

Override default status codes for any status flag with one of the following:

N	Normal condition.
X	Exception condition; access not in error.
E	Error condition; access in error, message displays, program aborts when Abort field value is Y.

Flag	Default
OK (OK-ON-REC)	N
END (END-ON-REC)	E
NTF (NTF-ON-REC)	E
DUP (DUP-ON-REC)	E
VIO (VIO-ON-REC)	E

Error Message

Defaults to standard Online Express messages. Other valid values are a text error message or user-defined macro name, without delimiters; 44 characters maximum.

Error Message Type

S	Default. Standard Online Express error message identifying record and type of access attempted.
T	Text message entered in Error Message field.
M	User macro named in Error Message field.

Abort on Error

Y	Default. Stop processing when Error status code returned.
N	Do not stop processing.

Database Qualification

Specify the access qualifications of the Obtain of any record.

Access from:

Database Access Summary screen, enter K in the selection field.

Database Record Selection screen, enter S in the selection field.

Transfer to:

Previous screen, press F3.

Field

Description and Values

Selection Field

I	Insert
D	Delete
R	Repeat

Type of field. Values displayed:

AD	Address field DB-KEY
CA	IDMS only. CALC key
KY	Key field
PR	VSAM primary index
SQ	IDMS and IMS only. Field upon which records are sequenced
SR	Nonunique field

=\|EQ	Equal to
>\|GT	Greater than
<\|LT	Less than
>=\|GE	Greater than or equal to
<=\|LE	Less than or equal to
^=\|NE	Not equal to

Value

COBOL data name, variable, or literal on which to qualify field.

Len

Length of field. Qualify on a VSAM partial key by overriding the full length with a partial length.

Bool

To specify Boolean qualification, type AND or OR in the field.

Database Record Selection

Define database accesses on the record selection list presented by the Database Record Selection screen. You can define multiple accesses for a maximum of 50 records. JOINs and UNIONs are supported for SQL.

Access from:	Database Access Summary screen, enter S in the selection field.
	Union Summary Menu, enter S in the selection field.
Transfer to :	Previous screen, press F3.
	Database Qualification screen, enter S in the selection field.

Field

Description and Values

Action

Specify one or more database accesses for a record. Database accesses correspond to and complete the definition of database processing functions. Specify DB accesses that correspond to DB functions as follows:

Access	Function
O(btain)	Query, Forward, Next
M(odify)	Update
E(rase)	Delete
S(tore)	Add
L(oop)	Use with Obtain to define record loop; also needed with Forward.
P(osition)	Use for IMS and IDMS relationship navigation.

Express Parameters

Specify generation options.

Access from:

Online Express Menu or any primary screen, enter P in the Command field.

Transfer to:

Previous screen, press F3.

Field

Description and Values

Control Point Comments

YES	Include standard control point comments in the generated program.
NO	Default. Do not include.

Interscreen Moves

YES	Move screen field values from the sending screen to the receiving screen when the SEND or MSG-SW functions execute.
NO	Default. Do not move.

Database Calls

YES	Default when subschema defined. Generate APS database calls.
NO	Default when no subschema. Do not generate.

Clear Screen with LOW-VALUES

YES	Default for CICS applications. Clear repeated record blocks with LOW-VALUES.
NO	Default for IMS, DDS, and ISPF Dialog applications. Clear repeated record blocks with spaces.

Use SYSMSG Field

PM only.

YES	Default. Display system messages in the SYSMSG box.
NO	Display system messages in a PM message box.

Display Union Support Info

YES	Automatically display Express Union Support screen whenever you define or review a UNION.
NO	Do not display.

Generate to Program Painter

YES	Default. Write program to APSPROG.
NO	Save specifications but do not write to APSPROG.

Field Mapping

Map screen or screen fields to program fields; specify the database record fields that correspond to the screen or screen fields.

Access from:	Online Express Menu or any primary screen, enter 4 in the Command field.
Transfer to:	Previous screen, press F3.
	Program Definition, enter 1 in the Command field.
	Alternate Functions screen, enter 2 in the Command field.
	PF Key Functions screen, enter 3 in the Command field.
	Control Points screen, enter 5 in the Command field.
	Database Access screen, enter 6 in the Command field.
	Savekey Definition screen, enter 7 in the Command field.

Field

Description and Values

Screen Field

Automatically supplied. An (RB1) appearing after a screen name indicates the field is in a repeated block.

Selection Field

D	Delete continuation line.
E	Select field for edit specification.
I	Insert continuation line.

I/O/B

I	Input. Data moves from screen or screen to database record.
O	Output. Data moves from database record to screen or screen.
B	Both (default). Data moves in both directions.

Program Field

Database field name that supplies or accepts data from screen or screen field.

Command	Description
*	Asterisk command. Duplicate the names of all screen or screen fields in the Program Field, except function, savekey, and system message fields. Enter B in I/O/B field. Note: Command does not overwrite any program field name previously entered.
D and I	Line commands. Specify continuation for qualifying a program field.
FE	Access the GUI Painter or the Screen Painter Field Selection screen to display field edits.
L linenumber	Positions a specific line at the top of screen.
PRE[FIX] fieldprefix [beginlinenumber[endlinenumber]]	Prefix program field with named prefix. An * for beginlinenumber or endlinenumber means the first or last line.
RES[ET] [beginlinenumber[endlinenumber]]	Reset screen by blanking out I/O/B and Program Field fields. An * for beginlinenumber or endlinenumber means the first or last line.

Menu Item Functions

Define teleprocessing and user-defined functions for GUI menu controls.

Access from:	Online Express Menu or any primary screen, enter 2.1 in the Command field.
Transfer to:	Previous, or invoking, screen, press F3.
	Program Definition screen, enter 1 in the Command field.
	Pushbutton Functions screen, enter 2.2 in the Command field.
	Field Mapping screen, enter 4 in the Command field.
	Control Points screen, enter 5 in the Command field.
	Database Access screen, enter 6 in the Command field.
	Savekey Definition screen, enter 7 in the Command field.
	Specification Editor screen, enter E in the Function field.

Field

Description and Values

Menu Item

Menu controls automatically display.

Function

Processing action. Valid codes are:

*	Execute Online Express predefined function.
$	Invoke user macro.
C	Call subroutine (LINK in CICS).
E	Display Specification Editor for writing local stub. E changes to L after local stub is written.
G	Execute global stub.
L	Execute local stub.
P	Perform paragraph.
S	SEND screen.
X	Transfer control to program.

Reserved Function
or Function Name

Object of action coded in function field. Valid actions and their objects are:

Code	Function/Name
*	ADD CLEAR DELETE NEXT QUERY TERM *UPDATE
$	macroname [arguments]
C	programname [userparms]
E	Invoke Specification Editor screen
G	stubname
L	Indicates local stub; value PAINTED or NOT PAINTED displays
P	paragraphname
S	screenname [keywords]
X	programname [(NONAPS)] [userparms]

Online Express Information Summary

View summary information about your application, including application, program, screen or screen, and subschema names, and update statistics.

Access from:

Any Online Express screen, enter OXINFO in the Command field.

Transfer to:

Previous screen, press F3.

Online Express Menu

Select screens for program specifications.

Option	Description
1	Display Program Definition screen to specify program information and functions.
2	Display Alternate Functions screen to define application and TP functions.
3	Display PF Key Functions screen to assign PF key functions.
4	Display Field Mapping screen to map screen fields to program fields.
5	Display Control Points screen to add application-specific logic.
6	Display Database Access Summary screen when database access specifications exist; display Database Record Selection screen to specify database access.
7	Display Savekey Definition screen to specify savekey storage requirements.
SC	Invoke APS Screen Painter or APS GUI Painter to change a screen field or screen control.
P	Display Express Parms screen to set generation options and display Union support.

Command	Description
COPY programname	Copy specifications of an existing Online Express program.
FE	Access the GUI Painter or the Screen Painter Field Selection screen.
GEN	Save program specifications, write program to Program Painter, and generate and compile program.
REPORT\|REP	Submit batch job to generate Program Definition report, which documents all specifications entered on Online Express screens.
SC	Transfer to the Screen Painter or GUI Painter.

Order by Columns

Specify row retrieval order by adding, deleting, or rearranging the order of columns.

Access from:

Database Access Summary screen, enter O in the selection field.

Union Summary Menu screen, enter O in the Command field.

Transfer to:

Previous screen, press F3.

Field

Description and Values

Loop Max

Define maximum number of SQL processing loops; maximum is 99999.

Selection Field

A	After
I	Insert
B	Before
M	Move
D	Delete
?	Obtain column information

Column Name

Online Express default is displayed. Add, delete, or type over column name to reorder.

Seq

A	Default. Ascending sequence
D	Descending

Index

Y	Unique
N	Not unique

Commands

Description

LISTCOL

Display the columns.

PF Key Functions

Associate any function (except the Alias function) to PF keys.

Access from:	Online Express Menu or any primary screen, enter 3 in the Command field.
Transfer to:	Previous screen, press F3.
	Program Definition screen, enter 1 in the Command field.
	Alternate Functions screen, enter 2 in the Command field.
	Field Mapping screen, enter 4 in the Command field.
	Control Points screen, enter 5 in the Command field.
	Database Access screen, enter 6 in the Command field.
	Savekey Definition screen, enter 7 in the Command field.
	Special Key Definition screen, enter SPC in the Command field.
	Specification Editor, enter E in the Function field.

Field

Description and Values

Function

Processing action. Valid codes are:

*	Execute Online Express predefined function.
$	Invoke user macro.
A	Alias. Specifies that the function code in adjacent field is equivalent to the function code in Function Name field.
C	Call subroutine (LINK in CICS).
E	Display Specification Editor for writing local stub. E changes to L after local stub is written.
G	Execute global stub.
L	Execute local stub.
M	For IMS or ISPF prototyping. MSG-SW. Transfer control to program.
P	Perform paragraph.
S	Send screen or screen.
X	For CICS, ISPF Dialog, or ISPF prototyping. XCTL. Transfer control to program

Reserved Function
or Function Name

Object of action coded in function field. Valid actions and their objects are:

Code	Function/Name
*	ADD\|ADD-ROW BACKWARD CLEAR DELETE\|DELETE-ROW FORWARD NEXT QUERY\|QUERY-ROW TERM *UPDATE\|UPDATE-ROW
$	macroname [arguments]
C	programname [userparms]
E	Invoke Specification Editor screen
G	stubname
L	Indicates local stub; value PAINTED or NOT PAINTED displays
M	programname [screenname\|screenname] [keywords]
P	paragraphname
S	screenname\|screenname [keywords]
X	programname [(NONAPS)] [userparms]

Commands	Description
*	Copy functions defined for PFKEY01 through PFKEY12 to PFKEY13 through PFKEY24.

Program Definition

Specify basic program information and functions.

Access from:	Online Express Menu or any primary screen, enter 1 in the Command field.
Transfer to:	Previous screen, press F3.
	Alternate Functions screen, enter 2 in the Command field.
	PF Key Functions screen, enter 3 in the Command field.
	Field Mapping screen, enter 4 in the Command field.
	Control Points screen, enter 5 in the Command field.
	Database Access screen, enter 6 in the Command field.
	Savekey Definition screen, enter 7 in the Command field.

Field

Description and Values

Function Field

Name of function code field from associated screen or screen.

Function Codes

Specify one or more predefined function codes to be allowed by program.

Q	Query. Retrieve record.
U	Update record.
A	Add. Create new record.
D	Delete record.
N	Next. Retrieve next sequential record, based on qualification of previous record accessed.
R	Refresh. Re-read the database when the end user executes a write function on a repeated record block row and re-display the record block.
F	Forward. Retrieve next block of repeated records.
B	Backward. Retrieve previous block of repeated records.
C	Clear. Move spaces to all screen or screen fields.
E	Exit. Terminate program.

Row Function Field

Field associated with each record of a repeated block. End user enters row function codes in this field.

Row Function Codes

Predefined row function codes to be allowed by program:

U	Update record
A	Create new record
D	Delete record

SYSMSG Field

System message field specified in Screen Painter.

Savekey Definition

Specify savekey storage requirements. This screen displays automatically calculated savekey storage requirements; specify either screen fields or the program Commarea to fulfill the requirements.

Access from:	Online Express Menu or any primary screen, enter 7 in the Command field.
Transfer to:	Previous screen, press F3.
	Program Definition screen, enter 1 in the Command field.
	Alternate Functions screen, enter 2 in the Command field.
	PF Key Functions screen, enter 3 in the Command field.
	Field Mapping screen, enter 4 in the Command field.
	Control Points screen, enter 5 in the Command field.
	Database Access screen, enter 6 in the Command field.

Special Key Definition

Associate any function (except Alias ) to the ENTER, CLEAR, or PA1-3 (CICS only) keys.

Access from:

PF Key Functions screen, enter SPC in the Command field.

Transfer to:

PF Key Functions screen, press F3.

Specification Editor, enter E in Option field.

Field

Description and Values

Option

Processing action. Valid codes are:

*	Execute Online Express predefined function.
$	Invoke user macro.
C	Call subroutine (LINK in CICS).
E	Display Specification Editor for writing local stub. E changes to L after local stub is written.
G	Execute global stub.
L	Execute local stub.
M	For IMS or ISPF prototyping. MSG-SW. Transfer control to program.
P	Perform paragraph.
S	Send screen or screen.
X	For CICS, ISPF Dialog, or ISPF prototyping. XCTL. Transfer control to program

Reserved Function
or Function Name

Object of action coded in function field. Valid actions and their objects are:

Code	Function/Name
*	ADD\|ADD-ROW BACKWARD CLEAR DELETE\|DELETE-ROW FORWARD NEXT QUERY\|QUERY-ROW TERM *UPDATE\|UPDATE-ROW
$	macroname [arguments]
C	programname [userparms]
E	Invoke Specification Editor screen
G	stubname
L	Indicates local stub; value PAINTED or NOT PAINTED displays
M	programname [screenname\|screenname] [keywords]
P	paragraphname
S	screenname\|screenname [keywords]
X	programname [(NONAPS)] [userparms]

Specification Editor

Write local program stubs and paragraphs for user-defined functions and control point logic.

Access from:	Alternate Functions screen, enter E in the Function field.
	PF Key Functions screen, enter E in the Function field.
	Special Key Definition screen, enter E in the Option field.
	Control Points screen, enter E in the Action field.
	Database Call Tailoring screen, enter E in the Action field.
Transfer to:	Previous screen, press F3.

To code program stubs, follow these rules:

Do not code a PERFORM statement or PARA keyword on the first line; Online Express generates them.
Code the paragraph statements in S-COBOL, COBOL, or COBOL/2 code, beginning in column 12.
Paragraphs can be coded on subsequent lines; code a PERFORM statement and the PARA keyword (in the Kywd column) for each paragraph.
Data Division code may follow Procedure Division code.
Paragraphs performed at multiple control points should be written at one central location, MISC-USER-PARAGRAPHS, shown on the Control Points screen. MISC-USER-PARAGRAPHS is not a control point, but a location to store paragraphs performed at control points. This location provides access to the Specification Editor, where you write the paragraphs. When writing paragraphs at MISC-USER-PARAGRAPHS, code:
- Program Painter keyword in the Kywd column on the first line
- The PARA keyword for each paragraph

SQL Command Review

View the SQL that will be generated for a SQL call.

Access from:

Database Access Summary screen, enter V in the selection field.

Transfer to:

Database Access Summary screen, press F3.

SQL Exporter screen, enter SAVE in the Command field.

Command	Description
SAVE	Save the SQL and pertinent S-COBOL and access the SQL Exporter screen, where you can access SPUFI or XDBSQL and test execute the call.

SQL Functions

Apply SQL functions against columns. You can choose a function from a list of all SQL functions, to apply against columns in SELECTs or SUBSELECTs.

Access from:

Column Selection Update screen, enter F in the selection field.

Subselect Specification screen, enter S in the function selection field.

Transfer to:

Previous screen, press F3.

To select a function, type S in the selection field preceding that function. You may also supply additional arguments for the CHAR, DECIMAL, and SUBSTR functions.

SQL Having Clause Specification

Create a HAVING clause, which restricts the columns processed by a GROUP BY clause.

Access from:

Automatically displays when you specify GROUP BY syntax on the Column Selection Update screen.

Transfer to:

The next screen in the call definition, press F3.

Field

Description and Values

Operator

Value(s)

COBOL data name, variable, column name, or literal. To continue on subsequent a line, enter I on the current line, type OF on the new line in the Operator field, and enter a qualifier in this field.

Bool

AND or OR

SQL Prototype

Save the SQL generated by the call, or access SPUFI or XDBSQL to test execute the call.

Access from:

SQL Command Review screen, enter SAVE in the Command field.

Transfer to:

SQL Command Review screen, press F3.

Field

Description and Values

Option

1	Specify the target library in the fields provided.
2	Display Explain Plan Table screen to view the structure of the SQL call.
3	Invoke SPUFI to further test the SQL statement.

Target Dataset for Option 1

Information required to save the SQL.

Replace Like-Named Members

YES	Update existing member by replacing previous version.
NO	Display message the member exists.

Save Plan Table Entry

YES	Save.
NO	Do not save.

SQL Qualification Specification

Qualify the database accesses by specifying access qualifications for a record Obtain.

Access from:	Database Access Summary screen, enter Q in the selection field.
	Union Summary Menu screen, enter Q in the selection field.
Transfer to:	Column Information screen, enter ? in the selection field.
	Database Access Summary screen, press F3.
	Subselect Specification screen, enter S in the selection field.

Field

Description and Values

Selection Field

A	After
M	Move
B	Before
R	Repeat
D	Delete
S	Transfer to Subselect Specification
I	Insert
?	Transfer to Column Information

Parenthesis Field

Enter a left parenthesis to begin a group of qualification conditions. Enter a right parenthesis in the Boolean field on a subsequent line.

Corr

Table correlation name for joined tables. Default names are A through P. Specify your own names (maximum 4 characters) on the Correlation Names screen.

Operator

Value(s)

COBOL data name, variable, SQL column name, literal, or SUBSELECT on which to qualify field.

Boolean

Valid on two or more columns. Specify AND or OR in the field.

Subselect Specification

Specify a subselect clause for a column.

Note: Make sure that the column and record are specified in order to display the column list and to ensure that the SQL function you select is compatible with the data type of the column you select.

Access from:	SQL Qualification Specification screen, enter S in the selection field or SUBSELECT in Value(s) field.
Transfer to:	Column Selection screen, enter S in the column selection field.
	SQL Functions screen, enter S in the function selection field.
	SQL Qualification Specification screen, press F3.

Field	Description and Values
Function	Type a SQL function, or leave this field blank and enter S in the selection field to access the SQL Functions screen, where you can select the desired function.
Column	Type the column name, or leave blank and enter S in the selection field to access the Column Selection screen, where you can select the desired column. A ? in the selection field displays the Column Information screen with information about the column named in this field.
From Record	Enter S next to the record from which to specify a SUBSELECT.
Where Qualification	Specify access qualification based on the column entered and the condition outlined by the Oper and Value fields.
Column	Type the column name, or leave blank and enter S in the selection field to access the Column Selection screen where you can select the desired column.
Oper	=\|EQ Equal to [NOT] BETWEEN >\|GT Greater than [NOT] LIKE <\|LT Less than [NOT] NULL >=\|GE Greater than or equal to [NOT] IN <=\|LE Less than or equal to ^=\|NE Not equal to
Value	Specify value, if appropriate.

Note these general rules about SUBSELECTS:

Specify ALL columns only when the SUBSELECT forms an EXISTS clause.
JOINS are not currently supported.
Nested SUBSELECTS are not currently supported.

Union Summary Menu

The Union Summary Menu leads you through the definition and summary of any UNION clauses you specify in you application. This screen summarizes the SELECT statements that form UNION clauses you specify.

Access from:	Database Access Summary screen, enter U in the selection field.
Transfer to:	Database Access Summary screen, press F3.
	Database Record Selection screen, enter S in the selection field.
	SQL Qualification Specification screen, enter Q in the selection field.
	Column Selection screen, enter C in the selection field.
	Order By Columns screen, enter O in the Command field.

Fields

Description and Values

Union or Union All

UNION	Select and qualify the rows to be retrieved.
A	Retrieve all rows from the SELECT result tables

selection field

A	After
C	Transfer to Column Selection
B	Before
Q	Transfer to Qualification Specification
I	Insert
S	Transfer to Database Record Selection
M	Move

Literal Id

Identify which UNION statement was matched in retrieving a row. Enter a literal constant; 8-character maximum.

If you specify a mismatch of column data types or lengths, the Union Columns Cross Reference screen displays. To correct the mismatch, transfer to Column Selection Update (enter C in the selection field of Union Summary Menu ) and make the correction.

Observe the following general rules for UNIONs:

Data returns in the host variable(s) of the first statement.
APS uses first SELECT statement as the MAIN statement.
- If you select an uneven number of columns, the MAIN statement must contain the largest number of columns. If the first SELECT has more columns than subsequent SELECTs, APS pads the subsequent statements with literal strings or numeric values.
- APS validates the MAIN statement columns against columns from subsequent statements.
Online Express prompts you for each query step. To view or change the criteria, use the Line Commands in the selection field.

User Controlled Database Calls

You can assign database accesses to a function and execute it within custom code.

Access from:

Database Access Summary screen, enter *USER in the call Function field, and USER in the Command field.

Transfer to:

Database Access Summary screen, press F3.

Assign database accesses to function *USER and execute it as follows:

On the Database Access Summary screen, enter *USER in the Function field next to the desired call.
Enter *USER in the Command field. If desired, override the paragraph name on this screen.
Perform this paragraph.

Control Points

The control points that apply to your program vary from program to program, depending on which functions you define for the program. To execute logic at control points, you display the Control Points screen, which displays all control points that exist in your program. The complete list of control points is as follows:

Control Point	Location in Program
AFTER-RECEIVE-PARA	After entering a program, regardless of invocation mode.
POST-SCREEN-READ	After a screen-invoked program receives its screen.
TRANSID-INVOKED-PARA	After a transid-invoked program is invoked.
PROGRAM-INVOKED-PARA	When APS displays the screen of a program invoked by the XCTL or MSG-SW function.
PRE-TERM	Before APS terminates the program.
AFTER-ENTER-CHECK	After the end user presses the processing key (the Enter key is the default), and before the PRE-FUNCTION-TEST paragraph executes.
PRE-FUNCTION-TEST	Before APS evaluates all functions except the Terminate, or Exit, function.
PRE-BRANCH	Before each MSG-SW, XCTL, or Call function executes.
ED-ERROR-PRE-SEND	Before APS send a screen whose field edits have failed.
GENERAL-PRE-SEND	After APS checks all functions, and before the TP-SEND call executes, when invocation mode is screen-invoked.
BEFORE-SEND-PARA	Before APS sends the screen, regardless of invocation mode.
PRE-SCREEN-TO-REC	Before APS performs the MOVE-SCREEN-TO-REC paragraph.
POST-SCREEN-TO-REC	After APS performs the MOVE-SCREEN-TO-REC paragraph, and the Update or Add function executes.
PRE-REC-TO-SCREEN	Before APS performs the MOVE-REC-TO-SCREEN paragraph.
POST-REC-TO-SCREEN	After APS performs the MOVE-REC-TO-SCREEN paragraph, and after the Query function executes.
PRE-RB1-ROW-TO-REC	Before the Add or Update function executes for a repeated record block row, and before screen fields move to database fields. APS uses the subscript CTR to reference repeated block rows.
POST-RB1-ROW-TO-REC	Before the Add or Update function executes for a repeated record block row, and after screen fields move to database fields. APS uses the subscript CTR to reference repeated block rows.
PRE-REC-TO-RB1-ROW	After the Query or Forward function executes for a repeated record block row, and before database fields move to screen fields. APS uses the subscript CTR to reference repeated block rows.
POST-REC-TO-RB1-ROW	After the Query or Forward function executes for a repeated record block row, and after database fields move to screen fields. APS uses the subscipt CTR to reference repeated block rows.
ERROR-SEND-AND-QUIT	When a program terminates abnormally, such as when a database call fails when the Database Call Tailoring screen's Abort On Error parameter is set to y.
LISTBOX-SELECT	When the end user selects a list box item. Enter predefined functions such as QUERY, UPDATE, ADD, and DELETE.
MISC-USER-PARAGRAPHS	A location where you can write and store multiple paragraphs that you can perform at any control point in your program.

The following flowcharts illustrate the location of the control points in APS-generated programs.

You can also execute custom logic at any of several APS-provided database call control points, selected from the Database Call Tailoring screen. The database control points are as follows:

Control Point	Location
BEFORE DB ACCESS	Before a non-loop database call executes
BEFORE LOOP	Before a loop database call executes
NORMAL STATUS (BEFORE REC MAPPED TO SCREEN)	Before Online Express maps looped records to the screen
NORMAL STATUS	After Online Express maps any records to the screen
EXCEPTION STATUS	After the database call returns a status flag with the Exception status code
ERROR STATUS	After the database call returns a status flag with the Error status code
AFTER DB ACCESS	After a non-loop database call executes
AFTER LOOP	After a loop database call executes

The following flowchart illustrates the location of these control points in APS-generated programs.