|
|
|
Following are comments on each section of the scan syntax.
Is the initial part of the syntax. For compatibility with SQL, an optional SELECT clause is specified. If desired, the FROM sub-clause can specify an overriding correlation ID for this expression, that is used on subselects to form correlated queries. The NDB name is assumed in the following situations:
Note: ndbname must be the current NDB name.
Following the optional SELECT clause, a scan expression must be specified, represented by EXP1.
correl-id must be from 1 to 8 characters, in PDSNAME format. The ID does not need to be unique. It cannot be the value WHERE. The value '*' is also acceptable, meaning 'current NDB'.
Is the top level of an expression or parenthesized part of an expression. The OR (|) connector connects any number of EXP2 sub-expressions.
Is the level of expression or parenthesized part of a sub-expression that allows the connection of any number of EXP3 sub-expressions by use of the AND connector. An ampersand (&) can also represent AND.
Note: AND binds tighter than OR.
Is the part of the syntax that shows the NOT connector used to negate parts of the expression. Two adjacent NOT connectors cancel each other out. A NOT sign (¬) represents NOT.
Is the part of the syntax that shows a parenthesized expression used to override precedence rules. The depth of parenthesis nesting has no limit.
Is the part of the syntax that shows that a test is one of ten varieties. These tests are described next.
The optional IGNORE clause allows you to ignore the test completely and treat it as true (IGNORE TRUE) or false (IGNORE FALSE). This feature is useful when the IGNORE clause is inserted dynamically into a complex scan expression.
Note: The IGNORE clause is only supported if the quoted data option is in effect.
Feeds in the result list of a previous &NDBSCAN as a list of matching records.
Tests whether a field (or list of fields) is PRESENT in a record. See L-LIST for specification of the field list.
Tests whether a field (or list of fields) is ABSENT (that is, not present) in a record.
Tests a field for presence (IS NOT NULL) or absence (IS NULL) in a record. It is the ANSI SQL version of the presently existing PRESENT and ABSENT operators. No field lists are supported, but the FIELDS keyword is supported.
Tests a field for (not) being within the (inclusive) range of two values. It is the ANSI SQL version of the presently supported field = value : value syntax. Only a single field is supported (no lists or ranges). Similarly, no lists or ranges are allowed. The FIELDS keyword is supported.
Tests for a nonempty set of records that pass a subselect. This test always evaluates to TRUE (at least one record) or FALSE (no records).
Tests for set membership. The format where a list of values is supplied, is equivalent to the presently supported list of values for the equal (=) or not equal (¬=) operators. The format where a subselect is used is new. No field name lists are supported, but the FIELDS keyword is supported.
Note: If the quoted data option is not in effect, a subselect is not recognized here. The SELECT keyword could be a valid data value for a character field.
Is the pattern match test, with NOT LIKE list capabilities. LIKE uses the ANSI SQL pattern match wildcards:
Matches 0 or more characters.
Matches exactly one present character.
Note: The R-LIST supports correlated field references and is supported for the [NOT] LIKE operators.
Is the CONTAINS operator. The operator cannot have a subselect on the right.
Note: R-LIST correlated field references are acceptable.
The standard relational operator syntax. NDB allows list of fields on the left, and lists of either fields or values on the right.
If a subselect is specified, then no left side lists are permitted.
Note: The SOME keyword is only permitted and recognized if the quoted data option is in effect.
Is the nested selection syntax. The values of the nominated fields of the records that pass the scan expression are used in the containing scan expression. The EXISTS test is an exception, where the fact that at least one record passes the subselect is the only thing that matters. The nominated fields must be type-compatible with the other fields in the containing subselect expression.
The FROM clause is required, and an optional correlation ID (correl-id) is specified, overriding the default of the NDB name. Correlation IDs do not need to be unique, but should be.
Is a list of fields to test. Lists of fields can include field name ranges or generic prefixes. With the exception of the PRESENT and ABSENT operators, all listed fields must be of the same type. The new ANSI SQL compatible operators do not support lists of fields. (Although they do support the FIELDS keyword, so that an unambiguous test is defined if you have a fieldname of FIELDS).
Each range or generic specification must match at least one field on the NDB. Overlapping ranges or generic specifications are allowed. The duplicate fields are ignored and the resulting internal list has each field only once.
Is a list of fields or values to test. Lists of values can include value ranges or generic specifications (for the equal (=) or not equal (¬=) operators only). Lists of fields can include (for numeric and DATE format fields only) an optional adjustment value. Lists are not supported for the ANSI SQL operators.
If an optional correl-id is inserted before a field list, then, if the specified correl-id is not the same as the containing select/subselect assigned correl-id, the test is a correlated test. The correl-id applies to all fields in the following field list. The ID can only be specified once per R-LIST, immediately after the FIELDS keyword. In this case, no adjustment value is allowed (the PLUS/MINUS clause).
ANY is the default for all operators except the ALL NOT EQUAL (p=) operator, which defaults to ALL.
| Copyright © 2009 CA. All rights reserved. |
|