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;
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
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
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
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
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,...
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
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
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
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
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
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
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
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
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
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
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
The %LOWCASE function sets a string value to lower case.
Use:
RULE lower case Extra String := "case change" ACTIVATE %LOWCASE DATA Extra String
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
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
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.
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
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
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
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
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
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
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
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
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.
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
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
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
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
The %UPCASE function sets a string value to upper case.
Use:
RULE upper case Extra String := "case change" ACTIVATE %UPCASE DATA Extra String
WebFOCUS |