August 30, 2010 at 11:13 am
Hi,
I am new with ComputedColumns.
There are 2 errors that I get when creating computed colums (Persisted) --
step1
first a created a UDF and i get the follwing error
Msg 4512, Level 16, State 3, Procedure UdfGetMonthlyAmount, Line 77
Cannot schema bind function 'dbo.UdfGetMonthlyAmount' because name 'Subscriber.Reference.Period' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
It says there is a problem in this stmnt - SELECT @getFreqText = Name FROM Subscriber.Reference.Period WHERE Id = @frequencyId
The UDF is as following:
USE [Subscriber]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[UdfGetMonthlyAmount]
(
@frequencyId int,
@amount money
)
RETURNS money
WITH SCHEMABINDING
AS
BEGIN
DECLARE @getFreqText varchar(38)
DECLARE @monthlyAmount money
--AVG DAYS = (365/12)
--AVG WEEKS
DECLARE @avgDays money = 30.4167
DECLARE @avgWeeks money = 4.3452
SELECT @getFreqText = Name FROM Subscriber.Reference.Period WHERE Id = @frequencyId
IF (@getFreqText = 'Daily')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) * @avgDays)
END
ELSE
IF (@getFreqText = 'Weekly')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) * @avgWeeks)
END
ELSE
IF (@getFreqText = 'Bi-Weekly')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) * (@avgDays /2))
END
ELSE
IF (@getFreqText = 'Semi-Monthly')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) * 2)
END
ELSE
IF (@getFreqText = 'Bi-Monthly')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) / 2)
END
ELSE
IF (@getFreqText = 'Quarterly')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) / 3)
END
ELSE
IF (@getFreqText = 'Semi-Yearly')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) / 6)
END
ELSE
IF (@getFreqText = 'Yearly')
BEGIN
SET @monthlyAmount = (SELECT ISNULL(@amount,0) / 12)
END
RETURN @monthlyAmount
END
Step2
To fix this, i took out the db name and left it schema.tablename --> SELECT @getFreqText = Name FROM Reference.Period WHERE Id = @frequencyId
Then i ran the alter stamnt to update my table.
GO
USE Subscriber
GO
Alter Table subscriber.budget.SubscriberBudgetItem
ADD MonthlyAmount As dbo.UdfGetMonthlyAmount(frequencyId,Amount) persisted
go
The moment i do this, this is the error that is shown.
Msg 4934, Level 16, State 3, Line 1
Computed column 'MonthlyAmount' in table 'SubscriberBudgetItem' cannot be persisted because the column does user or system data access.
Any ideas what am i doing wrong.
August 30, 2010 at 1:06 pm
I don't know for sure but it may be because of the ID - typeName lookup from another table. It seems to me that those values would never be something that should change anyway, so why risk the overhead and or the data integrity issue?
I would re-write your function to work like this:
set @monthlyAmount = case @ID
when = 1 then ISNULL(@amount,0) * @avgDays
when = 2 then ISNULL(@amount,0) * @avgWeeks
"
"
" etc. etc.
else ISNULL(@amount,0) / 12
end
This is just how I would do this... its just a calculation based on a typeID, and being a function it needs to be very efficient so why do all the extra data access.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply