Compare Date in Query method (AX 2009) Tuesday, Mar 17 2009 

Quite stuck in finding how to compare date in query method, but finally I found it. I need to convert it into strXpp so that the date convertion will convert correctly. Here is the example code:

……..
……..
queryRange = SysQuery::findOrCreateRange(qbsInventBatch, fieldnum(InventBatch,expDate));
queryRange.value(strfmt(‘(expDate > %1)’, Date2StrXpp(today())));

……
……

Advertisements

Select query by using connection class Monday, Jan 19 2009 

query in standard Axapta is really limited because I can’t define distinct. Here is the code to define it, but as a note, you need to define it as a server run object.

public client server static void GetBatchListed( itemId _ItemId)

{

    Connection  conn;

    Statement   stmt;

    ResultSet   R;

    SqlStatementExecutePermission perm;

    str         sql;

    TSWrkInventBatch    TSWrkInventBatch;

    ;


    conn = new connection();

    stmt = conn.createStatement();


    sql = “select * from InventBatch IB inner join (“;

    sql += “select distinct c.InventBatchId from Inventsum a inner join InventDim b “;

    sql += “on a.Inventdimid = b.inventdimid “;

    sql += “inner join InventBatch c on b.InventBatchId = c.InventBatchId “;

    sql += “where a.ItemId = %1 and “;

    sql += “c.inventBatchId <> ”) as TBJoin on IB.InventBatchId = TBJoin.InventBatchId”;



    perm = new SqlStatementExecutePermission(strfmt(sql,_ItemId));

    perm.assert();

    R = Stmt.executeQuery(sql);


    while (R.next())

    {

        info( R.getString(fieldnum(InventBatch, InventBatchId)));

    }

}