Internal Functions

Reference:

The following table lists and describes internal functions executable as ACTIVATE routines in the rules.

Function

Description

%CLOSE

Closes a file defined with DYNAM or FILEDEF.

%COLUMNS

Returns column names.

%CONCAT

Concatenates two strings into a resultant string value.

%DATE

Creates a formatted date string value.

%DISTINCT

Returns if DISTINCT columns were requested.

%DOW

Creates a day string value from a numeric indicator.

%DYNAM

Uses the DYNAM command for file manipulation in MVS.

%FILEDEF

Uses the FILEDEF system command for reserving, deleting, or modifying files on systems other than MVS.

%FTOA

Converts a NUMERIC fact value to a STRING.

%FUNCOLS

Returns functions with column names.

%FUNCTION

Returns function names.

%GET

Reads a line from a file defined with DYNAM or FILEDEF.

%GROUPBY

Returns group names.

%LEFTREL

Returns relational columns (left side).

%LENGTH

Returns the length of a string value.

%LOCATE

Locates the existence of a string within a string value and returns the position.

%LOWCASE

Sets a string value to lower case.

%MONTH

Creates a month string value.

%ORDERBY

Returns ordered columns.

%ORNOTERR

Invalid combination of OR and NOT logic.

%PUT

Writes a record to a file defined with DYNAM or FILEDEF.

%RELATION

Returns the relational statements.

%RELCNT

Returns the number of times a column name is used in a relational clause.

%RELOPCOL

Returns the relational column/operator combinations and determines whether they compare against a literal or a field.

%RELOPS

Returns relational operator AND/OR with LITERAL or FIELD.

%RELTABS

Returns relational conditions between tables.

%REQUEST

Writes the current command request being processed to a file.

%RIGHTREL

Returns relational columns (right side).

%SUBSTR

Picks out a section of a string value with starting and ending numeric positions and puts that section into a resultant string value.

%TABLES

Returns table names.

%TIME

Creates a formatted time string value.

%TRIM

Trims blanks from the beginning, end, or both from a string value.

%TRUNC

Truncates a string at the desired position.

%UPCASE

Sets a string value to upper case.

Examples in the following function descriptions are based on this sample SQL request:

SELECT T1.COLUMN1,T2.COLUMN2,AVG(T2.COLUMN4),MAX(T2.COLUMN5)
FROM CREATOR.TABLE1 T1,CREATOR.TABLE2 T2
WHERE (T1.INDEX1 = T2.INDEX2) AND (T1.COLUMN3 > '19951231')
GROUP BY T1.COLUMN1,T2.COLUMN2
ORDER BY T1.COLUMN1,T2.COLUMN2;


x
Reference: %CLOSE

The %CLOSE function closes a file defined with DYNAM or FILEDEF.

Use:

RULE close the file
IF read record
OR write record
THEN DBA Rules Concluded
AND ACTIVATE %CLOSE
AND DATA DDN 1

Top of page

x
Reference: %COLUMNS

The %COLUMNS function returns all the column names specified in the SELECT clause.

Use:

RULE Get column names
ACTIVATE %COLUMNS
DATA Column name IS WHAT
THEN Column names retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Column name IS CREATOR.TABLE1.COLUMN1
Column name IS CREATOR.TABLE2.COLUMN2

Top of page

x
Reference: %CONCAT

The %CONCAT function concatenates two strings into a resultant string value.

Use:

RULE concat strings
ACTIVATE %CONCAT
! Concatenates the second string to the first
DATA Record
DATA Extra String

Top of page

x
Reference: %DATE

The %DATE function creates a formatted date string value of xx/yy/zz where xx, yy, or zz can be any numbers chosen but are passed in as strings. For example, Message1, 2, and 3 may have been initialed using %SUBSTR from Current Date (yyyymmdd).

RULE make a date
ACTIVATE %DATE
DATA Message1
DATA Message2
DATA Message3

Top of page

x
Reference: %DISTINCT

The %DISTINCT function returns true if DISTINCT is specified in the SELECT clause; otherwise, it returns false.

Use:

RULE Get distinct
ACTIVATE %DISTINCT
DATA Distinct columns
THEN Distinct determined

Based on the sample SQL, the SIMPLEFACT Distinct columns are false. Distinct columns would be true if the sample had been:

SELECT DISTINCT T1.COLUMN1,...

Top of page

x
Reference: %DOW

The %DOW function creates a day string value from a numeric indicator. The Day of Week NUMERIC value passed into business rules is a number from 0 through 6, Sunday through Saturday.

Use:

RULE day name
ACTIVATE %DOW
DATA Day of Week
DATA Extra String

Top of page

x
Reference: %DYNAM

The %DYNAM function is used to manipulate files in MVS.

Use:

