Can't add datasource to queryrun.

This question has suggested answer(s)

Hi,

I want to create new queryrun with one new table based on existing queryrun.
If I have one datasource in base queryrun - all is OK.
If I have two datasources in base queryrun - nothing is OK :-(

Could anybody watch my example and tell me how can I change AddNewTable to see proper query ?

Regards

static void Job11A(Args _args)
{
    Query                       Query;
    QueryRun                    QueryRun;
    QueryBuildDataSource        qbds;

    void AddNewTable(QueryRun _QueryRun)
    {
        QueryBuildDataSource        qbdsCustTable;
        QueryBuildDataSource        qbdsAddress;

        QueryRun                    LocalQueryRun;
        Query                       LocalQuery;
        boolean ret = true;
        ;

        LocalQueryRun   = new QueryRun(_QueryRun.query());
        info (strfmt("# No changes: %1", LocalQueryRun.query().dataSourceNo(1).toString()));

        //please help in this queryrun
        LocalQuery      = new query(_QueryRun.query());
        qbdsCustTable   = LocalQuery.dataSourceTable(tablenum(custTable));
        qbdsAddress     = qbdsCustTable.addDataSource(tablenum(Address));
        qbdsAddress.relations(true);
        qbdsAddress.fetchMode(QueryFetchMode::One2Many);
        qbdsAddress.joinMode(joinMode::InnerJoin);
        LocalQueryRun = new QueryRun(LocalQuery);
        info (strfmt("# With changes: %1", LocalQueryRun.query().dataSourceNo(1).toString()));
    }
    ;

    info("Query with 1 datasource:");
    Query = new Query();
    qbds = Query.addDataSource(tablenum(custTable));
    qbds.addRange(fieldnum(custTable, CustGroup)).value(queryValue("20"));
    QueryRun = new QueryRun(Query);
    AddNewTable(QueryRun);

    info("Query with 2 datasources:");
    Query = new Query();
    qbds = Query.addDataSource(tablenum(custTable));
    qbds.addRange(fieldnum(custTable, CustGroup)).value(queryValue("20"));
    qbds = qbds.addDataSource(tablenum(custTrans)); //new line
    qbds.relations(true);                           //new line
    qbds.joinMode(joinMode::InnerJoin);             //new line
    QueryRun = new QueryRun(Query);
    AddNewTable(QueryRun);
}

This is output:


Query with 1 datasource:
# No changes: SELECT FIRSTFAST * FROM CustTable WHERE ((CustGroup = N'20'))
# With changes: SELECT FIRSTFAST * FROM CustTable WHERE ((CustGroup = N'20')) JOIN FIRSTFAST * FROM Address WHERE CustTable.RecId = Address.AddrRecId AND CustTable.TableId = Address.AddrTableId

Query with 2 datasources:
# No changes: SELECT FIRSTFAST * FROM CustTable WHERE ((CustGroup = N'20')) JOIN FIRSTFAST * FROM CustTrans WHERE CustTable.AccountNum = CustTrans.AccountNum
# With changes: SELECT FIRSTFAST * FROM CustTable WHERE ((CustGroup = N'20')) (WHERE IS CustTrans and Address ???)

Expected output in query with 2 datsources:

SELECT FIRSTFAST * FROM CustTable WHERE ((CustGroup = N'20')) JOIN FIRSTFAST * FROM CustTrans WHERE CustTable.AccountNum = CustTrans.AccountNumJOIN FIRSTFAST * FROM Address WHERE CustTable.RecId = Address.AddrRecId AND CustTable.TableId = Address.AddrTableI

Regards

All Replies
  • If you want to check my job in AX2012, please change tablenum(Address) to tablenum(LogisticsAddressCountryRegion).

  • The problem is that you are attempting to inner join 2 datasources to CustTable that are both 1:n relationships.  The engine will refuse to do this, and in fact it makes no sense.  If one were an EXISTS join then it would work.

    Try it with straight SQL and see what you get back as results and you will understand why the Query engine refuses to do it.

  • If you can explain what you are trying to accomplish, I think someone can help craft your query accordingly.

  • I made this job only as an example. My real query is quite different.

    I have 3 tables: TabItems, TabItemsGroup and TabItemsTrans.

    Relations:

    TabItemsGroup:TabItems (1:n)

    TabItems:TabItemsTrans (1:n)

    User on class (form) have base query(queryrun) like this:

    select TabItems

    or

    select TabItems join TabItemsGroup on TabItems.Group==TabItemsGroup.Group

    User can add queryrange and can add additional tables to query!!!

    But in process I want to make some calculations based on query described above but on table TabItemsTrans. This is why I made "void AddNewTable(QueryRun _QueryRun)" to change query and add TabItemsTrans (join with innerjoin !!!).

    From the base query I want to have query like this:

    select TabItems join TabItemsTrans on TabItems.Item==TabItemsTrans.Item

    or

    select TabItems join TabItemsGroup on TabItems.Group==TabItemsGroup.Group join TabItemsTrans on TabItems.Item==TabItemsTrans.Item

    But I can't do this by modifying the queryrun :-( Is it possible to modify queryrun ?

  • Try

    TabItemsGroup

       |-- 1:n -- TabItems

                           |-- 1:n -- TabItemsTrans

  • the 2 innerjoins act like an AND operations.

    So your query tells give me the custtables that have a custgroup and a custtrans.

    change them to outer join and it is working fine,

  • @ Brandon Wiese

    Yes, your example is very good but...

    Base query with TabItemsGroup and TabItems is from another application and I can't change base query. My duties included changing query and make operations on TabItemsTrans. Base query sometimes have 2 tables (one of them is TabItems) but sometimes have 7-9 tables. I can't change base query.

    I think that there are 2 possibilities:

    1. base query

           |-- 1:n TabItemsTrans (only if last child table in base query is TabItems)

    2. TabItemsTrans

           |-- EXISTS JOIN base query (and I must add all datasources, ranges, links from base query)

  • I think I may have overlooked the obvious solution.

    The problem is that Query engine will not support two or more 1:n inner joins to a root data source.

    But, you don't really have that situation at all.  In fact, since TabItemsGroup -- 1:n --TabItems, then conversely TabItems -- n:1 -- TabItemsGroup.

    Try this.

    qbds = query.dataSourceTable(tableNum(TabItemsGroup));

    qbds.fetchMode(QueryFetchMode::One2One);

    Once the engine sees that TabItemsGroup is not a 1:n relationship, it should allow the TabItemsTrans data source.

  • Of course that only scales to 7-9 tables if they all happen to be 1:1 from TabItems, but here's hoping that's the case.

  • I made a query build before for overriding form query, hope to be usefull for you

    public void init()

    {

        super();

     

        query = new Query();

     

        query.addDataSource(tablenum(Salestable));

        query.dataSourceNo(1).addDataSource(tablenum(CustInvoiceJour));

        query.dataSourceNo(2).relations(true);

        query.dataSourceNo(2).addRange(fieldnum(CustInvoiceJour,Salesid));

        query.dataSourceNo(2).addRange(fieldnum(CustInvoiceJour,InvoiceAccount));

        query.dataSourceNo(2).addRange(fieldnum(CustInvoiceJour,Invoiceid));

        query.dataSourceNo(2).addRange(fieldnum(CustInvoiceJour,Invoicedate));

        query.dataSourceNo(2).addDataSource(tablenum(CustInvoiceTrans));

        query.dataSourceNo(3).relations(true);

        query.dataSourceNo(3).addRange(fieldnum(CustInvoiceTrans,Itemid));

        query.dataSourceNo(3).addDataSource(tablenum(Inventdim));

        query.dataSourceNo(4).relations(true);

        query.dataSourceNo(4).addRange(fieldnum(Inventdim,InventLocationid));

     

        queryRun = new QueryRun(query);

        if (! queryRun.prompt())

        {

            element.close();

        }

     

        SalesTable_ds.query(queryRun.query());

       

    }

    Ibrahim Salah