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

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, where clause and literals in D365FO

Views (464)
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, where clause and literals 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 its record exceeds the due date of corresponding matching record in table SampleTable and also Amount field of that record is greater than constant value 25.
  •  Note CG_ExecuteSQLStmtWithLiterals is a runnable class.
class CG_ExecuteSQLStmtWithLiterals
{       
    /// <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));
        DictField                       amount = new DictField(tableNum(SampleTrans), fieldNum(SampleTrans, Amount));
 
        str                             sql;
        TransDate                       transactionDate;
 
        system = new SqlSystem();
 
        sql = 'SELECT %1, %3.%2, %7, %4.%6, %4.%8 FROM %3 '+ 'JOIN %4 ON %3.%1 = %4.%5 ' + ' WHERE %4.%6 > %3.%2 AND  %4.%8 > %9';
 
        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
                        amount.name(DbBackend::Sql),            // 8
                        system.sqlLiteral(25));                 // 9
       
        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