I'm not sure what we're doing differently but I can't get this to generate an error. I tried a few different things. The fact that I'm using a string in the below code vs stringbuilder didn't make a difference (except that the stringbuilder.appendlines hurts my eyes)
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.IO
Imports System.Collections.Generic
Imports System.Globalization
Imports System.Linq
Imports Microsoft.VisualBasic
Imports System.Windows.Forms
Imports OneStream.Shared.Common
Imports OneStream.Shared.Wcf
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Database
Imports OneStream.Stage.Engine
Imports OneStream.Stage.Database
Imports OneStream.Finance.Engine
Imports OneStream.Finance.Database
Namespace OneStream.BusinessRule.Extender.OneOff_RunSQL
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
Dim sql As String = ""
Dim dt As DataTable = Nothing
Using objDbConnInfoApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
sql = "
Select COALESCE(S.RegisterID,O.RegisterID, W.RegisterID) RegisterID,
COALESCE(S.RegisterIDInstance,O.RegisterIDInstance,W.RegisterIDInstance) RegisterIDInstance,
COALESCE(S.WFProfileName,O.WFProfileName,W.WFProfileName) WFProfileName,
COALESCE(S.WFScenarioName,O.WFScenarioName,W.WFScenarioName) WFScenarioName,
COALESCE(S.WFTimeName,O.WFTimeName,W.WFTimeName) WFTimeName,
COALESCE(NULLIF(O.JobTitle,''),W.JobTitle) JobTitle,
COALESCE(NULLIF(O.LastName,''), W.LastName) LastName,
COALESCE(NULLIF(O.FirstName,''), W.FirstName) FirstName,
COALESCE(O.Wage,W.Wage) Wage,
COALESCE(O.Bonus,W.Bonus) Bonus,
COALESCE(O.FTE,W.FTE) FTE,
COALESCE(O.GradeLevel,W.GradeLevel) GradeLevel,
COALESCE(S.Status,O.Status,W.Status) Status,
COALESCE(O.HireDate ,W.HireDate ) HireDate,
COALESCE(O.HirePeriod,W.HirePeriod) HirePeriod,
COALESCE(O.TermDate,W.TermDate) TermDate,
COALESCE(O.TermPeriod,W.TermPeriod) TermPeriod,
COALESCE(O.BirthDate,W.BirthDate) BirthDate,
DATEDIFF(month, '1/1/2024', IIF(CONVERT(varchar,COALESCE(NULLIF(O.TermDate,'1/1/1900 12:00:00 AM'), W.TermDate),101) = '1/1/1900','1/1/3999',CONVERT(varchar,COALESCE(NULLIF(O.TermDate,'1/1/1900 12:00:00 AM'), W.TermDate),101))) +1 As OutPeriod,
COALESCE(S.OutCode,O.OutCode,W.OutCode) OutCode,
DATEDIFF(month, '1/1/2024',IIF(CONVERT(varchar,COALESCE(NULLIF(O.DCode1,'1/1/1900 12:00:00 AM'), W.DCode1),101) = '1/1/1900','1/1/3999',CONVERT(varchar,COALESCE(NULLIF(O.DCode1,'1/1/1900 12:00:00 AM'), W.DCode1),101))) +1 As InPeriod,
COALESCE(NULLIF(NULLIF(S.Entity,'None'),''),NULLIF(NULLIF(O.Entity,'None'),''),W.Entity) Entity,
COALESCE(NULLIF(NULLIF(S.Code1,'None'),''),NULLIF(NULLIF(O.Code1,'None'),''), W.Code1) Code1,
COALESCE(NULLIF(O.Code2,'None'), W.Code2) Code2,
COALESCE(NULLIF(O.Code3,''), W.Code3) Code3,
COALESCE(NULLIF(O.Code4,''), W.Code4) Code4,
COALESCE(NULLIF(O.Code5,''), W.Code5) Code5,
COALESCE(NULLIF(O.Code6,''), W.Code6) Code6,
COALESCE(NULLIF(O.Code7,''), W.Code6) Code7,
COALESCE(NULLIF(O.Code6,''), W.Code6) Code8,
COALESCE(NULLIF(O.Code6,''), W.Code6) Code9,
COALESCE(NULLIF(O.Code6,''), W.Code6) Code10,
COALESCE(O.NCode1, W.NCode1) NCode1,
COALESCE(O.NCode2, W.NCode2) NCode2,
COALESCE(O.NCode3, W.NCode3) NCode3,
COALESCE(O.NCode4, W.NCode4) NCode4,
COALESCE(O.NCode5, W.NCode5) NCode5,
COALESCE(O.NCode6, W.NCode6) NCode6,
COALESCE(O.NCode7, W.NCode7) NCode7,
COALESCE(O.NCode8, W.NCode8) NCode8,
COALESCE(O.DCode1, W.DCode1) DCode1,
COALESCE(O.DCode2, W.DCode2) DCode2,
COALESCE(O.DCode3, W.DCode3) DCode3,
COALESCE(O.DCode4, W.DCode4) DCode4,
COALESCE(NULLIF(O.Annot1,''),W.Annot1) Annot1,
IIF(COALESCE(S.Status,O.Status,W.Status) = 'Workday','Workday', 'People Register') As Annot2,
XFW_PLP_CalcPlanDetail.CalcPlanDetailID As DetailID,
XFW_PLP_CalcPlanDetail.FKClassID,
XFW_PLP_CalcPlanDetail.Description,
XFW_PLP_CalcPlanDetail.WeightOrCount,
XFW_PLP_CalcPlanDetail.PeriodDivisor,
XFW_PLP_CalcPlanDetail.PeriodFilter,
XFW_PLP_CalcPlanDetail.Condition,
XFW_PLP_CalcPlanDetail.EntityOverride,
XFW_PLP_CalcPlanDetail.FlowOverride,
XFW_PLP_CalcPlanDetail.ICOverride,
XFW_PLP_CalcPlanDetail.UD1Override,
XFW_PLP_CalcPlanDetail.UD2Override,
XFW_PLP_CalcPlanDetail.UD3Override,
XFW_PLP_CalcPlanDetail.UD4Override,
XFW_PLP_CalcPlanDetail.UD5Override,
XFW_PLP_CalcPlanDetail.UD6Override,
XFW_PLP_CalcPlanDetail.UD7Override,
XFW_PLP_CalcPlanDetail.UD8Override,
XFW_PLP_CalcPlanDetail.Sequence
From
(Select * from XFW_PLP_Register where status='Workday' and WFProfileName = 'Workforce UK.01 Employee Register'
And WFScenarioName = 'BudV1'
And WFTimeName = '2024' ) W
FULL OUTER JOIN (Select * from XFW_PLP_Register where Status = 'OneStream' and
WFProfileName = 'Workforce UK.01 Employee Register'
And WFScenarioName = 'BudV1'
And WFTimeName = '2024') O
On O.RegisterID = W.RegisterID
LEFT OUTER JOIN (Select * from XFW_PLP_Register where Status = 'Split' and
WFProfileName = 'Workforce UK.01 Employee Register'
And WFScenarioName = 'BudV1'
And WFTimeName = '2024') S
On S.RegisterID = W.RegisterID
CROSS Join XFW_PLP_CalcPlanDetail
Where (
XFW_PLP_CalcPlanDetail.FKCalcPlanID = '01_ACTIVE_UK'
And XFW_PLP_CalcPlanDetail.FKWFProfileName = 'PlanTemplate'
And XFW_PLP_CalcPlanDetail.FKWFScenarioName = 'PlanTemplate'
And XFW_PLP_CalcPlanDetail.FKWFTimeName = 'PlanTemplate'
)
Order By
RegisterID,RegisterIDInstance,XFW_PLP_CalcPlanDetail.CalcPlanDetailID, XFW_PLP_CalcPlanDetail.Sequence
"
dt = brapi.Database.ExecuteSql(objDbConnInfoApp,sql,False)
End Using
Return dt
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
Hi Dan,
I tried putting the query in a single string instead of Append. But did not resolve the issue. Hence I worked on the quick work around suggested by Jack (Configure your query as a SQL Data Adapter, using custom variables where necessary, then use BRApi.Dashboards.Process.GetAdoDataSetForAdapter to execute it from a rule) and it worked flawlessly. I will still try to figure if there is some way, we can disable constraint on Executesql command. Maybe first just bring in columns and then disable constraints and then merge the data. (Looks skeptical)
Really appreciate your help here!!!
