Using a macro in a SQL statement in D365FO

Hi,

In this post we will see how to execute macro in a SQL statement in D365FO.

•  Created new tables SampleTable, SampleTrans and loaded some data into it.
•  For the where clause introduced a macro by name #CGDueDateRecords and when it is executed on SampleTrans table provides the records details where in which the due date is exceeding when compared to due date on SampleTable for the matching record.
• Macro CGDueDateRecords -- (%1.DueDate > %2.DueDate) 
• Note: CG_MacroInSQL is a runnable class.
• class CG_MacroInSQL
  {       
      /// <summary>
      /// Runs the class with the specified arguments.
      /// </summary>
      /// <param name = "_args">The specified arguments.</param>
      public static void main(Args _args)
      {
          SampleTable    sampleTable;
          SampleTrans    sampleTrans;
        
          while select sampleTrans
              join sampleTable
              where sampleTrans.RefId == sampleTable.Id
              && #CGDueDateRecords(sampleTrans, sampleTable)
          {
              Info(strFmt("Due date %1 is later on TransId %2", sampleTrans.DueDate, sampleTrans.TransId));
          }
               
      }
   
  }

On executing we can see the sample trans records where due date is later than that of its parent table.

Output

Regards,

Chaitanya Golla