我写这是为了创建过去30天的发票摘要。在检查发票时，我注意到我取消了很多空值，因为大多数客户都免税。我想放置一个IF语句（如果t.TAXABL_23 ='N'然后要加税= 1 ...在这里变成灰色）if语句会出现在这个混乱的地方，或者更好的是使用一个IF语句可以做到这一点。谢谢。
Select c.ordnum_31 as "Sales Order Number",
c.invce_31 as "Invoice Number",
c.custpo_31 as "Job Name",
c.ordid_31 as "Other Job Name",
c.invdte_31 as "Invoice Date",
cast (sum((1-(m.DSCRTE_23/100)) * (o.price_32 * o.shpqty_32)) as decimal (8,2)) as "Net Amount",
cast (c.frtamt_31 as decimal(8,2)) as Freight,
cast( ((t.TAXRTE_25 * .01) * c.TAXTOT_31) as decimal (8,2)) as Tax,
cast (c.MSCAMT_31 as decimal(8,2)) as MISC,
cast( round (sum((1-(m.DSCRTE_23/100)) * (o.price_32 * o.shpqty_32)) + c.frtamt_31 + c.MSCAMT_31 + ((t.TAXRTE_25 * .01) * c.TAXTOT_31),1 ) as decimal(8,2) ) as "Invoice Total"
from Invoice_Master c
left join Invoice_Detail o on c.ORDNUM_31 = o.ORDNUM_32
left join Customer_Master m on c.CUSTID_31 = m.CUSTID_23
left join Tax_master t on m.TXCDE1_23 = t.TAXCDE_25
where c.invdte_31 >= DATEADD(day,-30, getdate())
group by
c.ORDNUM_31,
c.CUSTID_31,
c.INVCE_31,
c.CUSTPO_31,
c.ORDID_31,
c.INVDTE_31,
c.frtamt_31,
c.taxtot_31,
m.dscrte_23,
c.MSCAMT_31,
t.taxrte_25
order by "Invoice Number"
您可能正在寻找一个CASE表达式：
...
... as Freight,
CASE WHEN t.TAXABL_23 = 'N'
THEN 0 -- exempt from taxes
ELSE ...calculate tax...
END as Tax,
... as MISC,
...
否则，如果只想将NULL转换为某个值，则可以使用ISNULL表达式：
...
ISNULL(...calculate tax..., 0) AS tax -- yields 0 when the result of the expression is NULL
...
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句