You’re offline. This is a read only version of the page.
Hi
I have written this query. And I want it to do following.
Share
Reportstatic void Job2(Args _args)
{
Query query = new Query();
QueryBuildDataSource queryBuildDSSalesTable;
QueryBuildDataSource queryBuildDSSalesLine;
QueryBuildDataSource queryBuildDSCustInvoiceJour;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
SalesLine salesLine;
;
// Setup the primary datasource.
queryBuildDSSalesTable = query.addDataSource(tablenum(SalesTable));
// Setup the secondary (joined) datasource.
queryBuildDSSalesLine = queryBuildDSSalesTable.addDataSource(tablenum(SalesLine));
queryBuildDSSalesLine.joinMode(JoinMode::InnerJoin);
queryBuildDSSalesLine.relations(true);
queryBuildDSCustInvoiceJour = queryBuildDSSalesTable.addDataSource(tablenum(CustInvoiceTrans));
queryBuildDSCustInvoiceJour.addRange(fieldnum(CustInvoiceTrans,InvoiceDate)).value('30-09-2015');
queryBuildDSCustInvoiceJour.joinMode(JoinMode::InnerJoin);
queryBuildDSCustInvoiceJour.relations(true);
queryRun = new QueryRun(query);
while(queryRun.next())
{
salesLine = queryRun.get(tablenum(SalesLine));
info(strfmt('%1 %2',salesLine.SalesId,salesLine.NCAdditionalText ));
}
}
*This post is locked for comments
No, I'm pretty sure that "while" works exactly as it should. I don't know your data, but it's clearly filtered by your own query, namely by the joins. Most importantly, you won't see any orders that don't have any corresponding record in CustInvoiceTrans.
Hi Martin
Thanks alot for your help. The query seems to work fine, but I dont know why it does not return all the records that have an invoicedate of 23-09-2015. Is that because I use a while loop?
All responses (
Answers (
Query query = new Query();
QueryBuildDataSource salesTableDs;
QueryBuildDataSource salesLineDs;
QueryBuildDataSource invoiceTransDs;
QueryRun queryRun;
SalesLine salesLine;
CustInvoiceTrans customerInvoiceTrans;
SalesTable salesTable;
salesLineDs = query.addDataSource(tableNum(SalesLine));
salesTableDs = salesLineDs.addDataSource(tableNum(SalesTable));
salesTableDs.relations(true);
invoiceTransDs = salesTableDs.addDataSource(tableNum(CustInvoiceTrans));
invoiceTransDs.addLink(fieldNum(SalesTable, SalesId), fieldNum(CustInvoiceTrans, SalesId));
invoiceTransDs.addRange(fieldNum(CustInvoiceTrans, InvoiceDate)).value(queryValue(mkDate(23,9,2015)));
queryRun = new QueryRun(query);
// info(salesTableDs.toString());
while(queryRun.next())
{
salesLine = queryRun.get(tablenum(SalesLine));
customerInvoiceTrans = queryRun.get(tablenum(CustInvoiceTrans));
info(strfmt('%1 %2',salesLine.SalesId,salesLine.Name));
}
You write code constructing a query and then observe data returned by it, but you never look at the key part - the query itself. But that's exactly what you should be debugging...
If you did that, you would find that your query is wrong:
SELECT * FROM SalesTable(SalesTable_1) JOIN * FROM SalesLine(SalesLine_1) ON SalesTable.SalesId = SalesLine.SalesId
AND SalesTable.SalesId = SalesLine.InventTransId JOIN * FROM CustInvoiceTrans(CustInvoiceTrans_1) ON ((InvoiceDate = {ts '2015-09-30 00:00:00.000'}))
There is no link between SalesLine and CustInvoiceTrans and a weird extra link between SalesTable and SalesLine! Why? Because you call addLink() on the wrong datasource.
Let me show you my refactored version of what you tried to write, returning the query string:
Query query = new Query(); QueryBuildDataSource salesTableDs; QueryBuildDataSource salesLineDs; QueryBuildDataSource invoiceTransDs; salesLineDs = query.addDataSource(tableNum(SalesLine)); salesTableDs = salesLineDs.addDataSource(tableNum(SalesTable)); salesTableDs.relations(true); invoiceTransDs = salesTableDs.addDataSource(tableNum(CustInvoiceTrans)); invoiceTransDs.addLink(fieldNum(SalesTable, SalesId), fieldNum(CustInvoiceTrans, SalesId)); invoiceTransDs.addRange(fieldNum(CustInvoiceTrans, InvoiceDate)).value(queryValue(mkDate(30,9,2015))); info(salesTableDs.toString());
Sorry there should have been this in the last line.
info(strfmt('%1 %2',salesLine.SalesId,salesLine.NCAdditionalText ));
Hi Martin
I try this and I get empty lines
Query query = new Query();
QueryBuildDataSource queryBuildDSSalesTable;
QueryBuildDataSource queryBuildDSSalesLine;
QueryBuildDataSource queryBuildDSCustInvoiceJour;
QueryBuildRange queryBuildRange;
QueryRun queryRun;
SalesLine salesLine;
SalesTable salesTable;
CustInvoiceTrans custInvoiceTrans;
;
// 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.addLink(fieldNum(salesTable,SalesId),fieldNum(salesLine,SalesId));
queryBuildDSCustInvoiceJour = queryBuildDSSalesTable.addDataSource(tablenum(custInvoiceTrans));
queryBuildDSSalesLine.addLink(fieldNum(salesTable,SalesId),fieldNum(custInvoiceTrans,SalesId));
queryBuildDSCustInvoiceJour.addRange(fieldnum(custInvoiceTrans,InvoiceDate)).value(queryValue(mkDate(30,9,2015)));
queryRun = new QueryRun(query);
while(queryRun.next())
{
salesLine = queryRun.get(tablenum(SalesLine));
info(query.toString());
}
You forgot tell us what's the problem. Also I would recommend to check the generate SQL before trying to fetch data. It would help you to spot the missing relation.
Here I have a few point for you (covering at least some bugs):
#1
Mohamed Amine Mahmoudi
100
Super User 2025 Season 1
#2
Community Member
48
#3
Zain Mehmood
6
Moderator
