Question Status

Suggested Answer
IKSIKS asked a question on 29 Jun 2013 10:09 AM

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

Reply
IKSIKS responded on 29 Jun 2013 11:15 AM

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

Reply
Suggested Answer
Brandon Wiese responded on 29 Jun 2013 11:47 AM

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.

Reply
Suggested Answer
Brandon Wiese responded on 29 Jun 2013 11:48 AM

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

Reply
IKSIKS responded on 29 Jun 2013 1:45 PM

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 ?

Reply
Suggested Answer
Brandon Wiese responded on 29 Jun 2013 5:40 PM

Try

TabItemsGroup

   |-- 1:n -- TabItems

                       |-- 1:n -- TabItemsTrans

Reply
Suggested Answer
Dick Wenning responded on 1 Jul 2013 1:19 AM

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,

Kind regards, 

Kaya Solutions

Dick Wenning

+31 6 147 989 53 

Landjuweel 5

3905 PE - Veenendaal

 

OTHER CONTACT INFORMATION

Reply
IKSIKS responded on 2 Jul 2013 12:49 PM

@ 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)

Reply
Suggested Answer
Brandon Wiese responded on 2 Jul 2013 1:09 PM

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.

Reply
Brandon Wiese responded on 2 Jul 2013 1:11 PM

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.

Reply
Suggested Answer
Ibrahim Salah responded on 4 Jul 2013 1:22 PM

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());

   

}

Best Regards,

Ibrahim Salah | Microsoft Dynamics AX Solution architect | My Blog |


Reply
Suggested Answer
Brandon Wiese responded on 29 Jun 2013 11:47 AM

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.

Reply
Suggested Answer
Brandon Wiese responded on 29 Jun 2013 11:48 AM

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

Reply
Suggested Answer
Brandon Wiese responded on 29 Jun 2013 5:40 PM

Try

TabItemsGroup

   |-- 1:n -- TabItems

                       |-- 1:n -- TabItemsTrans

Reply
Suggested Answer
Dick Wenning responded on 1 Jul 2013 1:19 AM

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,

Kind regards, 

Kaya Solutions

Dick Wenning

+31 6 147 989 53 

Landjuweel 5

3905 PE - Veenendaal

 

OTHER CONTACT INFORMATION

Reply
Suggested Answer
Brandon Wiese responded on 2 Jul 2013 1:09 PM

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.

Reply
Suggested Answer
Ibrahim Salah responded on 4 Jul 2013 1:22 PM

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());

   

}

Best Regards,

Ibrahim Salah | Microsoft Dynamics AX Solution architect | My Blog |


Reply