![]() |
Copyright © TIBCO Software Inc. All Rights Reserved |
Returns the first table occurrence that satisfies the selection criteria. Use if the value of every table parameter and every selection criterion is 99 or fewer characters long. (C)
A string specifying the table from which to select. Its syntax is C (fixed-length character string), with a length of 16. A string specifying the parameter set for the table. Its syntax is V (variable-length character string) with length of 512. A string specifying the selection criteria. Its syntax can be UN (Unicode) or V with a length of 1024. A string specifying the order in which to return the occurrences during selection. Its syntax is V, with a length of 512.
If the format of the arguments for FORALLA is incorrect, a fatal error can occur with no indication of the cause of the error.The name of the table. For example, to access the table EMPLOYEE, pass ‘EMPLOYEE’ as the value for the table argument.The string specified for parm consists of a list of parameter values. Each parameter value consists of a two-character length descriptor and a parameter value. For example, the string 06CANADA0580002 represents two parameters. The first is six characters long with the value CANADA. The second is five characters long with the value 80002.Both the data parameters and the location parameter can be specified in this manner. If the table has no parameters or the parameters are being specified in the selection parameter of FORALLA, the value of parm can be null (NULL).The string specified for selection is a postfix representation of the rules language WHERE clause. It is comprised of one or more terms joined by logical operators. Each term consists of the following:
• A two‑character descriptor consisting of a space followed by an R (' R').
• A 16 character field name. If the name is not 16 characters long, it must be padded to 16 characters with spaces.
• A two‑character value syntax descriptor consisting of a space followed by one of the following (for example, ' V') to denote the syntax of the value:
− A – syntax RD (raw data): FORALLA uses, or coerces the value to, syntax V; and then coerces it to syntax RD (see note below)
− E – syntax V (variable-length character string): FORALLA uses, or coerces the value to, syntax V; if the coercion to V fails, it raises an exception
− M – a numeric string: FORALLA uses, or coerces the string to, syntax V; and then coerces it to syntax B (binary), P (packed), or F (floating point); if either coercion fails, it raises an exception
− V – syntax UN or V. FORALLA uses, or coerces the value to, syntax V; if the coercion to syntax V fails, it uses syntax UN
The relational operators must be two characters in length. Prefix a space before an operator that consists of only one character.
The arithmetic operators must be two characters in length. Prefix with a space an operator that consists of only one character. For unary minus, place a space after the -.
The syntax of selection determines the representation of values in the expression. The usual technique for building selection is to use a series of concatenations. Here are some examples (where the local variable VAL contains the value in the expression to be built):If VAL is of syntax V, SEL_STR preserves its syntax; if VAL is of syntax UN, SEL_STR is coerced to syntax UN.Regardless of selection's and VAL's syntax, FORALLA coerces the value to syntax V.Regardless of selection's and VAL's syntax, FORALLA coerces the value to syntax B, P, or F, whichever is appropriate.
To include a raw-data value in the expression, use descriptor ' A', keeping in mind that a direct concatenation involving an RD value coerces the result to RD. If, as a result, the selection string is of syntax RD, FORALLA coerces it to syntax V prior to any processing, so some data loss is possible (for example, if the selection contains a value of syntax UN). Accordingly, the following is recommended: assuming VAL is of syntax RD, first cast it to syntax V:Even if SEL_STR is of syntax UN prior to the latter action, FORALLA coerces correctly the value to RD.In these examples, the “ß” represents a mandatory space.
• The following represents the selection CITY='TORONTO':The pattern is the name of the field, followed by the value, followed by the relational operator.
• The following represents the selection CITY='TORONTO' AND SALARY > 600:The field name (CITY) is followed by the value (TORONTO) and relational operator (=), and then another sequence of field (SALARY), value (600), and relational operator (>), followed by the logical operator (&) that joins the two expressions.
• ßRCITYßßßßßßßßßßßßßV07TORONTOß=ßRSALARYßßßßßßßßßßßM03600ß>ß&ßRMGR#ßßßßßßßßßßßßßM0580002ß=ß|The first and second expressions are the same, and are followed by a third sequence of field (MGR#), value (80002), relational operator (=), and a logical operator (|) that joins the third expression to the selection string.If selection on a table is not required, you can pass an empty value ('') for the selection parameter.
• A 16-character field name. If the field name is less than 16 characters long, it must be padded to 16 characters.
• A one-character ordering specifier. The ordering specifier is A for ascending and D for descending.The ORDERED clause ORDERED DESCENDING LNAME AND ORDERED ASCENDING EMPNO would translate to an ordering parameter like this:LNAMEßßßßßßßßßßDEMPNOßßßßßßßßßßßAIf you do not need ordering, you can pass an empty value ('') for the ordering parameter. In this case, the occurrences are sorted by primary key.
• If the value of any table parameter or of the selection criterion is 100 or more characters long, use @FORALLA rather than FORALLA.
• Using FORALLA in conjunction with FORALLB and FORALLE, you can dynamically build a selection criterion to retrieve table occurrences.
•
• There are a maximum of 16 tables per transaction that can be actively accessed with FORALLA. When you are finished accessing a table, you should call FORALLE to indicate that you are finished and to free up resources for another table to be accessed.
• The PARSE_TAM tool can be used to create the necessary arguments for FORALLA, or if you only need to build the selection argument you can use the SIMPLESELECT tool.
A FORALLA within a FORALL on the same table (and with the same parameter values) can result in unexpected behavior because the set of occurrences returned by the outer FORALL is replaced by the occurrence returned by FORALLA.
Using the following rule, QUERY_EMPS, users can construct a query against the EMPLOYEE table. The rule retrieves employee names based on the selection criteria associated with one field, or with a combination of two fields, and prints them to the message log. Users can execute the rule directly or the rule could be part of a larger application in which input values are entered via a screen.QUERY_EMPS pads input data to appropriate lengths, constructs a FORALLA statement using the data from one or two fields, and then uses FORALLB, FORALLE, and MSGLOG to retrieve and display the names of all employees meeting the selection criteria in the FORALLA statement.
To find those employees who work for manager number 79912 and have a salary of more than $700.00, the following input values are supplied:
![]() |
Copyright © TIBCO Software Inc. All Rights Reserved |