Let’s say we have a query in AOT QueriesProjTable
Resultant X++ Query
SELECT * FROM ProjTable(ProjTable)
EXISTS JOIN * FROM ProjTableDateViewSw(ProjTableDateViewSw_1)
WHERE ProjTable.ProjId = ProjTableDateViewSw.ProjId AND
And now we want to add more datasources into this query’s parent datasource (ProjTable) and some ranges etc.
Let’s assume we have a class (SRS report data provider class or a dialog class) which is using above query and during processing in this class we want to add more datasources and so on.
I created a SRS report data provider class for this example;
Class declaration
[
SRSReportQueryAttribute (querystr(MarginAnalysisReportV2Sw)),
SRSReportParameterAttribute(classstr(MarginAnalysisReportContractV2Sw))
]
public class MarginAnalysisReportDPV2Sw extends SRSReportDataProviderBase
{
Query query;
TempTable tempTable;
RecordInsertList recordInsertListTmpTable;
ProjTable projTable;
TransDate AsOfDate;
}
ProcessReport
[SysEntryPointAttribute]
public void processReport()
{
// Query variable declaration
QueryRun queryRun;
// Contract class declaration
MarginAnalysisReportContractV2Sw dataContract;
// Contract class parameters
ProjDateCriteriaSw dateType;
// Views declaration
ProjTableDateViewSw projTableDateViewSw;
// This will retrieve the above ProjTable query
// Select * from ProjTable
query = this.parmQuery();
dataContract = this.parmDataContract();
dateType = dataContract.parmDateType();
asOfDate = dataContract.parmAsOfDate();
this.addParameterRanges();
queryRun = new QueryRun(query);
while(queryRun.next())
{
projTable = queryRun.get(tablenum(ProjTable));
this.insertTmpTable(ProjTable);
}
}
addParameterRanges() Method
private void addParameterRanges()
{
QueryBuildDataSource qbds1;
QueryBuildRange qbr1;
// add ProjTableDate table into query’s parent datasource projtable
qbds1= query.dataSourceTable(tableNum(projTable)).addDataSource(tableNum(ProjTableDate));
// defining relation based on ProjId
qbds1.addLink(fieldNum(ProjTable, ProjId), fieldNum(ProjTableDate, ProjId));
// defining join mode
qbds1.joinMode(JoinMode::ExistsJoin);
// adding range on ActualEndDate field of ProjTableDate table
// setting value <= asOfDate which is considered as per date
qbds1.addRange(fieldNum(ProjTableDate,ActualEndDate)).value(queryRange(dateNull(), asOfDate));
}
Resultant X++ Query
SELECT * FROM ProjTable(ProjTable)
EXISTS JOIN * FROM ProjTableDateViewSw(ProjTableDateViewSw_1)
WHERE ProjTable.ProjId = ProjTableDateViewSw.ProjId AND
((ActualEndDate<={ts ‘2015-05-14 00:00:00.000’}))
Adding OR and AND in query
QueryBuildRange qbr;
qbr = query.dataSourceTable(tableNum(Table)).addRange(fieldNum(Table, TransDate));
qbr.value(strFmt(‘((%1 != %2) || (%3 == %4))’,
fieldStr(Table, ModelId),
queryvalue(“Latest”),
fieldStr(Table, TransDate),
asOfDate
));
qbr.value(strFmt(‘((%1 != %2) && (%3 == %4))’,
fieldStr(Table, ModelId),
queryvalue(“Latest”),
fieldStr(Table, TransDate),
asOfDate
));
Using enum value in query as a range
qbr.value(strFmt(‘(Status == %1)’, any2int(ProjStatus::active)));
Check modifiedDate value of the table
qbr.value(strFmt(‘(ModifiedDate > ProjTable.ModifiedDate)’));
Using wildcards in query
You can use LIKE keyword for wildcards in ranges
qbr.value(strFmt(‘(ProjName LIKE “*Builder*”)’));
