web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Select statements to q...
Microsoft Dynamics AX (Archived)

Select statements to query

(0) ShareShare
ReportReport
Posted on by Maverick 182

Hi,

How can I write a query for the below code in x++?

while select vendTable
        {
            select firstonly vendTrans order by vendTrans.TransDate desc
                    where vendTrans.AccountNum  == vendTable.AccountNum;
            procurementTmp.clear();
            procurementTmp.AccountNum           = vendTable.AccountNum;
            procurementTmp.Name                 = vendTable.name();
            procurementTmp.LastTransactionDate  = vendTrans.TransDate;

            procurementTmp.insert();

}

Thanks in advance,

Maverick

*This post is locked for comments

I have the same question (0)
  • Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Select statements to query

    Hi,

    Can you explain some more details regarding your requirement,as the code is already in X++. You can use insert_recordset to insert the data instead of while select loop.

  • Maverick Profile Picture
    Maverick 182 on at
    RE: Select statements to query

    Sorry for not being clear. I just need to populate the temp table (procurementTmp). I know how to do this using select statements.

    But I want to know how can we implement the same logic using query class.

  • Suggested answer
    Chaitanya Golla Profile Picture
    Chaitanya Golla 17,225 on at
    RE: Select statements to query

    Hi,

    Please try the following code:

    Query query;
querybuilddatasource qbds1, qbds2;
QueryRun queryRun;
procurementTmp procurementTmp;
VendTable vendTable;
VendTrans vendTrans;

query = new query();
qbds1 = query.adddatasource(tablenum(VendTable));
qbds2 = qbds1.adddatasource(tablenum(VendTrans));
qbds2.relations(true);
qbds2.joinMode(joinMode::innerjoin);
qbds2.addSortField(fieldNum(VendTrans, TransDate), SortOrder::Descending);

queryRun = new QueryRun(query);

while(queryRun.next())
{
   vendTable = queryRun.get(tablenum(vendTable));
   vendTrans = queryRun.get(tablenum(vendTrans));

     procurementTmp.clear();
     procurementTmp.AccountNum           = vendTable.AccountNum;
     procurementTmp.Name                 = vendTable.name();
     procurementTmp.LastTransactionDate  = vendTrans.TransDate;
     procurementTmp.insert();
}
  • Maverick Profile Picture
    Maverick 182 on at
    RE: Select statements to query

    Thanks for the prompt reply.

    Actually I need to find the last transaction date of each vendor from vendtrans.

    Also, if there is no transaction for a particular vendor in vendtrans, it shoud still fill the temp table with vend.lasttransactionDate as empty.

    I wonder whether your code will work the way I want.

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

#2
NNaumenko Profile Picture

NNaumenko 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans