[ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-5725: --------------------------------------- Our notion of updating model count is unfortunately not the same as side-effect free. The value we compute is to determine whether a statement level transaction is required. 0 = no statement level transaction needed 1 = no statement level transaction needed if this is the only thing being called. Otherwise if it's nested in a procedure or there are other statements that may also need a transaction, then a statement level one will be started. 2 = always start a user level transaction By the way we currently compute the value the usage of the atomic block means that we don't have to consider a higher level transaction as required for those statements - we'll create a block level transaction for that. So to have the same semantics as OData is looking for we would need a secondary check. Another thing to consider is if you even need an atomic block. Are you worried about a competing update to NutritionGoal in between the select and update? It is not necessarily intended that virtual functions are represented in OData metadata at all. The only reason they are there is because when they are defined by a DDL procedure body there is procedure in our system metadata. When the OData metadata is built it is looking at procedures only - note that source functions, Teiid system functions, etc. are not represented in the OData metadata. I would like this to be more consistent than it is. In general the mapping of function to function, and procedure to action is simpler - except not all Teiid functions qualify as OData functions as they can have lob parameters and side-effects. Ramesh would have to comment more on this as he did the initial metadata mapping - [~rareddy] did you ever intend to have all Teiid functions represented in OData? Perhaps as composible? Yes that is the workaround in this case if you want it as an action. More than likely though we'd want the semantics of "side effect free" to be represented rather than documenting this as a corner case. Can you log an issue about this? You could combine both of the selects, and you may find it easier to read using a loop rather than a lot of scalar subqueries: CREATE VIRTUAL PROCEDURE copyNutritionGoalsOfWeekdayToEntireWeek(vWeekday VARCHAR(1)) RETURNS BOOLEAN AS BEGIN ATOMIC IF ( vWeekday NOT IN ('0', '1', '2', '3', '4', '5', '6') ) RETURN FALSE; --##### read the relevant parameters of the selected workday into a temporary table ##### SELECT fkProfile, BMR_Formula, BMR_Value, ActivityLevel, CaloriesBurned, WeekGoal, GoalCaloriesDelta, CarbsGoalInPercent, ProteinsGoalInPercent, FatGoalInPercent INTO #tmpTable FROM NutritionGoal WHERE fkProfile = (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36)) AND Weekday = vWeekday; IF (ROWCOUNT = 0) RETURN FALSE; --##### update the other weekdays ##### LOOP ON (SELECT * FROM #tmpTable) as cur BEGIN UPDATE NutritionGoal SET NutritionGoal.BMR_Formula = cur.BMR_Formula, NutritionGoal.BMR_Value = cur.BMR_Value, NutritionGoal.ActivityLevel = cur.ActivityLevel, NutritionGoal.CaloriesBurned = cur.CaloriesBurned, NutritionGoal.WeekGoal = cur.WeekGoal, NutritionGoal.GoalCaloriesDelta = cur.GoalCaloriesDelta, NutritionGoal.CarbsGoalInPercent = cur.CarbsGoalInPercent, NutritionGoal.ProteinsGoalInPercent = cur.ProteinsGoalInPercent, NutritionGoal.FatGoalInPercent = cur.FatGoalInPercent WHERE NutritionGoal.fkProfile = cur.vfkProfile AND NutritionGoal.Weekday != vWeekday; END RETURN TRUE; END -- This message was sent by Atlassian Jira (v7.12.1#712002)