it works with simple select from where queries. but fails in queries like "SELECT Changes.ChangeType, Changes.Workflow, Count(*) AS NoOfChanges, Round(Avg(Changes.WorkflowDuration),2) AS AvgWorkflowDuration
FROM Changes INNER JOIN RelevantChanges ON Changes.ChangeNumber = RelevantChanges.ChangeNumber
GROUP BY Changes.ChangeType, Changes.Workflow;"
(i get "user lacks privilege or object not found")
i assume it is connected to one of the functions although if i hard code this query without a saved query it works fine
any ideas?
Last edit: david avraham 2016-05-10
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Im trying to use "SELECT * FROM <MYSAVEDQUERY>"
it works with simple select from where queries. but fails in queries like "SELECT Changes.ChangeType, Changes.Workflow, Count(*) AS NoOfChanges, Round(Avg(Changes.WorkflowDuration),2) AS AvgWorkflowDuration
FROM Changes INNER JOIN RelevantChanges ON Changes.ChangeNumber = RelevantChanges.ChangeNumber
GROUP BY Changes.ChangeType, Changes.Workflow;"
(i get "user lacks privilege or object not found")
i assume it is connected to one of the functions although if i hard code this query without a saved query it works fine
any ideas?
Last edit: david avraham 2016-05-10
AVG doesn't allow null values here. Use
Round(Avg(Nz(Changes.WorkflowDuration,0.0)),2)