sql query in ax

create a connection using connection object, don't need to assign any parameter here, this will help to run query. Create an statement object and assign a query in an execute method of it. Before that you need to create an SQlStatementExecutePermission that will allow to use SQL in a X++, then call assert that declares the calling code to invoke an API that is protected by a permission. after execute query a resultset is generated that you can access via .get(Datatype) method.

example is given below.



Connection con = new Connection();
Statement stmt = con.createStatement();
str sqlPassStr1;
ResultSet sqlres;
SqlStatementExecutePermission sqlStatementExecutePermission;
;

sqlPassStr1 = 'select ACCOUNTNUM, CUSTGROUP from CustTable';

sqlStatementExecutePermission = new SqlStatementExecutePermission(sqlPassStr1);
sqlStatementExecutePermission.assert();

sqlres = stmt.executeQuery( sqlPassStr1);
while(sqlres.next())
{
info(strfmt('%1 %2', sqlres.getString(1), sqlres.getString(2)));
}

add group by and fields selection in query

QueryBuildDataSource provides addSelectionField and addGroupByField to add fields and group on a query.

Further, what the aggregate function that you want to apply on a field you can provide it in a addSelectionField method that supports base enum SelectionField with some basic statistical operations. A sample code is given below.


Query q = new Query();
QueryBuildDataSource queryBuildDataSource;
QueryRun queryRun;
LedgerJournalTrans ledgerJournalTrans;

queryBuildDataSource = q.addDataSource(tablenum(LedgerJournalTrans));

queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans, JournalNum));
queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans,AmountCurDebit), SelectionField::Sum);
queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans, AmountCurCredit), SelectionField::Sum);
queryBuildDataSource.addGroupByField(fieldnum(LedgerJournalTrans, JournalNum));

queryRun = new QueryRun(q);

while(queryRun.next())
{
ledgerJournalTrans = queryRun.get(tablenum(LedgerJournalTrans));

info(strfmt('Journal Num = %1 Debit = %2 Credit = %3', ledgerJournalTrans.JournalNum, ledgerJournalTrans.AmountCurDebit, ledgerJournalTrans.AmountCurCredit));

}

An equivalent SQL query is given below.

select Journalnum, sum(AmountCurDebit), SUM(AmountCurCredit)
from LEDGERJOURNALTRANS
group by JOURNALNUM

Further you can filter the records using the addRange method in a QueryBuildDataSource class.

e.g.

queryBuildDataSource.addRange(fieldnum(LedgerJournalTrans, JournalNum)).value('000033_444')

the above statement filters the journal number '000033_444'.

min and max in ax

Use minof and maxof to find minimum and maximum values of a table field. minof finds the minimum value of a specific field and maxof finds the maximum value of a specific field.

here's the example below.

LedgerTrans ledgerTrans;

select minof(TransDate) from ledgerTrans;

info(strfmt('%1', ledgertrans.TransDate));

select maxof(TransDate) from ledgerTrans;

info(strfmt('%1', ledgertrans.TransDate));

equivalent SQL queries for minimum and maximum date respectively.

select min(transdate) from LedgerTrans

select max(transdate) from LedgerTrans

add query range in a query object

First we need to find the datasource where we want to apply the range. Query.datasourceTable provides the reference of a querybuilddatasource. After getting the reference we can use the addRange method to add range and value in it.

Here's the demo. CustTable is a query object exist in AOT. No range is applied in a AOT query object, here the range is applied on a accountnum field of CustTable.

Query query = new Query(querystr(CustTable));
QueryRun queryRun;
QueryBuildDataSource queryBuildDataSource;
CustTable custTable;

//finding the reference of datasource.
queryBuildDataSource = query.dataSourceTable(tablenum(CustTable));

//adding range and providing the value for the range.
queryBuildDataSource.addRange(fieldnum(CustTable, AccountNum)).value('4000');

queryRun = new QueryRun(query);

while(queryRun.next())
{
custTable = queryRun.get(tablenum(CustTable));

info(strfmt('%1 ', custTable.AccountNum));

}