Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Query Extended Range failure.

Posted on by 75

Hello, I'm having an issue with the extended query range syntax. I'm currently trying to generate a query that shows retail discounts based on a starting and ending date range.

The range looks like this.

qbdsStandard = SysQuery::findOrCreateDataSource(qStandard, tableNum(RetailPeriodicDiscount));
SysQuery::findOrCreateRange(qbdsStandard, fieldNum(RetailPeriodicDiscount, ValidFrom))
   .value(strFmt(
      @'(
         (!((RetailPeriodicDiscount.%1 < %3) && (RetailPeriodicDiscount.%2 > %4)))
      && (!((RetailPeriodicDiscount.%1 > %3) && (RetailPeriodicDiscount.%2 < %4)))
      && (!((RetailPeriodicDiscount.%1 > %3) && (RetailPeriodicDiscount.%1 > %4)))
      && (!((RetailPeriodicDiscount.%2 < %3) && (RetailPeriodicDiscount.%2 < %4)))
      )',
   fieldStr(RetailPeriodicDiscount, ValidFrom),
   fieldStr(RetailPeriodicDiscount, ValidTo),
   queryValue(startDate),
   queryValue(endDate)
));

They query will run fine, however an infolog message appears saying "Query extended range failure: The '!' operator can only be used with the 'like' keyword." The range is also ignored. However, if I call toString() on the query and conver the resulting X++ sql into "real" SQL the query and range work fine.

X++ SQL (except * is actually a list of all the fields)

SELECT *
FROM RetailPeriodicDiscount(RetailPeriodicDiscount_1)
WHERE ((DateValidationType = 1)) AND (((
(!((RetailPeriodicDiscount.ValidFrom < "3/21/2015") && (RetailPeriodicDiscount.ValidTo > "3/24/2015")))
&& (!((RetailPeriodicDiscount.ValidFrom > "3/21/2015") && (RetailPeriodicDiscount.ValidTo < "3/24/2015")))
&& (!((RetailPeriodicDiscount.ValidFrom > "3/21/2015") && (RetailPeriodicDiscount.ValidFrom > "3/24/2015")))
&& (!((RetailPeriodicDiscount.ValidTo < "3/21/2015") && (RetailPeriodicDiscount.ValidTo < "3/24/2015")))
)))
JOIN * FROM RetailPeriodicDiscountLine(RetailPeriodicDiscountLine_1)
ON RetailPeriodicDiscount.OfferId = RetailPeriodicDiscountLine.OfferId
JOIN * FROM RetailGroupMemberLine(RetailGroupMemberLine_1) ON RetailPeriodicDiscountLine.RetailGroupMemberLine = RetailGroupMemberLine.RecIdJOIN * FROM EcoResProduct(EcoResProduct_1) ON RetailGroupMemberLine.Product = EcoResProduct.RecId

I then convert it (by hand) into what I think would be the equivalent sql.

REAL SQL

SELECT *
FROM RetailPeriodicDiscount d

JOIN RetailPeriodicDiscountLine dl
ON d.OfferId = dl.OfferId
JOIN RetailGroupMemberLine gml
ON dl.RetailGroupMemberLine = gml.RecId
JOIN EcoResProduct p
ON gml.Product = p.RecId

WHERE ((d.DateValidationType = 1))
AND (((
(not((d.ValidFrom < '3/21/2015') and (d.ValidTo > '3/24/2015')))
and (not((d.ValidFrom > '3/21/2015') and (d.ValidTo < '3/24/2015')))
and (not((d.ValidFrom > '3/21/2015') and (d.ValidFrom > '3/24/2015')))
and (not((d.ValidTo < '3/21/2015') and (d.ValidTo < '3/24/2015')))
)))

Basically, the real sql executes correctly, and the correct rows are returned with the date ranges. However, the X++ sql just returns everything with absolutely no filtering. Is there a way to do this in X++? Am I overlooking something?


  • Martin Dráb Profile Picture
    Martin Dráb 224,528 Super User on at
    RE: Query Extended Range failure.

    You can do it - AX comes with a unit test framework out of the box.

  • RyanV Profile Picture
    RyanV 75 on at
    RE: Query Extended Range failure.

    Yep. It's kind of a pain because I have to dig out a pen and paper and make sure that all tests pass in my head at least. I guess this would be the perfect use case for unit testing in AX ;)

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 224,528 Super User on at
    RE: Query Extended Range failure.

    I did few tests and it seems that you really can't invert whole expressions (or at least I failed). Even if I copy a string generated by AX from a normal range and paste it to an extended range value, it fails.

    You'll have to negate operators (= → !=, < → >= etc.).

  • RyanV Profile Picture
    RyanV 75 on at
    RE: Query Extended Range failure.

    Thanks for the reply Martin.

    Unfortunately using the "not" keyword I'm getting "Query extended range failure: Syntax error near 0." It does not appear to like negations in boolean expressions. I'm going to re-write the expression without using !/not to achieve the same results, which is a little bit of a pain, but oh well.

    I also changed my code to use the .name() call for retrieving the datasource name.

  • Martin Dráb Profile Picture
    Martin Dráb 224,528 Super User on at
    RE: Query Extended Range failure.

    The extended query syntax is more a hack than a feature and you have to be vary careful regarding the syntax that AX internally uses when building query strings, otherwise you get into troubles. You won't get any compile-time control nor any reasonably diagnostics.

    I immediately see two errors:

    1. The operator you want to use is NOT (instead of !)
    2. Don't use the hard-coded data source name (RetailPeriodicDiscount) - it may be different (e.g. RetailPeriodicDiscount_1). Take the value from qbdsStandard.name().

Helpful resources

Quick Links

Can you answer this forum question?

You could make someone's day!

Community Newsletter - May 2024

Kudos to our community stars!

Community Spotlight of the Month

Kudos to Mohamed Amine Mahmoudi!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 283,632 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 224,528 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,146

Featured topics

Product updates

Dynamics 365 release plans