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
Thx to all for help in this query. Now all works fine.
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.
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 !
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.
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.
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:
Share
Report
All responses (
Answers (
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
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 ?
Set fetchMode(QueryFetchMode::One2One) for datasources A and B.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156