Microsoft Dynamics AX (Archived)

queryRun.query().dataSourceNo(1).toString - not working ?

(0) Share

Report

Posted on by Ireneusz Smaczny

1,535

I have query with 4 datasources.

A
|----B (1:n, innerJoin)
|------C (1:n, innerJoin)
|------D (1:n, innerJoin)

I job I want to view query in job:

query = new query(querystr(AOTTestQuery));
queryRun = new QueryRun(query);
info (queryRun.query().dataSourceNo(1).toString());

But I see in my query only datasources A and B. What to do to view full query ?

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

Suggested answer

Joris dG 17,775 on at

Like (0)

Report

I like to just do info(query.xml());

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report

Set fetchMode(QueryFetchMode::One2One) for datasources A and B.

Was this reply helpful? Yes No
Ireneusz Smaczny 1,535 on at

Like (0)

Report

Hi,
@Joris de Gruyter
info(query.xml()); - it works but query is not a "normal" query, it is to complex to analyze...

@Martin Dráb
Did you mean fetchMode(QueryFetchMode::One2One) to (B and C) and (B to D) ?

Summary I don't understand this query:-(

I try make my query in 2 variants:

Variant1:
A
|----B (1:n, innerJoin)
       |------C (1:n, innerJoin)
       |------D (1:n, innerJoin)

Variant2:
A
|----B (1:n, innerJoin)
       |------C (1:1, innerJoin)
       |------D (1:1, innerJoin)

Job is the same:

query = new query(querystr(AOTTestQuery));
queryRun = new QueryRun(query);
info (queryRun.query().dataSourceNo(1).toString());
while (queryRun.next())
{
A = queryRun.get(tableNum(A));
B = queryRun.get(tableNum(B));
C = queryRun.get(tableNum(C));
D = queryRun.get(tableNum(C));
info (strFmt("%1, %2 -> %3 i %4", A.RecId, B.RecId, C.RecId, D.RecId));
}

Results in Variant1:
ValueA, ValueB, 0, 0
ValueA, ValueB, ValueC1, 0
ValueA, ValueB, ValueC2, 0
ValueA, ValueB, ValueC3, 0
ValueA, ValueB, 0, ValueD1
ValueA, ValueB, 0, ValueD2

Results in Variant 2:
ValueA, ValueB, ValueC1, ValueD1
ValueA, ValueB, ValueC1, ValueD2
ValueA, ValueB, ValueC2, ValueD1
ValueA, ValueB, ValueC2, ValueD2
ValueA, ValueB, ValueC3, ValueD1
ValueA, ValueB, ValueC3, ValueD2

In Variant1:
I don’t understand results :-(
Info(queryRun.query().dataSourceNo(1).toString()); - invalid

In Variant2:
I have cartesian join.
Info(queryRun.query().dataSourceNo(1).toString()); - valid

Can anybody help me to understand my query in Variant1 ?

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report
Sorry, I meant C and D. Let's rather talk about a specific piece of code. It's also necessary because you didn't show us your query, so we can't comment on why your not happy with how your data source are joined.

Look at the following test implementation:

Query query = new Query(); QueryBuildDataSource dsA, dsB, dsC, dsD; dsA = query.addDataSource(tableNum(SalesTable)); dsB = dsA.addDataSource(tableNum(SalesLine)); dsC = dsB.addDataSource(tableNum(InventTable)); dsD = dsB.addDataSource(tableNum(DlvMode)); dsB.relations(true); dsC.relations(true); dsD.relations(true); dsC.fetchMode(QueryFetchMode::One2One); dsD.fetchMode(QueryFetchMode::One2One); info(dsA.toString());

If you didn't set fetch mode, data sources C and D wouldn't be shown in the string returned from toString(). But now it works as expected:

SELECT * FROM SalesTable(SalesTable_1) JOIN * FROM SalesLine(SalesLine_1) ON SalesTable.SalesId = SalesLine.SalesId JOIN * FROM InventTable(InventTable_1) ON SalesLine.ItemId = InventTable.ItemId JOIN * FROM DlvMode(DlvMode_1) ON SalesLine.DlvMode = DlvMode.Code

Was this reply helpful? Yes No
Ireneusz Smaczny 1,535 on at

Like (0)

Report

I changed a litle your job:

static void CopyOfJob43(Args _args)
{
    Query query = new Query();
    QueryRun    queryRun;
    QueryBuildDataSource dsA, dsB, dsC, dsD;
    SalesTable SalesTable;
    SalesLine   SalesLine;
    InventTable InventTable;
    DlvMode DlvMode;
    int i;
    ;
    dsA = query.addDataSource(tableNum(SalesTable));
    dsB = dsA.addDataSource(tableNum(SalesLine));
    dsC = dsB.addDataSource(tableNum(InventTable));
    dsD = dsB.addDataSource(tableNum(DlvMode));
    dsB.relations(true);
    dsC.relations(true);
    dsD.relations(true);
    dsC.fetchMode(QueryFetchMode::One2Many);
    dsD.fetchMode(QueryFetchMode::One2Many);
    info(dsA.toString());

    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
        SalesTable = queryRun.get(tableNum(SalesTable));
        SalesLine = queryRun.get(tableNum(SalesLine));
        InventTable = queryRun.get(tableNum(InventTable));
        DlvMode = queryRun.get(tableNum(DlvMode));

        i++;
        if (i>200)
        {
            break;
        }
        info (strFmt("%1, %2 -> %3, %4", SalesTable.RecId, SalesLine.RecId, InventTable.RecId, DlvMode.RecId));
        //breakpoint;
    }
}

