<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=259446424624044&amp;ev=PageView&amp;noscript=1">

Fullscope Blog

A real-world look at digital transformation.

New AX Tip: Queries – Not Really All or Nothing

By Jamie Bracewell | June 28, 2016

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.

figure1

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.

Figure2

Figure 2: Shows the select statement when hovering over the data source in the junk query with only two fields added

 Have you had a similar “a ha” AX moment? Share it here or shoot me an email rick.scruggs@fullscope.com.

Comments

Latest Posts