October 10, 2002 at 4:40 pm
I have a following update statement in SP and I am sure that it can be tidyed up i just don't know how.
There are three functions:
One calculates STDEV, second further manipulates STDEV, third rounds all results to set number of Significant figures.
There should be a way not to have to pass all parameters to all functions, but when I try to assign the STDEV result to a variable i get error:
"A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."
Any ideas how to make it pretty?
Thanks Jakub
Update dbo.MDL_tgtLoadX10
Set MDLStr =
Case
when dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)) >= 100 then dbo.sgfgsql(dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)),3)
when dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)) <100 then dbo.sgfgsql(dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)),2)
end,
MDL_Calc =
Case
when dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)) >= 100 then dbo.sgfgsql(dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)),3)
when dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)) <100 then dbo.sgfgsql(dbo.fMDLcalc(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10)),2)
end,
M_STDEV = dbo.sgfgsql(dbo.mSTDEV(MDL1, MDL2, MDL3, MDL4, MDL5, MDL6, MDL7, MDL8, MDL9, MDL10, USE1, USE2, USE3, USE4, USE5, USE6, USE7, USE8, USE9, USE10),4)
Where Method = @Method
October 14, 2002 at 6:07 am
I think the best approach is to store the result of the function fMStdDev in a temporary table (along with the primary key of the table). In a second phase you can join this table in your update statement, taking the necessary actions on the calculated value.
You could even store the result of fMDLCalc in that same table with the fSTDDEV to further reduce calculationtimes.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply