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 a join and where clause in D365FO

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

Hi,

In this post we will see the demo of executing direct SQL statement with a join and where clause in D365FO.

  •  Created two tables by name SampleTable and SampleTrans that holds the parent-child relationship data respectively.
  •  Constructed SQL statement that displays data from table SampleTrans where due date of any record of table SampleTrans exceeds the due date on the corresponding record of table SampleTable.
  •  Note: CG_ExecuteSQLStmt is a runnable class
class CG_ExecuteSQLStmt
{       
    /// <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;
        SQLSystem                       system;         
        SQLStatementExecutePermission   permission;         
        ResultSet                       resultSet;         
       
        DictTable                       sampleTable = new DictTable(tableNum(SampleTable));
        DictTable                       sampleTrans = new DictTable(tableNum(SampleTrans));
       
        DictField                       Id = new DictField(tableNum(SampleTable), fieldNum(SampleTable, Id));
        DictField                       tableDueDate = new DictField(tableNum(SampleTable), fieldNum(SampleTable, dueDate));
        DictField                       refId = new DictField(tableNum(SampleTrans), fieldNum(SampleTrans, RefId));
        DictField                       transId = new DictField(tableNum(SampleTrans), fieldNum(SampleTrans, TransId));
        DictField                       transDueDate = new DictField(tableNum(SampleTrans), fieldNum(SampleTrans, dueDate));
 
        str                             sql;
 
        system = new SqlSystem();
 
        sql = 'SELECT %1, %3.%2, %7, %4.%6 FROM %3 '+ 'JOIN %4 ON %3.%1 = %4.%5 ' + ' WHERE %4.%6 > %3.%2';
 
        sql = strFmt(   sql,
                        Id.name(DbBackend::Sql),            // 1
                        tableDueDate.name(DbBackend::Sql),  // 2
                        sampleTable.name(DbBackend::Sql),   // 3
                        sampleTrans.name(DbBackend::Sql),   // 4
                        RefId.name(DbBackend::Sql),         // 5
                        transDueDate.name(DbBackend::Sql),  // 6
                        transId.name(DbBackend::Sql));      // 7
       
        userConnection = new UserConnection();         
        statement      = userConnection.createStatement();
 
        new SqlStatementExecutePermission(sql).assert();
       
        resultSet = statement.executeQuery(sql);
        CodeAccessPermission::revertAssert();
 
        while (resultSet.next())        
        {   
            info(strFmt("TransId %1 with due date %2", resultSet.getString(3), resultSet.getString(4)));        
        }
 
    }
 
}
Regards,

Chaitanya Golla

Comments

*This post is locked for comments