How to Return Sql Query from AX 2012

Description:- In this Example we will Show how to return SQL (Structured Query Language) Query from Dynamics AX 2012. So For that we will Create Job in AX 2012.

 

Create Job from AOT

1. In the AOT, click Job.
2. Right-click the Job node, and then select New Job.

Create Table

1. In the AOT, expand the Data Dictionary node.
2. Right-click the Tables node, and then select New Table.
3. Right-click the table, and then click Properties.
4. Rename the table by modifying the Name property.

Add Field to Table

1. Right-click the Fields node of your table.
2. Click New, and then choose a primitive data type to base your field on. If you plan to base the field on a specific extended data type, you must choose a primitive data type that the extended data type is based on.
3. To base the field on an extended data type, set the Extended Datatype property.

static void SqlCodeStatement(Args _args)
{
    //PO_001 - Table Record Name
    A_PO po; - Table name
    select generateonly forceliterals po where po.PO_ID=="PO_001";
    info(po.getSQLStatement());
}

Create Query from AOT

1. In the AOT, click Queries, and locate the query that you want to define a range for.
2. Expand the query, click Data Sources, and then expand a data source.
3. Right-click Ranges, and then click New Range.
4. Right-click the new range, click Properties, and then select a field in the Field property list.

static void SqlQueryReturn(Args _args)
{
    //A_UserQuery - Created Query from AOT in Dynamics AX 2012
    Query query;
    query = new Query(queryStr(A_UserQuery));
    info(query.dataSourceNo(1).toString());
}

Author : Umesh Patel

Related Posts