web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / DAXology (Study about Dynamics AX and Dynamics ... / Executing direct SQL statem...

Executing direct SQL statement with joins using SQLBuilder class in D365FO

Views (157)
Chaitanya Golla Profile Picture Chaitanya Golla 17,225

Hi,

In this post we will see the demo of executing direct SQL statement with joins using SQLBuilder class in D365FO.

  • Created two tables by name  SampleTable and SampleTrans and loaded some data into it. SampleTable being parent table and SampleTrans being child table with a primary-foreign key relationship.
  • Constructed SQL statement that displays data from tables SampleTable and SampleTrans.
  • Note: CG_SQLWithJoinsUsingSQLBuilder is a runnable class.  
class CG_SQLWithJoinsUsingSQLBuilder
{       
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    {  
        UserConnection                  userConnection;
        Statement                       statement;
        SQLStatementExecutePermission   permission;
        ResultSet                       resultSet;
        str                             sql;
       
        SQLBuilderSelectExpression      selectExpr;
        SQLBuilderTableEntry            sampleTable;
        SQLBuilderTableEntry            sampleTrans;
 
        SQLBuilderFieldEntry            id;
        SQLBuilderFieldEntry            dueDate;
        SQLBuilderFieldEntry            refId;
        SQLBuilderFieldEntry            transId;
 
        DictRelation                    joinRelation;
 
        selectExpr = SQLBuilderSelectExpression::construct();
        selectExpr.parmUseJoin(true);
 
        sampleTable = selectExpr.addTableId(tablenum(SampleTable));
       
        id      = sampleTable.addFieldId(fieldnum(SampleTable, Id));
        dueDate = sampleTable.addFieldId(fieldnum(SampleTable, DueDate));
 
        sampleTrans = sampleTable.addJoinTableId(tablenum(SampleTrans));
       
        transId = sampleTrans.addFieldId(fieldnum(SampleTrans, TransId));
        refId = sampleTrans.addFieldId(fieldnum(SampleTrans, RefId));
 
        sampleTrans.addRelation(id, refId);
        sampleTrans.parmJoinType(SQLBuilderJoinType::InnerJoin);
 
        selectExpr.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(id, 'Id'));
        selectExpr.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(dueDate, 'DueDate'));
        selectExpr.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(transId, 'TransId'));
        selectExpr.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(refId, 'RefId'));
 
        sql = selectExpr.getExpression(null);
 
        userConnection = new UserConnection();
        statement      = userConnection.createStatement();
 
        new SqlStatementExecutePermission(sql).assert();
      
        resultSet = statement.executeQuery(sql);
        CodeAccessPermission::revertAssert();
 
        while (resultSet.next())
        {
            info(strFmt("Id %1 , Due date %2, TransId %3", resultSet.getString(1), resultSet.getString(2), resultSet.getString(3)));
        }
    }
 
}

Output

Regards,

Chaitanya Golla

Comments

*This post is locked for comments