Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

axapta query on multiple tables

Posted on 2012-03-30
1
Medium Priority
?
3,758 Views
Last Modified: 2012-04-02
I'm trying to do a query using 3 tables in axapta.  I do the following:
QueryRun selectReportQuery()
{
    Query                   query           = new Query();
    QueryRun                localQueryRun;
    QueryBuildDataSource    qbds1;
    QueryBuildDataSource    qbds2;
    QueryBuildDataSource    qbds3;
    ;

    qbds1 = query.addDataSource(tableNum(salesTable));
    qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');

    qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));
    qbds2.fetchMode(JoinMode::InnerJoin);
    qbds2.relations(false);
    qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));

    qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);
    qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);
    qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));

    qbds3 = qbds1.addDataSource(tableNum(salesLine));
    qbds3.relations(true);
    qbds3.joinMode(JoinMode::InnerJoin);
    qbds3.fetchMode(queryFetchMode::One2Many);
qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));

when i run the query, it will use the search results from the 1st 2 tables, but will ignore the 3rd table.  What am i doing wrong, or is there another way to do this?
0
Comment
Question by:Cherylvanl
1 Comment
 
LVL 4

Accepted Solution

by:
agusacil earned 2000 total points
ID: 37790629
You may need to restructure your query like this :
CustInvoiceJour
SalesTable
SalesLine

The reason is that AX may gives unpredicted query plan when one main table (SalesTable) is joined to more than one child data sources.
Rearrange that way will allow you fetch all Custinvoicejour linked to SalesTable that is linked to SalesLine with given criteria.

Hope that helps.

Agus
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part III
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question