RULE Allocate The CNTLFILE
THEN Allocate The CNTLFILE
AND DDN 1 := "CNTLFILE"
AND Dynam Data := "FILE CNTLFILE"
AND Dynam Data 1 := "DA SYS1.MY.CNTLFILE SHR REU"
AND ACTIVATE %DYNAM
AND DATA Dynam Data
AND DATA Dynam Data 1

Top of page

x
Reference: %FILEDEF

The %FILEDEF function is equivalent to using the %DYNAM command and uses the FILEDEF system command for reserving, deleting, or modifying files on systems other than MVS.

Use:

RULE Allocate The CNTLFILE
THEN Allocate The CNTLFILE
AND DDN 1 := "CNTLFILE"
AND Dynam Data 1 := "DISK C:\\CNTLFILE.TXT"
AND ACTIVATE %FILEDEF
AND DATA DDN 1
AND DATA Dynam Data 1

Top of page

x
Reference: %FTOA

The %FTOA function converts a NUMERIC fact value to a STRING.

Use:

RULE convert to alpha
ACTIVATE %FTOA
! The resulting string
DATA Extra String
! The length of the string
DATA Lineptr
! The number to be converted
DATA Extra Number

Top of page

x
Reference: %FUNCOLS

The %FUNCOLS function returns all the functions with column names specified in the SELECT clause.

Use:

RULE Get functions with columns
ACTIVATE %FUNCOLS
DATA Function column IS WHAT
THEN Functions with columns retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Function column IS AVG.CREATOR.TABLE2.COLUMN4
Function column IS MAX.CREATOR.TABLE2.COLUMN5

Top of page

x
Reference: %FUNCTION

The %FUNCTION function returns all the function names specified in the SELECT clause.

Use:

RULE Get functions
ACTIVATE %FUNCTION
DATA Function name IS WHAT
THEN Functions retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Function name IS AVG
Function name IS MAX

Top of page

x
Reference: %GET

The %GET function reads a line from a file defined with DYNAM or FILEDEF.

Use:

RULE read a record
ACTIVATE %GET
DATA DDN 1
DATA Record
DATA ioresult
IF ioresult = 0
THEN read record

Top of page

x
Reference: %GROUPBY

The %GROUPBY function returns all the column names specified in the GROUP BY clause.

Use:

RULE Get group bys
ACTIVATE %GROUPBY
DATA Group by column IS WHAT
THEN Group bys retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Group by column IS CREATOR.TABLE1.COLUMN1
Group by column IS CREATOR.TABLE2.COLUMN2

Top of page

x
Reference: %LEFTREL

The %LEFTREL function returns the left-hand side of the relational condition as specified in the WHERE clause.

Use:

RULE Get left relational column 
ACTIVATE %LEFTREL
DATA Left relational column IS WHAT
THEN Left relational column retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Left relational column IS CREATOR.TABLE1.INDEX1
Left relational column IS CREATOR.TABLE1.COLUMN3

Top of page

x
Reference: %LENGTH

The %LENGTH function returns the length of a string value.

RULE find string length
ACTIVATE %LENGTH
DATA Extra Number  ! contains the number of characters in Record
DATA Record

Top of page

x
Reference: %LOCATE

The %LOCATE function locates the existence of a string within a string value and returns the position.

Use:

RULE locate a string
ACTIVATE %LOCATE
Tablename 1 := "Mercedes Benz"
Tablename 2 := "Benz"
DATA Tablename 1
DATA Tablename 2
DATA Extra Number  ! this will contain the number 10 to indicate position

Top of page

x
Reference: %LOWCASE

The %LOWCASE function sets a string value to lower case.

Use:

RULE lower case
Extra String := "case change"
ACTIVATE %LOWCASE
DATA Extra String

Top of page

x
Reference: %MONTH

The %MONTH function creates a month string value from a numeric indicator 0 through 12, January through December.

Use:

RULE month name
ACTIVATE %MONTH
DATA Extra Number
DATA Extra String

Top of page

x
Reference: %ORDERBY

The %ORDERBY function returns all the column names specified in the ORDER BY clause.

Use:

RULE Get group bys
ACTIVATE %ORDERBY
DATA Order by column IS WHAT
THEN Order bys retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Order by column IS CREATOR.TABLE1.COLUMN1
Order by column IS CREATOR.TABLE2.COLUMN2

Top of page

x
Reference: %ORNOTERR

The %ORNOTERR function returns true if a faulty OR/NOT condition is specified in a WHERE clause.

Use:

RULE Determine faulty relational condition
ACTIVATE %ORNOTERR
DATA Bad or not condition
THEN Faulty relational determined
SELECT T1.COLUMN1
FROM CREATOR.TABLE1 T1
WHERE (NOT T1.COLUMN9 = 'RED' OR NOT T1.COLUMN9 = 'BLUE');

Based on the sample SQL, the SIMPLEFACT Bad or not condition are false. If the sample had been

