查询当前及上一年发票数据时触发除零错误的问题求助

同时查询当前年度及上一年度数据时触发除零错误，但单独查询任意一个年度的数据都能正常运行。

Select FISCYR,
  LTrim(RTrim(FISCPER)) as 'Period',
  Customer,
  ORDNUMBER,
  LTrim(RTrim(PONUMBER)) as 'PO Number',
  Item,
  LTrim(RTrim([DESC])) as 'Product',
  Cast(QTY as Decimal(18,2)) As 'QTY',
  CAST(EXTINVMISC/EXTWEIGHT as Decimal(18,2)) as 'Price (Lb)',
  LTrim(RTrim(LOTNUMF)) as 'Lot Num',
  LTrim(RTrim(Location)) as 'Location',
  LTrim(RTrim(VIADESC)) as 'Ship Via'  
From DBO.viewinvoicehistory
Where TRANSTYPE=1
  and fiscyr = Year(GETDATE()) 
   or fiscyr = year(DATEADD(YEAR, -1, GETDATE()))
  and CUSTNAME='LOBLAW COMPANIES LIMITED.'
Order By FiscYr DESC,LTrim(RTrim(FISCPER)) DESC

1. 逻辑运算符优先级错误：SQL中AND优先级高于OR，原WHERE子句实际执行逻辑是：
   • 要么满足TRANSTYPE=1且是当前年度的所有客户数据
   • 要么满足是上一年度且客户为指定值的数据
     这导致查询结果混入了其他客户的记录，其中存在EXTWEIGHT=0的行，触发除零错误。
2. 未处理除零场景：即使过滤逻辑正确，若目标数据中存在EXTWEIGHT=0的记录，直接执行除法仍会报错。

1. 用括号明确年份条件的逻辑分组，确保只查询指定客户的两年数据；
2. 使用NULLIF(EXTWEIGHT, 0)将0值转为NULL，避免除零错误，也可根据需求用ISNULL将NULL替换为0或其他默认值。

修复后的SQL代码：

Select FISCYR,
  LTrim(RTrim(FISCPER)) as 'Period',
  Customer,
  ORDNUMBER,
  LTrim(RTrim(PONUMBER)) as 'PO Number',
  Item,
  LTrim(RTrim([DESC])) as 'Product',
  Cast(QTY as Decimal(18,2)) As 'QTY',
  -- 处理除零，EXTWEIGHT为0时返回NULL，如需默认值可改为：ISNULL(CAST(EXTINVMISC/NULLIF(EXTWEIGHT, 0) as Decimal(18,2)), 0)
  CAST(EXTINVMISC/NULLIF(EXTWEIGHT, 0) as Decimal(18,2)) as 'Price (Lb)',
  LTrim(RTrim(LOTNUMF)) as 'Lot Num',
  LTrim(RTrim(Location)) as 'Location',
  LTrim(RTrim(VIADESC)) as 'Ship Via'  
From DBO.viewinvoicehistory
Where TRANSTYPE=1
  AND CUSTNAME='LOBLAW COMPANIES LIMITED.'
  AND (fiscyr = Year(GETDATE()) OR fiscyr = year(DATEADD(YEAR, -1, GETDATE())))
Order By FiscYr DESC, LTrim(RTrim(FISCPER)) DESC

内容的提问来源于stack exchange，提问作者D.Thomson