Forum Discussion

Nikpowar97's avatar
Nikpowar97
Contributor
2 years ago

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 fo...
Dashboards
  • JackLacava's avatar
    JackLacava
    2 years ago

    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.

JackLacava's avatar
JackLacava
Honored Contributor
2 years ago

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.

  • Nikpowar97's avatar
    Nikpowar97
    Contributor
    2 years ago

    Thanks Jack and wonderful folks here for discussing this. 

    I agree with Jack's suggestion to Configure query as a SQL Data Adapter, using custom variables(Using BRString() maybe where necessary, then use BRApi.Dashboards.Process.GetAdoDataSetForAdapter to execute it from a rule as a workaround.

    FYI.

    The two queries that worked both in data adapter and code are:

    Query: 

    Select XFW_PLP_Register.RegisterID,
    XFW_PLP_Register.RegisterIDInstance,
    XFW_PLP_Register.WFProfileName,
    XFW_PLP_Register.WFScenarioName,
    XFW_PLP_Register.WFTimeName,
    COALESCE(NULLIF(X.JobTitle,''), XFW_PLP_Register.JobTitle) JobTitle ,
    COALESCE(NULLIF(X.LastName,''), XFW_PLP_Register.LastName) LastName,
    COALESCE(NULLIF(X.FirstName,''), XFW_PLP_Register.FirstName) FirstName ,
    COALESCE(X.Wage,XFW_PLP_Register.Wage) Wage ,
    COALESCE(X.Bonus, XFW_PLP_Register.Bonus) Bonus,
    COALESCE(X.FTE, XFW_PLP_Register.FTE) FTE,
    COALESCE(X.GradeLevel, XFW_PLP_Register.GradeLevel) GradeLevel ,
    COALESCE(XFW_PLP_Register.Status, X.Status) Status ,
    COALESCE(X.HireDate, XFW_PLP_Register.HireDate) HireDate ,
    COALESCE(X.HirePeriod, XFW_PLP_Register.HirePeriod) HirePeriod ,
    COALESCE(X.TermDate, XFW_PLP_Register.TermDate) TermDate,
    COALESCE(X.TermPeriod, XFW_PLP_Register.TermPeriod) TermPeriod ,
    COALESCE(X.BirthDate, XFW_PLP_Register.BirthDate) BirthDate ,
    DATEDIFF(month, '1/1/2024', IIF(CONVERT(varchar,COALESCE(NULLIF(X.TermDate,'1/1/1900 12:00:00 AM'), XFW_PLP_Register.TermDate),101) = '1/1/1900','1/1/3999',CONVERT(varchar,COALESCE(NULLIF(X.TermDate,'1/1/1900 12:00:00 AM'), XFW_PLP_Register.TermDate),101))) +1 As OutPeriod,
    COALESCE(X.OutCode, XFW_PLP_Register.OutCode) OutCode ,
    DATEDIFF(month, '1/1/2024',IIF(CONVERT(varchar,COALESCE(NULLIF(X.DCode1,'1/1/1900 12:00:00 AM'), XFW_PLP_Register.DCode1),101) = '1/1/1900','1/1/3999',CONVERT(varchar,COALESCE(NULLIF(X.DCode1,'1/1/1900 12:00:00 AM'), XFW_PLP_Register.DCode1),101))) +1 As InPeriod,
    COALESCE(X.InCode, XFW_PLP_Register.InCode) InCode ,
    COALESCE(NULLIF(X.Entity,'None'), XFW_PLP_Register.Entity) Entity,
    COALESCE(NULLIF(X.Code1,''),XFW_PLP_Register.Code1) Code1,
    COALESCE(NULLIF(X.Code2,''), XFW_PLP_Register.Code2) Code2,
    COALESCE(NULLIF(X.Code3,''), XFW_PLP_Register.Code3) Code3 ,
    COALESCE(NULLIF(X.Code4,''), XFW_PLP_Register.Code4) Code4,
    COALESCE(NULLIF(X.Code5,''), XFW_PLP_Register.Code5) Code5 ,
    COALESCE(NULLIF(X.Code6,''), XFW_PLP_Register.Code6) Code6 ,
    COALESCE(NULLIF(X.Code7,''), XFW_PLP_Register.Code7) Code7,
    COALESCE(NULLIF(X.Code8,''), XFW_PLP_Register.Code8) Code8 ,
    COALESCE(NULLIF(X.Code9,''), XFW_PLP_Register.Code9) Code9 ,
    COALESCE(NULLIF(X.Code10,''), XFW_PLP_Register.Code10) Code10,
    COALESCE(NULLIF(X.Code11,''), XFW_PLP_Register.Code11) Code11,
    COALESCE(NULLIF(X.Code12,''), XFW_PLP_Register.Code12) Code12 ,
    COALESCE(X.NCode1, XFW_PLP_Register.NCode1) NCode1,
    COALESCE(X.NCode2, XFW_PLP_Register.NCode2) NCode2,
    COALESCE(X.NCode3, XFW_PLP_Register.NCode3) NCode3 ,
    COALESCE(X.NCode4, XFW_PLP_Register.NCode4) NCode4 ,
    COALESCE(X.NCode5, XFW_PLP_Register.NCode5) NCode5 ,
    COALESCE(X.NCode6, XFW_PLP_Register.NCode6) NCode6 ,
    COALESCE(X.NCode7, XFW_PLP_Register.NCode7) NCode7,
    COALESCE(X.NCode8, XFW_PLP_Register.NCode8) NCode8 ,
    COALESCE(X.DCode1, XFW_PLP_Register.DCode1) DCode1,
    COALESCE(X.DCode2, XFW_PLP_Register.DCode2) DCode2,
    COALESCE(X.DCode3, XFW_PLP_Register.DCode3) DCode3,
    COALESCE(X.DCode4, XFW_PLP_Register.DCode4) DCode4,
    COALESCE(NULLIF(X.Annot1,''), XFW_PLP_Register.Annot1) Annot1,
    COALESCE(NULLIF(X.Annot2,''),XFW_PLP_Register.Annot2) 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
    XFW_PLP_Register
    CROSS Join XFW_PLP_CalcPlanDetail
    left outer Join (Select * from XFW_PLP_Register where Status = 'OneStream' and
    WFProfileName = 'Workforce Norway.01 Employee Register'
    And WFScenarioName = 'BudV1'
    And WFTimeName = '2024') X
    On X.RegisterID = XFW_PLP_Register.RegisterID
    Where (
    XFW_PLP_Register.WFProfileName = 'Workforce Norway.01 Employee Register'
    And XFW_PLP_Register.WFScenarioName = 'BudV1'
    And XFW_PLP_Register.WFTimeName = '2024'
    And XFW_PLP_CalcPlanDetail.FKCalcPlanID = '15_ACTIVE_HUNGARY'
    And XFW_PLP_CalcPlanDetail.FKWFProfileName = 'PlanTemplate'
    And XFW_PLP_CalcPlanDetail.FKWFScenarioName = 'PlanTemplate'
    And XFW_PLP_CalcPlanDetail.FKWFTimeName = 'PlanTemplate'
    )
    Order By
    XFW_PLP_Register.RegisterID, XFW_PLP_Register.RegisterIDInstance, XFW_PLP_CalcPlanDetail.CalcPlanDetailID, XFW_PLP_CalcPlanDetail.Sequence

    Query2:

    Select XFW_PLP_Register.RegisterID,
    XFW_PLP_Register.RegisterIDInstance,
    XFW_PLP_Register.WFProfileName,
    XFW_PLP_Register.WFScenarioName,
    XFW_PLP_Register.WFTimeName,
    XFW_PLP_Register.JobTitle,
    XFW_PLP_Register.LastName,
    XFW_PLP_Register.FirstName,
    XFW_PLP_Register.Wage,
    XFW_PLP_Register.Bonus,
    XFW_PLP_Register.FTE,
    XFW_PLP_Register.GradeLevel,
    XFW_PLP_Register.Status,
    XFW_PLP_Register.HireDate,
    XFW_PLP_Register.HirePeriod,
    XFW_PLP_Register.TermDate,
    XFW_PLP_Register.TermPeriod,
    XFW_PLP_Register.BirthDate,
    DATEDIFF(month, '1/1/2024', IIF(CONVERT(varchar,(XFW_PLP_Register.TermDate),101) = '1/1/1900','1/1/3999',CONVERT(varchar,XFW_PLP_Register.TermDate,101))) +1 As OutPeriod,
    XFW_PLP_Register.OutCode,
    DATEDIFF(month, '1/1/2024', IIF(CONVERT(varchar,(XFW_PLP_Register.DCode1),101) = '1/1/1900','1/1/3999',CONVERT(varchar,XFW_PLP_Register.DCode1,101))) +1 As InPeriod,
    XFW_PLP_Register.InCode,
    XFW_PLP_Register.Entity,
    XFW_PLP_Register.Code1,
    XFW_PLP_Register.Code2,
    XFW_PLP_Register.Code3,
    XFW_PLP_Register.Code4,
    XFW_PLP_Register.Code5,
    XFW_PLP_Register.Code6,
    XFW_PLP_Register.Code7,
    XFW_PLP_Register.Code8,
    XFW_PLP_Register.Code9,
    XFW_PLP_Register.Code10,
    XFW_PLP_Register.Code11,
    XFW_PLP_Register.Code12,
    XFW_PLP_Register.NCode1,
    XFW_PLP_Register.NCode2,
    XFW_PLP_Register.NCode3,
    XFW_PLP_Register.NCode4,
    XFW_PLP_Register.NCode5,
    XFW_PLP_Register.NCode6,
    XFW_PLP_Register.NCode7,
    XFW_PLP_Register.NCode8,
    XFW_PLP_Register.DCode1,
    XFW_PLP_Register.DCode2,
    XFW_PLP_Register.DCode3,
    XFW_PLP_Register.DCode4,
    XFW_PLP_Register.Annot1,
    IIF(XFW_PLP_Register.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
    XFW_PLP_Register
    CROSS Join XFW_PLP_CalcPlanDetail
    Where (
    XFW_PLP_Register.WFProfileName = 'Workforce UK.01 Employee Register'
    And XFW_PLP_Register.WFScenarioName = 'BudV1'
    And XFW_PLP_Register.WFTimeName = '2024'
    And 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'
    And (XFW_PLP_Register.Status ='Workday')
    )
    Order By
    XFW_PLP_Register.RegisterID, XFW_PLP_Register.RegisterIDInstance, XFW_PLP_CalcPlanDetail.CalcPlanDetailID, XFW_PLP_CalcPlanDetail.Sequence

     

    The Query which worked in the SQL Adapter but didn't work as expected is the mentioned at the Top.

     

     

    • DanielWillis's avatar
      DanielWillis
      Valued Contributor
      2 years ago

      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

       

       

      • Nikpowar97's avatar
        Nikpowar97
        Contributor
        2 years ago

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