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
))