我有一个烦人的问题，这使我无法生成一些数据；SQL作业共有23个步骤，但在21日失败。
-- Step 21 Create the table z1QReportOverview
-- Create z1QReportProjectOverview.sql
-- Project Overview - By Category (Part 4).sql
USE database
SELECT z1QReportProjectOverview1.[ERA Category] AS Category,
z1QReportProjectOverview1.[Total Projects Signed],
z1QReportProjectOverview1.[Total Spend Under Review],
z1QReportProjectOverview1.[Avg. Project Size],
z1QReportProjectOverview2.[Work in Progress],
z1QReportProjectOverview2.[Implemented],
z1QReportProjectOverview2.[No Savings],
z1QReportProjectOverview2.[Lost],
CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed]) AS [Loss Ratio],
z1QReportProjectOverview2.[Completed],
(
CONVERT(decimal(18,0),([Completed]+[Implemented]))/
CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost]))
)
AS [Success Ratio],
z1QReportProjectOverview3.[Avg. Spend] AS [Average Spend],
z1QReportProjectOverview3.[Avg. Savings] AS [Average Savings],
z1QReportProjectOverview3.[Avg. Savings %] AS [Average Savings %]
INTO dbo.z1QReportProjectOverview
FROM dbo.z1QReportProjectOverview1
JOIN dbo.z1QReportProjectOverview2
ON (z1QReportProjectOverview1.[ERA Category] = z1QReportProjectOverview2.[ERA Category])
JOIN dbo.z1QReportProjectOverview3
ON (z1QReportProjectOverview2.[ERA Category] = z1QReportProjectOverview3.[ERA Category])
ORDER BY Category
我相信我知道是什么导致零除错误。
“丢失”字段由三个字段组成，在某些情况下（非常少见），所有三个字段均为0，导致“丢失”字段中的值为0。
我相信这是导致错误的主要原因，但是那里还有第二个部门，我对SQL非常不满意，因此我的问题是：
我应该在哪里放置CASE WHEN子句？
-极有可能也写错了这一点:(““ [丢失] = 0 SET [已签名的项目总数] = 0时的情况”
任何建议深表感谢！
您可以使用CASEas来检查除数是否为0。
CASE WHEN CONVERT(decimal(18,0),[Lost]) <> 0 THEN
CONVERT(decimal(18,0),[Lost])/CONVERT(decimal(18,0),[Total Projects Signed])
ELSE 0 END AS [Loss Ratio],
z1QReportProjectOverview2.[Completed],
CASE WHEN CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost])) <> 0 THEN
(CONVERT(decimal(18,0),([Completed]+[Implemented]))/CONVERT(decimal(18,0),([Completed]+[Implemented]+[Lost])))
ELSE 0 END AS [Success Ratio],
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句