案例语句中的 SQL 除以零错误和语法错误

阿尔斯兰·丁·沙菲克

我正在用 SQL 生成报告。我有一个除以零的错误。为了解决这个错误，我在 SQL 查询中使用了 case。现在我在“as”处收到语法错误。有没有人可以帮我解决这个问题？

create procedure dbo.CompanyAverageSales @startdate as varchar(50),@enddate 
as varchar(50)
As begin

select  datename(year,SO_SalesOrder.InvoicedDate) as [Year],
    datename(month,SO_SalesOrder.InvoicedDate) as [Month],
    l.Name as [Store Name], 
    case when sum(convert(int,SO_SalesOrder.Custom1))>0 then sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) as [Average Sales]
From SO_SalesOrder
inner join BASE_Location l on SO_SalesOrder.LocationId = l.LocationId 
inner join SO_SalesOrder_Line
on SO_SalesOrder.SalesOrderId = SO_SalesOrder_Line.SalesOrderId
inner join BASE_PaymentTerms
on BASE_PaymentTerms.PaymentTermsId = SO_SalesOrder.PaymentTermsId
where SO_SalesOrder.InvoicedDate >= @startdate and SO_SalesOrder.InvoicedDate <= @enddate
group by l.Name,
         datename(year,SO_SalesOrder.InvoicedDate),
         datename(month,SO_SalesOrder.InvoicedDate)


end

杰伊·香卡·古普塔

添加END之前AS

case 
when sum(convert(int,SO_SalesOrder.Custom1))>0 
then 
    sum(SO_SalesOrder_Line.SubTotal)/sum(convert(int,SO_SalesOrder.Custom1)) 
Else 
    0
END as [Average Sales]

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。