Use X++ query insted of Query Object for join.

Hi All,

Anybody have idea about how to use X++ query for Join and use value of returning from this join.

I mean to say is if we are using query class then we have queryRun.next() to nevigate through data. but same way if we want to use result of join using X++ query then is it possible?

Mehul Thacker.

// This job will join SalesTable and SalesLine and out put all of the lines for a specific salesId.

static void Job5(Args _args)

{

SalesTable salesTable;

SalesLine salesLine;

;

while select salesLine

join salesTable where

salesLine.SalesId == SalesTable.SalesId &&

salesTable.SalesId == ‘SO00000571’

{

info(strfmt(“CustAccount %1; Item: %2; Quantity: %3”, salesTable.CustAccount, salesLine.ItemId, salesLine.SalesQty));

}

}

Hi Alex,

Mehul actually want’s to fetch the data using joins on Query class

Ex:

Query query = new Query();
QueryBuildDataSource queryBuildDSSalesTable;
QueryBuildDataSource queryBuildDSSalesLine;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
SalesLine salesLine;
;

// Setup the primary datasource.
queryBuildDSSalesTable = query.addDataSource(tablenum(SalesTable));
queryBuildDSSalesTable.addRange(fieldnum(SalesTable,SalesId)).value(‘XXXXXX’);

// Setup the secondary (joined) datasource.
queryBuildDSSalesLine = queryBuildDSSalesTable.addDataSource(tablenum(SalesLine));
queryBuildDSSalesLine.joinMode(JoinMode::InnerJoin);
queryBuildDSSalesLine.relations(true);
queryRun = new QueryRun(query);
while(queryRun.next())
{
salesLine = queryRun.get(tablenum(SalesLine));
info(strfmt(’%1’,salesLine.SalesId));
}

Hi mehul, the above example may solve your doubt. if not, please let me know what exactly you want…

Siva Mohan Y

It is as same as writing a select/while select statement and returning the values[:)]

What if you have subselects?

Hi Stephen,

u can use a “while select” in your first loop.

like this:

static void Job1(Args _args)

{

TableA tableA;

TableB tableB;

;

while select tableA

{

while select tableB where tableB.recId == tableA.RefRecId {

info(tableB.field);

}

}

}

HTH

What if you have subselects in the where clause like this

Where (Select asst2.status
from assetbook asst2,
assetbooktable astb2
where astb2.bookid = asst2.bookid
and asst2.assetid = asst.assetid
and astb2.currentoperationstax = 0) <>

(Select asst2.status
from assetbook asst2,
assetbooktable astb2
where astb2.bookid = asst2.bookid
and asst2.assetid = asst.assetid
and astb2.currentoperationstax = 2)

Also what if you have more than one subselect in the select clause like

Select distinct asst.assetid,
(Select asst2.bookid
from assetbook asst2,
assetbooktable astb2
where astb2.bookid = asst2.bookid
and asst2.assetid = asst.assetid
and astb2.currentoperationstax = 0) FinancialBook,

(Select asst2.bookid
from assetbook asst2,
assetbooktable astb2
where astb2.bookid = asst2.bookid
and asst2.assetid = asst.assetid
and astb2.currentoperationstax = 2) TaxBook,

Also, what if you have more than on e subselect in the select clause like:

Select asst.AssetID,

(Select asst2.bookid
from assetbook asst2,
assetbooktable astb2
where astb2.bookid = asst2.bookid
and asst2.assetid = asst.assetid
and astb2.currentoperationstax = 0) FinancialBook,

(Select asst2.bookid
from assetbook asst2,
assetbooktable astb2
where astb2.bookid = asst2.bookid
and asst2.assetid = asst.assetid
and astb2.currentoperationstax = 2) TaxBook,

I rewrote this without the subselects

so now I need to figure out to get it written as a report data provider class

static void Main(Args _args)

{

AssetBook finBook;

AssetBookTable finBookTable;

AssetBook taxBook;

AssetBookTable taxBookTable;

while select finBook

join taxBook where finBook.BookID == taxBook.BookID &&
finBook.Status != taxBook.Status

join finBookTable
where finBook.BookId == finBookTable.BookId &&
finBookTable.CurrentOperationsTax == 0

join taxBookTable
where finBook.BookId == finBookTable.BookId &&
finBookTable.CurrentOperationsTax == 2

{

info(strfmt("%1; %2; %3; %4; %5; %6", finBook.AssetID, finBook.xtraunitnumber, finBook.BookId, taxBook.BookId, finBook.status, taxBook.Status));