[ https://issues.jboss.org/browse/TEIID-5725?page=com.atlassian.jira.plugin... ] Christoph John edited comment on TEIID-5725 at 4/24/19 5:05 PM: ---------------------------------------------------------------- Hello Steven, thanks this helps a lot. However, reading through your description resulted in a further question. I am not sure if I might came over a further issue with it or if I simply have a bug in my procedure. For my understanding of the description a virtual procedure with side effects should always be mapped to an action. However, the following is my virtual procedure and it is mapped to a function CREATE VIRTUAL PROCEDURE copyNutritionGoalsOfWeekdayToEntireWeek(vWeekday VARCHAR(1)) RETURNS BOOLEAN AS BEGIN ATOMIC IF ( vWeekday IN ('0', '1', '2', '3', '4', '5', '6') ) BEGIN DECLARE LONG vfkProfile = SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36); --##### read the relevant parameters of the selected workday into a temporary table ##### SELECT BMR_Formula, BMR_Value, ActivityLevel, CaloriesBurned, WeekGoal, GoalCaloriesDelta, CarbsGoalInPercent, ProteinsGoalInPercent, FatGoalInPercent INTO #tmpTable FROM NutritionGoal WHERE fkProfile = vfkProfile AND Weekday = vWeekday; --##### update the other weekdays ##### UPDATE NutritionGoal SET NutritionGoal.BMR_Formula = (SELECT BMR_Formula FROM #tmpTable), NutritionGoal.BMR_Value = (SELECT BMR_Value FROM #tmpTable), NutritionGoal.ActivityLevel = (SELECT ActivityLevel FROM #tmpTable), NutritionGoal.CaloriesBurned = (SELECT CaloriesBurned FROM #tmpTable), NutritionGoal.WeekGoal = (SELECT WeekGoal FROM #tmpTable), NutritionGoal.GoalCaloriesDelta = (SELECT GoalCaloriesDelta FROM #tmpTable), NutritionGoal.CarbsGoalInPercent = (SELECT CarbsGoalInPercent FROM #tmpTable), NutritionGoal.ProteinsGoalInPercent = (SELECT ProteinsGoalInPercent FROM #tmpTable), NutritionGoal.FatGoalInPercent = (SELECT FatGoalInPercent FROM #tmpTable) WHERE NutritionGoal.fkProfile = vfkProfile AND NutritionGoal.Weekday != vWeekday; RETURN TRUE; END RETURN FALSE; END By the way, if you see how I could write this function more efficiently, I would be grateful for a comment :) The function parameter is an enum in the underlaying db. But enum seems to be not supported in teiid. According to your description: The procedure/function is side effect free - this is determined by the inferred or explicit value of 0 for the model update count So do I need to add options to my procedure than. I do not understand than why there is a virtual function and virtual procedure specification. I would have expected to get a function for a virtual function and a action for a virtual procedure. Maybe you could also add the options to an example in the documentation to make it transparent? Would this than be OPTIONS(UPDATECOUNT '1'); ? was (Author: cjohn001): Hello Steven, thanks this helps a lot. However, reading through your description resulted in a further question. I am not sure if I might came over a further issue with it or if I simply have a bug in my procedure. For my understanding of the description a virtual procedure with side effects should always be mapped to an action. However, the following is my virtual procedure and it is mapped to a function CREATE VIRTUAL PROCEDURE copyNutritionGoalsOfWeekdayToEntireWeek(vWeekday VARCHAR(1)) RETURNS BOOLEAN AS BEGIN ATOMIC IF ( vWeekday IN ('0', '1', '2', '3', '4', '5', '6') ) BEGIN DECLARE LONG vfkProfile = SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36); --##### read the relevant parameters of the selected workday into a temporary table ##### SELECT BMR_Formula, BMR_Value, ActivityLevel, CaloriesBurned, WeekGoal, GoalCaloriesDelta, CarbsGoalInPercent, ProteinsGoalInPercent, FatGoalInPercent INTO #tmpTable FROM NutritionGoal WHERE fkProfile = vfkProfile AND Weekday = vWeekday; --##### update the other weekdays ##### UPDATE NutritionGoal SET NutritionGoal.BMR_Formula = (SELECT BMR_Formula FROM #tmpTable), NutritionGoal.BMR_Value = (SELECT BMR_Value FROM #tmpTable), NutritionGoal.ActivityLevel = (SELECT ActivityLevel FROM #tmpTable), NutritionGoal.CaloriesBurned = (SELECT CaloriesBurned FROM #tmpTable), NutritionGoal.WeekGoal = (SELECT WeekGoal FROM #tmpTable), NutritionGoal.GoalCaloriesDelta = (SELECT GoalCaloriesDelta FROM #tmpTable), NutritionGoal.CarbsGoalInPercent = (SELECT CarbsGoalInPercent FROM #tmpTable), NutritionGoal.ProteinsGoalInPercent = (SELECT ProteinsGoalInPercent FROM #tmpTable), NutritionGoal.FatGoalInPercent = (SELECT FatGoalInPercent FROM #tmpTable) WHERE NutritionGoal.fkProfile = vfkProfile AND NutritionGoal.Weekday != vWeekday; RETURN TRUE; END RETURN FALSE; END By the way, if you see how I could write this function more efficiently, I would be grateful for a comment :) According to your description: The procedure/function is side effect free - this is determined by the inferred or explicit value of 0 for the model update count So do I need to add options to my procedure than. I do not understand than why there is a virtual function and virtual procedure specification. I would have expected to get a function for a virtual function and a action for a virtual procedure. Maybe you could also add the options to an example in the documentation to make it transparent? Would this than be OPTIONS(UPDATECOUNT '1'); ? -- This message was sent by Atlassian Jira (v7.12.1#712002)