sap sql 中case 另一种使用方法

 

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