Results:
SELECT * FROM SalesTable(SalesTable_1) JOIN * FROM SalesLine(SalesLine_1) ON SalesTable.SalesId = SalesLine.SalesId
5637144576, 5637144576 -> 0, 0
5637144576, 5637144576 -> 22565421272, 0
5637144576, 5637144576 -> 0, 22565420927
5637144576, 5637144577 -> 0, 0
5637144576, 5637144577 -> 22565421275, 0
5637144576, 5637144577 -> 0, 22565420927
…

Results are like results from 3 queries:
dsA, dsB (example: 5637144576, 5637144576 -> 0, 0)
+
dsA, dsB, dsC (example: 5637144576, 5637144576 -> 22565421272, 0)
+
dsA, dsB, dsD (example: 5637144576, 5637144576 -> 0, 22565420927)

but info(dsA.toString()) – only to first query.

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (0)

Report

Yes, that's the effect of fetch mode.

I hope that your questions "queryRun.query().dataSourceNo(1).toString - not working ?" and What to do to view full query? are now answered.

Was this reply helpful? Yes No
Ireneusz Smaczny 1,535 on at

Like (0)

Report

I try to understand this query with 4 datasources. I changed in AX property cacheLookup for InventTable and DlvMode to None. Next I run SQL Profiler and run my job CopyOfJob43 (above).

In profiler trace I found:

One query with SALESTABLE and SALESLINE:
SELECT T1.*,T2.* FROM SALESTABLE T1 CROSS JOIN SALESLINE T2 WHERE (T2.SALESID=T1.SALESID)) ORDER BY T1.SALESID

For EVERY row from query with SALESTABLE and SALESLINE new query:
SELECT T1.* FROM INVENTTABLE T1 WHERE (ITEMID=@P3) ORDER BY T1.ITEMID

For EVERY row from query with SALESTABLE and SALESLINE new query:
SELECT T1.* FROM DLVMODE T1 WHERE (CODE=@P3) ORDER BY T1.CODE

With 1000 rows from SALESLINE in profiler I have
-   1 main query (SALESTABLE and SALESLINE)
-   1000 queries only for INVENTTABLE
-   1000 queries only for DLVMODE

This is very stupid that DynamicsAX makes the query in this way :-(

This is why my “info(dsA.toString())” shows only 2 tables without INVENTTABLE and DLVMODE !

Was this reply helpful? Yes No
Martin Dráb 237,880 Most Valuable Professional on at

Like (1)

Report

My code already showed how to get the expected value in toString() and fetch data in a single query. It's because it uses QueryFetchMode::One2One.

You've immediately changed it back to One2Many and now you're complaining about it. All what you described in your last reply was the effect of QueryFetchMode::One2Many, which you set by yourself despite of being advised otherwise. If you don't like the behavior of QueryFetchMode::One2Many, simply don't use QueryFetchMode::One2Many. I 've no idea what else you want to hear.

Was this reply helpful? Yes No
Ireneusz Smaczny 1,535 on at

Like (0)

Report

Thx to all for help in this query. Now all works fine.

Was this reply helpful? Yes No

Community site session details

queryRun.query().dataSourceNo(1).toString - not working ?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

queryRun.query().dataSourceNo(1).toString - not working ?

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates