CMQL to access cross reference. Converting an access statement to CMQL.

[Rich Brim]

Has anyone published a guide to assist in learning CMQL as compared to
access?

For instance.

select orders with status or with enterdate > "01/01/2012" and with
enterdate <= "05/01/2012" or with balance > "0" and with enterdate >
"01/01/2011" and with letter = "N"

How would this look in CMQL?

Rich

[John Lambert]

It should look the same but there may be something wrong when you have
multiple terms like that. Please report it to WRC and we can investigate

Sent from my iPhone

> --
> You received this message because you are subscribed to the Google Groups "InterSystems: MV Community" group.
> To post to this group, send email to Cac...@googlegroups.com
> To unsubscribe from this group, send email to CacheMV-u...@googlegroups.com
> For more options, visit this group at http://groups.google.com/group/CacheMV?hl=en
>

[Michael Cohen]

Hello Rich,

I am from InterSystems Worldwide Support Center and would be happy to help investigate this. If the following does not help, please email details to sup...@intersystems.com and mention my name. Please indicate the platform you are migrating from. I know there can be different defaulting if AND or OR is omitted, but that does not seem to be the case here. I suspect that you have an expectation for the precedence of AND vs OR, or WITH.

Caché supports migrations from various vendors. Our intent is to work as expected. For many differences, we can support both styles. But in many cases, we need to know what you are expecting. To tell us, you can set your account's emulation with our TCL command CEMU (with no arg it returns the current setting).

For example, with the default CEMU CACHE, we behave like universe, so
LIST VOC WITH F1 LIKE K...
returns the Keywords, but
LIST VOC WITH F1 K]
returns nothing.

But, after executing the command
CEMU JBASE
LIST VOC WITH F1 K]
does return the Keywords (and we added support for LIKE as well).

Note that this CEMU setting persists until you change it. Our documentation lists the available CEMU options.

Regarding your issue, our default is universe style, which processes ANDs and ORs left to right, while CEMU jbase gives AND a higher precedence than OR.

For example, the query
LIST MD F1 WITH @ID LIKE V... OR WITH F1 LIKE K... AND WITH @ID LIKE A...
returns 18 items for CEMU JBASE
(3 V IDs plus 15 A keywords)
because the precedence of AND causes this to be interpreted as
LIST MD F1 WITH @ID LIKE V... OR WITH (F1 LIKE K... AND @ID LIKE A...)

while with CEMU CACHE, this returns 15 items
(just the 15 A keywords)
because left-to-right interprets this as
LIST MD F1 WITH (@ID LIKE V... OR F1 LIKE K...) AND WITH @ID LIKE A...

If this does not resolve this question, perhaps you could provide a simpler example with any required DICTs, with the results from your system (please identify) and Caché.

I should also point out that we convert cmql queries into SQL. We will display the SQL with the parentheses so you can see how your query is interpreted by appending the (Z option to the end of your query. That is how I determined the above results.

Although it is not necessary, my general suggestion, for both access and SQL queries that include a mix of AND and OR, is to use parentheses (and the required WITHs) to ensure that you get the result you expect.

Michael Cohen

[Rich Brim]

I am trying to keep the emulation as Cache.

Here is a simple example.

ACCT:SELECT ORDERS WITH ACTIONCODE

58 Items selected to list #0

ACCT:SELECT ORDERS WITH ACTIONCODE AND WITH SENDDATE

[401] No items present.
ACCT:SSELECT ORDERS WITH ACTIONCODE OR WITH ACTIONCODE AND WITH
SENDDATE

[401] No items present.

this last select would select 58 items in d3 and universe. So, I need
how to use parentheses to make this work in Cache emulation.

Rich

[Ed Clark]

On Mar 20, 2012, at 9:30 PM, Rich Brim wrote:

>
> I am trying to keep the emulation as Cache.

Is there a particular reason why you want to use Cache emulation? There are significant behavioural differences between emulations. What platform are you coming from?

>
> Here is a simple example.
>
> ACCT:SELECT ORDERS WITH ACTIONCODE
>
> 58 Items selected to list #0
>
> ACCT:SELECT ORDERS WITH ACTIONCODE AND WITH SENDDATE
>
> [401] No items present.
> ACCT:SSELECT ORDERS WITH ACTIONCODE OR WITH ACTIONCODE AND WITH
> SENDDATE
>
> [401] No items present.
>
> this last select would select 58 items in d3 and universe. So, I need

universe itself has "flavors" which change behavior. What you describe is how universe "pick" flavor works, but probably not how universe "information" flavor works.

> how to use parentheses to make this work in Cache emulation.
>
> Rich
>

[Michael Cohen]

Rich,

CEMU CACHE behaves the same as universe (I ran this example there to confirm) and treats AND and OR in left-to-right order. jbase, reality and others give AND precedence and produce the results you seem to expect (as does Caché with those CEMU settings). Perhaps you are testing universe with a setting other than IDEAL.

In any case, if you want to stay with CEMU CACHE, the last step of the following Caché example illustrates how you might add parentheses to control the result.

Here I created a file ORDERS with fields ACTIONCODE AND SENDDATE, and added data with the various combinations of existing and missing values. I believe your goal is to see record 2 that has ACTIONCODE but not SENDDATE. Adding parentheses gives this result for both Caché with CEMU CACHE and for universe with IDEAL.

MV:LIST DICT ORDERS

LIST DICT ORDERS
Field............ CODE KEY.CODE CONV. FORMAT DISPLAY NAME SM ASSOC.....
Name

ACTIONCODE D 1 10L S
SENDDATE D 2 D4/ 10R S
@ID D 0 10L ORDERS S

3 Items listed.
MV:LIST ORDER ACTIONCODE SENDDATE

LIST ORDERS ACTIONCODE SENDDATE
ORDERS.... ACTIONCODE SENDDATE..

1 A 05/18/1995
2 B
3 11/07/2000
4

4 Items listed.
MV:
MV:LIST ORDERS ACTIONCODE SENDDATE WITH ACTIONCODE

LIST ORDERS ACTIONCODE SENDDATE WITH ACTI...
ORDERS.... ACTIONCODE SENDDATE..

1 A 05/18/1995
2 B

2 Items listed.
MV: MV:LIST ORDERS ACTIONCODE SENDDATE WITH ACTIONCODE OR WITH ACTIONCODE AND WITH SENDDATE

LIST ORDERS ACTIONCODE SENDDATE WITH ACTI...
ORDERS.... ACTIONCODE SENDDATE..

1 A 05/18/1995

One item listed.
MV:
MV:
MV:LIST ORDERS ACTIONCODE SENDDATE WITH ACTIONCODE OR WITH (ACTIONCODE AND SENDDATE)

LIST ORDERS ACTIONCODE SENDDATE WITH ACTI...
ORDERS.... ACTIONCODE SENDDATE..

1 A 05/18/1995
2 B

2 Items listed.
MV:

Feel free to contact me here or via Support if there is any more I can do to help you with this.

Michael Cohen