sap sql 中case 另一种使用方法

最新推荐文章于 2024-08-20 15:00:00 发布

lijian260

最新推荐文章于 2024-08-20 15:00:00 发布

阅读量1.2w

点赞数

文章标签： sap sql join 2010

本文链接：https://blog.csdn.net/lijian260/article/details/7029823

版权

select   year(t1.docdate) as iyear, Month(t1.docdate) as imonth, t1.docentry,'应收贷项凭证' type1, t1.docdate,
                 t0.itemcode, t0.dscription, t0.quantity ,--isnull(t2.avgprice,0) avgprice
case
when year(odln.docdate)=2010 and month(odln.docdate)=7
then (select isnull(AvgPrice,0) as AvgPrice from [t_ZKMCAVGCOST] where iYear=2010 and iMonth=8 and ItemCode=t0.ItemCode)
else isnull(t2.avgprice,0)
end as avgprice
    from rin1    t0 inner join orin t1 on t0.docentry = t1.docentry
--
                       inner join inv1      on t0.baseentry = inv1.docentry and t0.baseline =inv1.lineNum      --应收发票子表
                       inner join oinv      on oinv.docentry = inv1.docentry                                   --应收发票
                       inner join dln1      on inv1.baseEntry = dln1.docentry and inv1.Baseline = dln1.lineNum --交货子表
                       inner join odln      on dln1.docentry = odln.docentry
                       inner join oitm      on oitm.itemcode =t0.itemcode
                       left join [t_ZKMCAVGCOST] t2 on t0.itemcode=t2.itemcode
                and t2.iyear= year(odln.docdate) and t2.imonth=month(odln.docdate)
       where   year(t1.docdate) ='2010' and month(t1.docdate) ='9'
               and month(odln.docdate)<>month(t1.docdate) and ItmsGrpCod =101 --不是本月发货的

其中：

t2.iyear= year(odln.docdate) and t2.imonth=month(odln.docdate)

在追贷项凭证的时候追到发货月是 7月份，而 ZKMCAVGCOST表的内容是从8月份开始的没有7月份的值，有7月份的值的时候看成8月份的值。

从而写了这个：

case
when year(odln.docdate)=2010 and month(odln.docdate)=7
then (select isnull(AvgPrice,0) as AvgPrice from [t_ZKMCAVGCOST] where iYear=2010 and iMonth=8 and ItemCode=t0.ItemCode)
else isnull(t2.avgprice,0)
end as avgprice