Operational Differences between MultiValue and Caché
Query Language Differences
There are several differences in how queries are interpreted and executed in Caché. They are detailed in this section.
Caché MV supports all the variants of MV query syntax and auto configures itself for the syntax implied by the emulation of the account from whence the query is issued. The emulation flag NO_PRINT_LIMITERS is predefined for all the emulation styles available to Caché MV-enabled accounts/namespaces (via the CEMU command) and affects whether Pick style print limiters are allowed in queries.
A print limiter does not affect the records selected from a file, only whether the value of a particular output column is displayed or not. In traditional Pick mode, this means that a listing such as:
means list all the items in the MD file, displaying the DICT entry called F1, but only print the values of F1 if they are equal to the literal "K".
In Prime/UniVerse accounts however, this same query means something completely different because print limit is not a supported concept. In accounts requiring Prime/UniVerse style queries, the same command means:
List the item in the file MD whose item ID is "K", list the value(s) of DICT entry F1.
CMQL statements default the AND or OR operator according to the settings of the emulation for the current account. Accounts operating in UniVerse or PRIME modes will cause CMQL statements that omit AND or OR to default to AND. Accounts operating in PICK mode will cause AND or OR omissions to default to OR. Thus the statement:
LIST VOC WITH F1 = "K" F1 WITH F3 = "C"
will be interpreted in UniVerse or PRIME modes as:
LIST VOC WITH F1 = "K" AND F1 WITH F3 = "C"
while in a Pick style account, it will be understood as:
LIST VOC WITH F1 = "K" OR F1 WITH F3 = "C"
Because of the emulation-dependent nature of the interpretation, InterSystems recommends that developers be explicit with the query syntax and put in the AND and OR everywhere, rather than default them.
are ALWAYS assumed to have OR clauses in ALL emulation modes.
The use of the traditional wild card characters in a CMQL query is dependent on the emulation in effect at the time the query is issued.
If an emulation such as PICK, jBASE, R83, and so on is in effect, then the use of [ ] and ^ in a comparison string is allowed. However in Prime/UniVerse style emulations, they are not allowed and have no significant meaning.
The LIKE and UNLIKE clauses are always allowed in all emulations.
When issuing a CMQL query, some emulations permitted the USING clause before the filename, for example:
LIST USING DICT ANOTHER.FILE CUSTOMER NAME, ADDRESS
In Caché, all emulations require any USING clause to follow the data file name, as in
LIST CUSTOMER USING DICT ANOTHER.FILE NAME, ADDRESS
CMQL statements can be directed to other than the default printer channel using the syntax:
where nnn is optional and 0 or absence indicates the default channel.