Create view using SQLBuilder class in D365FO

Hi,

In this post we will see the sample code of creating view in SQL Server using SQL builder classes of D365FO. 

•  Created table by name SampleTable and loaded some data into it.
•  Constructed view by name SampleView with the fields Id and duedate of table SampleTable.
•  Note: CG_CreateViewUsingSQLBuilder is a runnable class.

class CG_CreateViewUsingSQLBuilder
{       
    /// <summary>
    /// Runs the class with the specified arguments.
    /// </summary>
    /// <param name = "_args">The specified arguments.</param>
    public static void main(Args _args)
    { 
        SQLBuilderSelectExpression      selectExpr;
        SQLBuilderTableEntry            sampleTable;

        SQLBuilderFieldEntry            id;
        SQLBuilderFieldEntry            dueDate;
        str                             viewId = "SampleView";
        boolean                         isViewCreated;

        selectExpr = SQLBuilderSelectExpression::construct();

        // SampleTable
        sampleTable = selectExpr.addTableId(tablenum(SampleTable));
       
        id      = sampleTable.addFieldId(fieldnum(SampleTable, Id));
        dueDate = sampleTable.addFieldId(fieldnum(SampleTable, DueDate));
       
        // Fields list
        selectExpr.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(id, 'Id'));
        selectExpr.addSelectFieldEntry(SQLBuilderSelectFieldEntry::newExpression(dueDate, 'DueDate'));

        isViewCreated = SQLBuilderView::createView(viewId, selectExpr);

        if (isViewCreated == true)
        {
            info(strFmt("View %1 has been created", viewId));
        }
        else
        {
            info(strFmt("View %1 is not created", viewId));
        }

       
    }

}

Output: Message and ViewDefinition

Regards,

Chaitanya