Enforce Constraints on Data Table. Failed to enable constraints. One or more rows contain values.
I am trying to return a SQL query in a Data table inside the business rule. But I am Getting the issue: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
I tried to remove the constraint. Maybe I am doing it incorrectly or it is not possible.
Dim sql As New Text.StringBuilder
sql.AppendLine("Select COALESCE(S.RegisterID,O.RegisterID, W.RegisterID) RegisterID, ")
sql.AppendLine("COALESCE(S.RegisterIDInstance,O.RegisterIDInstance,W.RegisterIDInstance) RegisterIDInstance, ")
sql.AppendLine("COALESCE(S.WFProfileName,O.WFProfileName,W.WFProfileName) WFProfileName, ")
sql.AppendLine("COALESCE(S.WFScenarioName,O.WFScenarioName,W.WFScenarioName) WFScenarioName, ")
'''''''''QUERY Continues as below SQL CODE.
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim ds As New DataSet
ds.Tables.Clear()
ds.EnforceConstraints = False
Dim dt As DataTable = ds.Tables.Add()
' error comes when executing next line
dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
Dim Count As Integer = dt.Constraints.Count
BRApi.ErrorLog.LogMessage(si, "Count: here2")
Return dt
End Using
The SQL query generates a table in a Data Adapter dashboard component. But the rule fails as the Data table does not enforce constraints through data coming from SQL.
The SQL query:
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
Thank you, that's an interesting problem. The fact is that there is no easy way, as far as I can see, to override the settings that ExecuteSql will use; and by default it seems to expect some database constraint to be respected. Unfortunately it's also hard to figure out which constraint is failing, in a query this big -- and it's puzzling that it seems to work fine elsewhere.
For your immediate needs, there is a simple workaround. 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. That should get the job done.
Beyond that, if you want to get a different resolution (e.g. a better error message, or a change in api to allow extra parameters like this to be passed to ExecuteSQL), then you can open a case with Support and work with them. This might take some time though, and because it's about a custom operation on the api, they may or may not provide a resolution.
