When developers think about queries, they think the queries are limited for the most part because the combined ranges are all AND conditions. Not so.
A recent requirement related to the CustOpenInvoicesListPage query surfaced. I needed to add an additional range on the custtrans data source for the field transtype. After going through the standard development practices and taking a look at all of the points of modification, I was befuddled by what I saw: multiple occurrences of the transtype field.
At first I didn’t think running the query would produce any records unless somehow the query was recreated in code at runtime. I searched for code that wasn’t there. A colleague gave me the idea to hover over the data source to get a look at the SQL statement. And there it was, right in front of me … (see Figure 1) Multiple ORs surrounding all of the transtype field conditions then an AND for the additional field not named transtype.
Figure 1: Shows the where clause when hovering over the custtrans data source
I recreated the same scenario on the SalesTable as a datasource to a junk query. I added the range for payment twice, and then added one for another field. This also created an OR condition in the AOT query (see Figure 2). I have been involved with AX since it was called Axapta 3.0. This is the first time I have seen an AOT query with any type of OR condition not inside a special expression. I was a little surprised.
Figure 2: Shows the select statement when hovering over the data source in the junk query with only two fields added