Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How write a dynamic se...
Microsoft Dynamics AX (Archived)

How write a dynamic select statement in X++

(0) ShareShare
ReportReport
Posted on by Community Member

Hi All,

I have a requirement to write my select statement based on one table. The table is as below.

Customer Customer Name Where clause Table Field
13441 TMU SalesId SalesTable CustOrderNumber
11996 TRU SalesId SalesTable EDI_ShipToLocation
123456 ADT LineNum SalesLine LineNum

So if the customer is "13441" I need to use the table as "SalesTable and Field as "CustOrderNumber and in where clause need to use"SalesId".

So my select statement will change based on customer account every time. 

Is there anyway I can achieve it and can build my select statement ?

Regards

*This post is locked for comments

  • Suggested answer
    Andrew Xu Profile Picture
    Andrew Xu 3,877 on at
    RE: How write a dynamic select statement in X++

    Mukti,

    Here's 1 solution should work.

    1. Build a T-SQL statement based on your table.

    2. Execute that statement with class Statement. 

    https://msdn.microsoft.com/en-us/library/statement.executequery.aspx

    3. Iterate the result set to get your data.

  • Dauvis Profile Picture
    Dauvis 170 on at
    RE: How write a dynamic select statement in X++

    If I am understanding that requirement correctly, you should be able to create a query using QueryBuildDataSource with just the table you need and your where clause field would specify the range you add to it (studying Chaitanya's example will teach you what you need to know). To get the field from the record that the QueryRun returns, you will have figure out what the field Id is. Once you have the field Id, you can get its value like this.

    int fieldId = ... // your field Id

    Common tableRecord = ... // the record you get from the QueryRun

    anytype value; // your value

    value = tableRecord.(fieldId);

  • Community Member Profile Picture
    Community Member on at
    RE: How write a dynamic select statement in X++ AX 2012

    Hi Sukrut, 

    I am sorry if I was not able to explain it properly.

    Ok, so the requirement is, in above table (which is basically a configuration table) we have all the customer Id, name, table name id field name.

    I will get the customer Id from external source and will match from this table, if customer Id exists, I need to create a select statement where I need to use the above table and fetch the data from the "SalesTable"; in this case for customer 13441. But my It team can change the value of Table "SalesTable" to "xyzTable"  so basically I need to use run time / dynamic select statement.

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: How write a dynamic select statement in X++ AX 2012

    Hi,

    Could you explain what does field mean in your table, is it the fieldlist you are referring to. You can check code below and let me know if anything is missing.

    SalesTable salesTable;
    SalesLine salesLine;
    Query query;
    QueryRun queryRun;
    Querybuilddatasource qbds, qbdsLine;
    QuerybuildRange qbr;
    AccountNum c1;
    
    c1 = "13441";
    
    Query = new Query();
    qbds = query.addDatasource(tablenum(SalesTable));
    qbdsLine = qbds.addDataSource(tablenum(SalesLine));
    qbdsLine.relations(true);

    if (c1 == "13441")
    {
        qbr = SysQuery::findOrCreateRange(qbds, (fieldnum(SalesTable, SalesId)));
        qbr.value(queryvalue("XYZ1"));
    }
    else if (c1 == "11996")
    {
        qbr = SysQuery::findOrCreateRange(qbds, (fieldnum(SalesTable, SalesId)));
        qbr.value(queryvalue("XYZ2"));
    }
    else if (c1 == "123456")
    {
        qbr = SysQuery::findOrCreateRange(qbdsLine, (fieldnum(SalesLine, LineNum)));
        qbr.value(queryvalue("XYZ3"));
    }
    
    QueryRun = new QueryRun(query);

    while (queryRun.next())
    {
        salesTable = queryRun.get(tablenum(SalesTable));
        salesLine  = queryRun.get(tablenum(SalesLine));
        
        info(strfmt("SalesId: %1", SalesTable.SalesId));
        info(strfmt("LotId: %1", salesLine.InventTransId));
    }


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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,001 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,833 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans