Read complex SQL statement/Stored procedure and Insert SQL output to D365 table

 If we have SQL based complex query/stored procedure and we wants to implement SQL statement but due to complex SQL statement its difficult us to implement into  X++ 

Today, we will see how D365 FO read the SQL statement and its output; and insert SQL output columns value to D365 FO table.

In my case I have SQL query which contains 400+ lines.

 

public class XXXService extends SysOperationServiceBase

{

    public void processOperation(Contract _contract)

    {

        ResultSet                 resultSet;

        ResultSetMetaData         resultSetMetaData;

        XXX_Table                xxxTable;

        boolean                   isFirstRow = true;

        int                             i;

        str                             sqlStatement;

   

       sqlStatement   = strFmt( _contract.parmSQLStatement()); // get the SQL statement from parameter

        resultSet         = this.getResultSet(sqlStatement);

        resultSetMetaData = resultSet.getMetaData();

 

        while(resultSet.next())  // resultSet contains per record line

      {

            for ( i = 1; i <= resultSetMetaData.getColumnCount(); i++)   // Irritate row one by one  

            {

                if(i==1)

                              xxxTable.SalesId        = resultSet.getString(i);

                else if(i==2)

                              xxxTable.CustomerPostalCode   = resultSet.getInt(i);

                else if(i==3)

                             xxxTable.InvoiceAmount = resultSet.getReal(i);

                else if(i==4)

                             xxxTable.CustomerFullAddress= resultSet.getString(i);

                else if(i==5)

                              xxxTable.InvoiceId         = resultSet.getString(i);

else if(i==6)

                             xxxTable.DimensionValue         = resultSet.getString(i);

                else if(i==7)

                              xxxTable.TotalRequestQty          = resultSet.getReal(i);

                else if(i==8)

                             xxxTable.TotalShipQty                  = resultSet.getReal(i);   

              // Other SQL statement column output assigned to xxxTable       

                    xxxTable.insert();

                }

            }

        }

private ResultSet getResultSet(str _strQuery)

    {

SqlStatementExecutePermission permission;

               ResultSet resultSet;

 

        Connection  connection = new Connection();

        Statement   statement = connection.createStatement();

          

        permission = new SqlStatementExecutePermission(_strQuery);

        permission.assert();

        resultSet = statement.executeQuery(_strQuery);

 

        return resultSet;

    }

 
.

Sample SQL statement:
   
 Select ST.SalesId
    , Case When SubString(ST.SalesId, 1, 2) = 'IN' Then IsNull(ST.SalesId, '') 
	Else '' End as ConvertedSalesId
    
    , Case 
        When SubString(ST.SalesId, 1, 2) = 'IN' Then 'SalesOrder'
        When IsNumeric(IsNull(SL.ItemId, 'Item')) = 1 Then 'PurchaseOrder'
        Else 'TransferOrder'
      End as TransactionType  
    , Cast(IsNull(Case When IsNumeric(IsNull(C.CustAccount, 'A')) = 1 
      Then C.CustAccount Else '0' End, '0') as decimal(10,0)) as convertedCustomerId,
    
      When IsNumeric(IsNull(SH.InvoiceId, 0)) = 1 Then IsNull(SH.InvoiceId, '0')
	Else
      Replace(Left(SubString(Replace(Replace(Replace(Replace(Replace(SH.InvoiceId,'(',''),')',''),'-',''),' ',''),',','')
	    , PatIndex('%[^0-9.-]%', SubString(Replace(Replace(Replace(Replace(Replace(SH.InvoiceId,'(',''),')',''),'-',''),' ',''),',','')
	    , PatIndex('%[0-9.-]%', Replace(Replace(Replace(Replace(Replace(SH.InvoiceId,'(',''),')',''),'-',''),' ',''),',','')), 10) + 'X') -1),'.','')
      End, 20), '0') as NewOrderNumber
    , Case
        
        When IsNull(SL.QtyReceived, 0) = 0 and IsNull(SL.QtyShipped, 0) > 0 Then 1
        Else 0
      End as Status
    , Cast(
       Cast(DatePart(yyyy, ST.CreatedDateTime) as varchar(6))
       + Right('0' + Cast(DatePart(mm, ST.CreatedDateTime) as varchar(6)), 2)
         as decimal(6,0)) as CreatedYearMonth
    From
      Salestable ST
    Inner Join
      SALESLINE SL On
        // Some condition
    Inner Join
      InventDim K On
        // Some condition
    Where
      ST.CreatedDateTime >= (GetDate() - %1)  // Dynamic value
 )


Select
  IsNull(M.InvoiceId, '0') as InvoiceId,
  // Other selected field
From
  SalesTable_H H
Left Outer Join
(
	// other nested join-1
Left Outer Join
(
	// other nested join-2
))