How to add self join to a table using Query class

Finance and Operations/AX Developer Forum
1

Hi,

My requirement is to add a self join to a table using Query class, the Sql could be:

Select t1.col1, t2.col3

From tab1 t1 inner join tab1 t2

Where t1.col1 = t2.col3

How to convert it into query class and add self linking?

Regards,

Abhinay

2

It’s the same as joining two different tables, you just likely don’t have any relation between col1 and col3, therefore you will have to add one by addLink().

3

Hi Martin,

I have a form with PurchTable, PurchLine and AccountingDistribution tables in datasource, when I try to add the following code in the init method of AccountingDistribution table it gives a run time error: Error: The data source is not embedded within a (parent) data source.

public****void init()
{
;

super();

this.queryBuildDataSource().addDataSource(tableNum(AccountingDistribution)).joinMode(JoinMode::NoExistsJoin);
this.queryBuildDataSource().relations(false);

this.queryBuildDataSource().addLink(fieldNum(AccountingDistribution, RecId),
fieldNum(AccountingDistribution, ReferenceDistribution));

//info(this.query().toString());
}

4

It’s because you’re trying to add the link to the parent data source, not to the child one. This works:

Query query = new Query();
QueryBuildDataSource ds1 = query.addDataSource(tableNum(AccountingDistribution));
QueryBuildDataSource ds2 = ds1.addDataSource(tableNum(AccountingDistribution));
ds2.relations(false);
ds2.addLink(fieldNum(AccountingDistribution, RecId),
            fieldNum(AccountingDistribution, ReferenceDistribution));
info(ds1.toString());