cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enforce Constraints on Data Table. Failed to enable constraints. One or more rows contain values.

Go to solution
Nikpowar97
New Contributor III

‎04-05-2023 01:53 AM - last edited on ‎05-25-2023 05:41 AM by Community Manager

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

 

 

 

Labels:
0 Kudos
1 ACCEPTED SOLUTION

Go to solution
JackLacava
Community Manager
Community Manager

‎04-05-2023 08:56 AM

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.

View solution in original post

0 Kudos
16 REPLIES 16

Go to solution
DanielWillis
Contributor III

‎04-05-2023 01:59 AM - edited ‎04-05-2023 02:00 AM

Does it work if you change it to the below?

 

Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
return BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
End Using

 

Most of the other stuff seems unnecessary to me at a quick glance unless it has been simplified for the example.

0 Kudos

Go to solution
Nikpowar97
New Contributor III
In response to DanielWillis

‎04-05-2023 03:23 AM - edited ‎04-05-2023 04:30 AM

Hey Daniel, I tried with directly returning the Datatable without storing in variable (whcih was by default) The issue still persists. I am doing the same thing by storing in a variable. But the issue is in running the SQL string query in the Data Table. It throws an error: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

The same SQL query when ran through the Data Adapter Dashboard Component Does not pop out this issue.

Nikpowar97_0-1680679189061.jpeg

Business Rule:

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()
dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
Dim Count As Integer = dt.Constraints.Count
BRApi.ErrorLog.LogMessage(si, "Count: here2")     ' Does not generate the error is coming on line in bold
Return dt
End Using

0 Kudos

Go to solution
ChristianW
Valued Contributor

‎04-05-2023 05:15 AM

Maybe it is just a typo?

Appendline() is quite unhandy for long sql statements. Alternatively, you can store the sql in a file (in a dashboard file, or in OS file structure) and access it via a BrApi call.

 

0 Kudos

Go to solution
JackLacava
Community Manager
Community Manager
In response to ChristianW

‎04-05-2023 08:59 AM

I worked with his query in SQL Studio, removing the WHERE clauses, and it runs. Runs also in SQL Data Adapter. Then I dropped it into an executeSql call and I got his error. I guess it's some funky option our wrapping api sets, and we have no way to override it.

0 Kudos

Go to solution
mkohorst
New Contributor II

‎04-05-2023 08:14 AM

Isn't it this? dt.Constraints.Clear

0 Kudos

Go to solution
JackLacava
Community Manager
Community Manager
In response to mkohorst

‎04-05-2023 08:46 AM

The problem is that anything done before executeSql is effectively thrown away, to be replaced with the datatable that the function returns.

0 Kudos

Go to solution
mkohorst
New Contributor II

‎04-05-2023 08:35 AM

Actually agree with Daniel, normally you just do something like this: 

Dim dt As DataTable = Nothing
Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)
End Using

Or this: 

Using dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString, False)

End Using

0 Kudos

Go to solution
JackLacava
Community Manager
Community Manager
In response to mkohorst

‎04-05-2023 08:47 AM

Fails with that too, I checked.

0 Kudos

Go to solution
OS_Pizza
Contributor III

‎04-05-2023 08:47 AM

1. Use Try and catch to put up an exception when the error occurs.

2. Use datatable.Geterrors method to print the outlier.

 

0 Kudos

Go to solution
JackLacava
Community Manager
Community Manager
In response to OS_Pizza

‎04-05-2023 09:19 AM

I tried that, but getErrors returns an empty row. That's probably because we're not manipulating the datatable, but rather just replacing it with a new instance that executesql returns.

0 Kudos

Go to solution
JackLacava
Community Manager
Community Manager

‎04-05-2023 08:56 AM

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.

0 Kudos

Go to solution
Nikpowar97
New Contributor III
In response to JackLacava

‎04-05-2023 10:06 AM

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.

 

 

0 Kudos

Go to solution
DanielWillis
Contributor III
In response to Nikpowar97

‎04-05-2023 07:01 PM

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

 

 

0 Kudos

Go to solution
Nikpowar97
New Contributor III
In response to DanielWillis

‎04-06-2023 11:53 AM

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

 

 

0 Kudos

Go to solution
DanielWillis
Contributor III

‎04-07-2023 01:35 AM

Yeah I was saying the string/string builder difference was irrelevant. If you paste my entire code to a rule does it work?

 

0 Kudos

Go to solution
Nikpowar97
New Contributor III
In response to DanielWillis

‎04-22-2023 04:37 AM - edited ‎04-27-2023 03:02 AM

Hey Daniel, Yes I tried. It didn't not work. If you have worked with Register tables (XFW_PLP_Register/TLP,etc..)  I have multiple RegiterdIDs in the table and I am cross joining in with the CalcPlanDetail. THe ExecuteSql command throws an error after I do a cross join Hence it seems to be an foreign key Constraint on the CalcPlanDetail when referring multiple records in the Primary table. Maybe you should try adding multiple Records in the RegisterID column with different RegsiterIDInstances and do a cross join with the CalcPlanTable maybe you can replicate this issue. It should work on the Data Adapter but not on the Executesql command. 🙂

0 Kudos
Please sign in! Nikpowar97
Community Resources
Training and Documentation
Additional Resources