March 21, 2005 at 1:05 pm
I have a table that has 2 fields where the data values need to be calculated. To do this I created a UDF that does the proper calculation and binded these to their respective fields in the table. The problem is that one of the UDF's requires input from the field that gets its value from the other UDF bound to it. SQL server does not allow this giving me the message "Invalid column 'annual_requirement' is specified in a constraint or computed-column definition." So a computed column cannot be specified as an input in a UDF.
I thought about breaking the table into 2 tables having only one calculated field in each table. But can a UDF input be specified with a field in another table? I am not sure this would work either. Any help or advice regarding this would be appreciated.
March 21, 2005 at 1:14 pm
You can't use another table unless you're inside the udf or inside a trigger. Maybe you could send the parameters needed to calculate the first field in the 2nd udf and recall the 1s udf in the 2nd one (don't know if it's allowed in this context).
The only other way I see is the use permanent columns and populate them with a trigger or default values based on the udfs (but again you'd have to use a trigger to keep the info up to date).
March 22, 2005 at 7:04 am
couldn't you create a view of the table that has the results of the udf as additional columns of the table/view?
for example create view vw_sometable as
select col1,..colN,dbo.fn__special1(col5) as Result1,dbo.fn__special2 (col5,col6) as Result2,
from sometable
if one of the columns is the function of a function, it might be like this:
dbo.fn__special1(col5) as Result1,
dbo.fn__special2 (dbo.fn__special1(col5) ,col6) as Result2
but i didn't test that to see if it works yet.
Lowell
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply