AOT and X++ queries and ranges in AX 2012

Let's say we have a query in AOT \Queries\ProjTable

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*")'));