sql视图:
单价是decimal类型,需要通过公式计算出来,现在显示的是字符串,公式不计算,我希望得到计算出来的decimal类型。
ELECT T.QuoteDetId,
T.QuoteID,
T.QuoteNO,
T.MaterialID,
T5.MaterialNO,
T.BatchID,
T.IsBatch,
T.UnitPrice as '单价' ,
T6.Formula, --这是公式
replace(replace( replace(replace(T6.Formula,'单价',T.UnitPrice),'length',T5.length),'Width',T5.Width),'Weight',T5.Weight) as '价格',
T.Currency,
T.ExchangeRate,
T.ActPeriodDay,
T.Remark,
T.InquireID,
T.InquireNO,
T.InqDetID,
T.SelectFlag,
T2.IsBySupplier,
T3.BussinessId,
T3.ApproveStatus,
T3.CreateByID,
T3.ApproveEndDt
FROM dbo.MPS_QuoteDetail AS T
INNER JOIN dbo.MPS_Quote AS T2 ON T.QuoteID = T2.QuoteID and T.IsBatch=1
INNER JOIN dbo.MPS_Inquire AS T3 ON T3.InquireID = T.InquireID
INNER JOIN dbo.MPS_InquireDetail AS T4 ON T4.InquireID = T3.InquireID
AND T.InqDetID=T4.InqDetID
AND T2.ApproveStatus = 1
AND T.UnitPrice>0
INNER JOIN dbo.MPS_Material T5 on T5.BatchID=T4.BatchID
and T4.Brand=T5.Brand
and T5.Weight between T4.WeightMin and T4.WeightMax
and T5.IsParticular=T4.IsParticular
inner join MPS_BatchSet T6 on T5.BatchID=T6.BatchID
运行结果,单价列,显示如下:
7470.0000*1194.000*889.000*190/2000000000,是字符串,不是我希望得到的数值类型。
有两种解决方案:
1,使用标量值函数,将字符串拼接成表达式,然后计算结果;
2,使用动态SQL,返回计算结果
能具体些吗?