Hello everyone,
I have an InvoiceHeader table with the following definition:
InvoiceId int NOT NULL (Key)
and an InvoiceDetail table with the following definition:
DetailId int NOT NULL (Key)
InvoiceId int NOT NULL (FKey)
Packages smallint NULL
PackagePrice money NULL
Units smallint NULL
UnitPrice money NULL
Discount1 money NULL
Discount2 money NULL
I added a computed column to the InvoiceDetail table:
Value
formula:
(((CONVERT([decimal](18,6),[Packages])*CONVERT([decimal](18,6),[PackagePrice])+CONVERT([decimal](18,6),[Units])*CONVERT([decimal](18,6),[UnitPrice]))*(((100)-CONVERT([decimal](18,6),[Discount1]))/(100)))*(((100)-CONVERT([decimal](18,6),[Discount2]))/(100)))
This horrible looking formula has been much simpler, but I had to convert each and every column to allow it to be persisted.
I know the cause of that is poor design in the first place, but that is not for me to change now :(
and all these conversions finally made me able to make this column Persisted.
Then I added a function to calculate the InvoiceTotal :
ALTER function [dbo].[GetInvoiceTotal](@InvoiceID as int)
returns decimal(18,2)
as
begin
declare @total as decimal(18,2)
select @total =CONVERT(decimal(18,2) , sum(convert(decimal(18,2) ,invd.Value)))
from InvoiceDetails invd
where invd.InvoiceID = @InvoiceID
return convert(decimal(18,2) , Isnull(Convert(decimal(18,2) , @total ) , 0.00) )
end;
Then added a computed column to the InvoiceHeader table :
InvoiceTotal
as dbo.GetInvoiceTotal(InvoiceId)
but when I try to make it persisted I get an error:
cannot be persisted because the column is non-deterministic.
What I have tried:
I read the documentation at :
http://msdn.microsoft.com/en-us/library/ms178091.aspx
ISNULL is always deterministic,
All aggregate functions are deterministic unless they are specified with the OVER and ORDER BY clauses.
Issues occur with : datetime, smalldatetime, or sql_variant... none of which is used here.
I coverted the h*ll of the code to make persisted.
What am I missing ?