Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / complex ranges in dyna...
Microsoft Dynamics AX (Archived)

complex ranges in dynamic X++ queries

(0) ShareShare
ReportReport
Posted on by dark_knight 768

Hello,

 I'm trying to rewrite the following SQL code to dynamic X++ query:

      while select BOMId from bomVersion
            group by routeOpr.IsTechnReasonable_ICL,bom.ItemId,ecoResProduct.RecId
            where bomVersion.ItemId==itemIdProduction &&
                  bomVersion.Active == NoYes::Yes &&
                  bomVersion.FromQty == #bomVersionFromQty &&
                  (
                    (bomVersion.FromDate==dateNull() && bomVersion.ToDate == dateNull())||
                    (bomVersion.FromDate<= systemDateGet() && bomVersion.ToDate >= systemDateGet())||
                    (bomVersion.FromDate<= systemDateGet() && bomVersion.ToDate == dateNull())
                  )


I write the following:

static void Job74(Args _args)
{
    Query                   query;
    Queryrun                qr;
    QueryBuildDataSource    qbdsBomVersion;
    BOMVersion              bomVersion;
    
    
    query = new Query();
    qbdsBomVersion = query.addDataSource(tableNum(BOMVersion));
    qbdsBomVersion.addRange(fieldNum(BOMVersion,ItemId)).value(queryValue("7075"));
    qbdsBomVersion.addRange(fieldNum(BOMVersion,Active)).value(queryvalue(SysQuery::value(NoYes::Yes)));
    qbdsBomVersion.addRange(fieldNum(BOMVersion,FromQty)).value(queryValue("1"));
    
    
    qr = new QueryRun(query);
    info(qr.query().dataSourceNo(1).toString());
}


I can't understand how to write this part:

                    (bomVersion.FromDate==dateNull() && bomVersion.ToDate == dateNull())||
                    (bomVersion.FromDate<= systemDateGet() && bomVersion.ToDate >= systemDateGet())||
                    (bomVersion.FromDate<= systemDateGet() && bomVersion.ToDate == dateNull())


Seems like here are two ranges. Tried to use something like this:

qbdsBomVersion.addRange(fieldNum(BomVersion,FromDate)).value(strfmt("((%1.%2 == %4) && (%1.%3 == %4)) || ((%1.%2 <= %5) && (%1.%3 >= %5)) || ((%1.%2 <= %5) && (%1.%3 == %4))",
                            qbdsBomVersion.name(),fieldstr(BOMVersion,FromDate),fieldStr(BOMVersion,ToDate),dateNull(),systemDateGet()));


But with no success. Could you please assist with this?

Thank you.

*This post is locked for comments

  • dark_knight Profile Picture
    dark_knight 768 on at
    RE: complex ranges in dynamic X++ queries

    Thanks. Seems like getting better but I get the error of extended range, like the right bracket is expecting near 35. I follow the recommendations of brackets quantity from here:

    docs.microsoft.com/.../using-expressions-in-query-ranges

    The rules for creating query range value expressions are:

       Enclose the whole expression in parentheses.

       Enclose all subexpressions in parentheses.

       Use the relational and logical operators available in X++.

       Only use field names from the range's data source.

       Use the dataSource.field notation for fields from other data sources in the query.

    Here is the code:

    static void Job74(Args _args)
{
    Query                   query;
    Queryrun                qr;
    QueryBuildDataSource    qbdsBomVersion;
    BOMVersion              bomVersion;


    query = new Query();
    qbdsBomVersion = query.addDataSource(tableNum(BOMVersion));
    qbdsBomVersion.addRange(fieldNum(BOMVersion,ItemId)).value(queryValue("7075"));
    qbdsBomVersion.addRange(fieldNum(BOMVersion,Active)).value(queryvalue(SysQuery::value(NoYes::Yes)));
    qbdsBomVersion.addRange(fieldNum(BOMVersion,FromQty)).value(queryValue("1"));
    qbdsBomVersion.addRange(fieldNum(BomVersion,FromDate)).value(strfmt("((%1.%2 <= %5) && ((%1.%3 == %4) || (%1.%3 >= %5)))",
                            qbdsBomVersion.name(),fieldstr(BOMVersion,FromDate),fieldStr(BOMVersion,ToDate),date2strXpp(dateNull()),systemDateGet()));

    qr = new QueryRun(query);
    info(qr.query().dataSourceNo(1).toString());
}


    Also the resulting query is:

    SELECT * FROM BOMVersion(BOMVersion_1) WHERE ((ItemId = N'7075')) AND ((Active = 1)) AND ((FromQty = 1.E0)) AND ((((BOMVersion_1.FromDate <= 13.02.2019) && ((BOMVersion_1.ToDate == 01\01\1900) || (BOMVersion_1.ToDate >= 13.02.2019)))))


    I thinks it's OK with brackets. Please fix me if I'm wrong. At least I followed recommendation. Also ToDate field is in the inappropriate format for me... - 01\01\1900. I suppose it should be 01.01.1900 but not sure. Thanks.








  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    RE: complex ranges in dynamic X++ queries

    Yes, all valid dates are higher than dateNull(), i.e. 1/1/1900.

  • dark_knight Profile Picture
    dark_knight 768 on at
    RE: complex ranges in dynamic X++ queries

    Hello Martin)) Thank you again for your always quick answers)) you mean that if bomVersion.FromDate will be dateNull() it will be anyway <= systemDateGet() and no need for additional check?

  • Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    RE: complex ranges in dynamic X++ queries

    Can't we first simplify the query to something like this?

    bomVersion.FromDate <= systemDateGet()
  && (bomVersion.ToDate == dateNull() || bomVersion.ToDate >= systemDateGet())

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans