Finance | Project Operations, Human Resources, ...

Answered

Cartesian product of three tables via Query and QueryBuildDataSource

(0) Share

Report

Posted on by Sebastian Aurand

1,650

Hello,

I need to connect three tables in a Query in order to get the cartesian product of all three in runtime. How can I achieve this requirenment using Query, QueryBuildDataSource and QueryRun. If I do it in the following matter the tables are fetched one after the other in the queryrun (first all values of "TableBase", second all values of "Table1", third all values of "Table2"). The cursor "qr" never contains all the values at the same time.

Query queryAll = new Query();

QueryBuildDataSource qbds_base = queryAll.addDataSource(tableNum(TableBase));

QueryBuildDataSource qbds_table1 = qbds_base.addDataSource(tableNum(Table1));
qbds_table1.joinMode(JoinMode::InnerJoin);

QueryBuildDataSource qbds_table2 = qbds_base.addDataSource(tableNum(Table2));
qbds_table2.joinMode(JoinMode::InnerJoin);

QueryRun qr = new QueryRun(queryAll);

while (qr.next())
{

TableBase tableBase = qr.get(tableNum(TableBase));

Table1 table1 = qr.get(tableNum(Table1));

Table2 table2 = qr.get(tableNum(Table2));

}

Thanks

Sebastian

I have the same question (0)

All responses (6)

Answers (1)

Suggested answer

ergun sahin 8,826 Moderator on at

Like (0)

Report

Add relations (qbds.relations(true) or manuel relation)

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

Like (0)

Report
Ergün is right - use relations(true) to use table relations, or addLink() if relations can't be used.

By the way, please use Insert > Insert Code (in the rich formatting view) to paste source code. It's then easier to read. For example:

Query queryAll = new Query(); QueryBuildDataSource qbds_base = queryAll.addDataSource(tableNum(TableBase)); QueryBuildDataSource qbds_table1 = qbds_base.addDataSource(tableNum(Table1)); qbds_table1.joinMode(JoinMode::InnerJoin); QueryBuildDataSource qbds_table2 = qbds_base.addDataSource(tableNum(Table2)); qbds_table2.joinMode(JoinMode::InnerJoin); QueryRun qr = new QueryRun(queryAll); while (qr.next()) { TableBase tableBase = qr.get(tableNum(TableBase)); Table1 table1 = qr.get(tableNum(Table1)); Table2 table2 = qr.get(tableNum(Table2)); }

Was this reply helpful? Yes No
Sebastian Aurand 1,650 on at

Like (0)

Report
Thanks for the hints. I did not use "relations(true)" because the tables have no link. How could I establish a manual link in order to get all possible combinations in the query:

Table Base:

ID1 = Entry1

ID2 = Entry2

ID3 = Entry3

Table1:

IDa = First entry

IDb = Second entry

IDc = Third entry

Table2:

No1 = Number 1

No2 = Number 2

No3 = Number 3

Required result in the query:

ID1; IDa; No1

ID1; IDa; No2

ID1; IDa; No3

ID1; IDb; No1

ID1; IDb; No2

ID1; IDb; No3

ID1; IDc; No1

...

ID3; IDc; No3

In SQL this could be done with the select statement:

SELECT *
FROM TABLEBASE
CROSS JOIN TABLE1
CROSS JOIN TABLE2

Sebastian

Was this reply helpful? Yes No
Verified answer

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

Like (0)

Report

Ah, sorry, I got distracted by the previous answer and didn't realize that your question is different.

Then the fact that you don't have any join conditions is correct; that's what you want.

What if you use qbds_table1.fetchMode(QueryFetchMode::One2One) and qbds_table2.fetchMode(QueryFetchMode::One2One)? Will you get the expected result?

Was this reply helpful? Yes No
Sebastian Aurand 1,650 on at

Like (0)

Report

Thanks Martin. This was the missing point.

Was this reply helpful? Yes No
ergun sahin 8,826 Moderator on at

Like (1)

Report

Sorry, I missed that there is no relationship between tables. Since you didn't include the code with rich formatting, I gave my full attention to understanding the codes. It seems It was necessary to focus on what you wanted to convey with the "cartesian product".

Was this reply helpful? Yes No

Join the conversation

Community site session details

Cartesian product of three tables via Query and QueryBuildDataSource

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Cartesian product of three tables via Query and QueryBuildDataSource

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 > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates