将udf代码插入查询中 [英] insert udf code into query

问题描述

喜群组。试图摆脱用户定义的函数..这里是旧查询...

hi.  trying to get rid of a user defined function.. here is old query...

select
L.loanid,
OriginalAmortTerm = Case 
When (L.field1 is not NULL or  ((LO.Field2*IsNull(AN.THREE, L.FOUR)/12)- Case When LO.OrigPI = 0 Then L.FIVE Else LO.SIX End) >-1)
Then DateDiff(m, LO.SEVEN, L.EIGHT) + 1 
Else smd.dbo.udf_Amort(IsNull(AN.THREE, L.FOUR), 
Case 
When LO.OrigPI = 0 
Then L.FIVE Else LO.OrigPI End, LO.Field2) 
End

FROM
table

现在我想换掉&bbsp
smd.dbo.udf_Amort（IsNull（AN.THREE，L.FOUR）部分..这里是我到目前为止...但我认为某处有一个支架丢失。不确定......

Now I want to rplace the 
smd.dbo.udf_Amort(IsNull(AN.THREE, L.FOUR) part.. Here is what I have so far.. but I think there is a bracket missing somewhere.  Not sure...

select
L.loanid,
OriginalAmortTerm = Case 
When (L.field1 is not NULL or  ((LO.Field2*IsNull(AN.THREE, L.FOUR)/12)- Case When LO.OrigPI = 0 Then L.FIVE Else LO.SIX End) >-1)
Then DateDiff(m, LO.SEVEN, L.EIGHT) + 1 
Else

WHEN IsNull(AN.THREE, L.FOUR) = 0  OR L.FIVE = 0 OR L.FirstPrincipal = 0 THEN 0 --UDF_SUBSTITUTE HERE
WHEN (IsNull(AN.THREE, L.FOUR) / 12.000000) * L.FirstPrincipal >= L.FIVE THEN NULL --UDF_SUBSTITUTE HERE
ELSE convert(decimal(18,0), -Log(1 - (IsNull(AN.THREE, L.FOUR) / 12.000000) * L.FirstPrincipal / L.FIVE) / Log(1 + (IsNull(AN.THREE, L.FOUR) / 12.000000))), --UDF_SUBSTITUTE HERE


Case 
When LO.OrigPI = 0 
Then L.FIVE Else LO.OrigPI End, LO.Field2)
End

FROM
table

请帮助

提前致谢

推荐答案

--this?
select
L.loanid,
OriginalAmortTerm = Case 
When (L.field1 is not NULL or  ((LO.Field2*IsNull(AN.THREE, L.FOUR)/12)- Case When LO.OrigPI = 0 Then L.FIVE Else LO.SIX End) >-1)
Then DateDiff(m, LO.SEVEN, L.EIGHT) + 1 
Else
CASE
WHEN IsNull(AN.THREE, L.FOUR) = 0  OR L.FIVE = 0 OR L.FirstPrincipal = 0 THEN 0 --UDF_SUBSTITUTE HERE
WHEN (IsNull(AN.THREE, L.FOUR) / 12.000000) * L.FirstPrincipal >= L.FIVE THEN NULL --UDF_SUBSTITUTE HERE
ELSE convert(decimal(18,0), -Log(1 - (IsNull(AN.THREE, L.FOUR) / 12.000000) * L.FirstPrincipal / L.FIVE) / Log(1 + (IsNull(AN.THREE, L.FOUR) / 12.000000)))
END
END
, --UDF_SUBSTITUTE HERE
 
Case 
When LO.OrigPI = 0 
Then L.FIVE Else LO.OrigPI End, LO.Field2)
End

这篇关于将udf代码插入查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！