SELECT T1.COLUMN1
FROM CREATOR.TABLE1 T1
WHERE (NOT T1.COLUMN9 = 'RED' OR NOT T1.COLUMN9 = 'BLUE');

Then, when COLUMN9 is RED, it is not BLUE, so the OR makes the condition true; and when COLUMN9 is BLUE, it is not RED, so the OR still makes the condition true; and when COLUMN9 is neither RED nor BLUE, the condition is still true. The effect is the same as coding SELECT T1.COLUMN1 without a WHERE clause.

In this case, the Bad or not condition is true.


Top of page

x
Reference: %PUT

The %PUT function writes a record to a file defined with DYNAM or FILEDEF.

Use:

RULE write a record
ACTIVATE %PUT
DATA DDN 1
DATA Record
DATA ioresult
IF ioresult = 0
THEN write record

Top of page

x
Reference: %RELATION

The %RELATION function returns all the relational statements specified in the WHERE clause.

Use:

RULE Get relational statements
ACTIVATE %RELATION
DATA Relational statement IS WHAT
THEN Relational statements retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Relational statement IS CREATOR.TABLE1.INDEX1.EQ.CREATOR.TABLE2.INDEX2
Relational statement IS CREATOR.TABLE1.COLUMN3.GT.19951231

Top of page

x
Reference: %RELCNT

The %RELCNT function returns the number of times a column name is used in a relational clause.

Use:

RULE count column use
ACTIVATE %RELCNT
DATA Record
DATA Extra Number

Top of page

x
Reference: %RELOPCOL

The %RELOPCOL function returns all the relational column/operator combinations and determines whether they compare against a literal or a field as specified in the WHERE clause.

Use:

RULE Get relational column and operator
ACTIVATE %RELOPCOL
DATA Relational IS WHAT
THEN Relational column and operator retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Relational IS CREATOR.TABLE1.INDEX1.EQ.FIELD
Relational IS CREATOR.TABLE1.COLUMN3.GT.LITERAL

Top of page

x
Reference: %RELOPS

The %RELOPS function returns all the relational operators and determines whether they compare against a literal or a field as specified in the WHERE clause.

Use:

RULE Get relational operator
ACTIVATE %RELOPS
DATA Relational operator IS WHAT
THEN Relational operator retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Relational operator IS EQ.FIELD
Relational operator IS GT.LITERAL

Top of page

x
Reference: %RELTABS

The %RELTABS function returns all the relational conditions between tables as specified in the WHERE clause.

Use:

RULE Get relational table conditions
ACTIVATE %RELTABS
DATA Table relations ARE WHAT
THEN Relational table conditions retrieved

Based on the sample SQL, the following attribute-value pair are true:

Relational statement IS CREATOR.TABLE1.EQ.CREATOR.TABLE2

Top of page

x
Reference: %REQUEST

The %REQUEST function writes the current command request being processed to a file.

Use:

RULE put request in a file
! this assumes that DDN 1 is a file that has been previously defined
ACTIVATE %REQUEST
DATA DDN 1

Top of page

x
Reference: %RIGHTREL

The %RIGHTREL function returns the right-hand side of the relational condition when it references a field as specified in the WHERE clause.

Use:

RULE Get right relational column 
ACTIVATE %RIGHTREL
DATA Right relational column IS WHAT
THEN Right relational column retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Right relational column IS CREATOR.TABLE2.INDEX2

Top of page

x
Reference: %SUBSTR

The %SUBSTR function picks out a section of a string value with starting and ending numeric positions and puts that section into a resultant string value.


Top of page

x
Reference: %TABLES

The %TABLES function returns all the table names specified in the FROM clause.

Use:

RULE Get table names
ACTIVATE %TABLES
DATA Table IS WHAT
THEN table names retrieved

Based on the sample SQL, the following attribute-value pairs are true:

Table IS CREATOR.TABLE1
Table IS CREATOR.TABLE2

Top of page

x
Reference: %TIME

The %TIME function creates a formatted time string value of xx:yy where xx or yy can be any character values chosen. For example, Message1 and 2 may have been initialized using %SUBSTR from Day of Week.

Use:

RULE make time
ACTIVATE %TIME
DATA Message1
DATA Message2

Top of page

x
Reference: %TRIM

The %TRIM function trims blanks from the beginning, end, or both from a string value.

Use:

RULE trim blanks
ACTIVATE %TRIM
DATA Tablename 1
DATA right

Top of page

x
Reference: %TRUNC

The %TRUNC function truncates a string at the desired position.

Use:

RULE truncate
! Make Extra String the year
Extra String := Current Date
Lineptr := 5       ! Ends the current date in the fifth position
ACTIVATE %TRUNC
DATA Extra String
DATA Lineptr

Top of page

x
Reference: %UPCASE

The %UPCASE function sets a string value to upper case.

Use:

RULE upper case
Extra String := "case change"
ACTIVATE %UPCASE
DATA Extra String

WebFOCUS