金蝶K3即时库存数据提取语句

select 
t1.fnumber as F1I31128l9507457,--物料编码
t1.fname Ff131K1281030445,--物料名称
t.fbatchno as Fe1R31128949515,--批号
t3.fname as F18l3112895P126,--仓库
t4.fname as FTRP4NM,--仓位
t.fqty as F13g18212895J144,--库存
case when t.FKFDate='' then null else CONVERT(datetime,t.FKFDate,120) end FIH7GS6,--生产采购日期
case when t.FKFPeriod=0 then null else t.FKFPeriod end FF8QOSP,--保质期
case when t.FKFDate='' then null else dateadd(d,t.FKFPeriod,t.FKFDate) end FFBPPF8,--到期日
t6.FName FBAHI4F--基本计量单位
FROM AIS20141103101148.[dbo].icinventory t
INNER JOIN AIS20141103101148.[dbo].t_ICItem t1 ON t1.FItemID = t.FItemID
INNER JOIN AIS20141103101148.[dbo].t_Stock t3 ON t3.FItemID = t.fstockid
INNER JOIN AIS20141103101148.[dbo].t_StockPlace t4 ON t4.FSPID = t.FStockPlaceID
inner join AIS20141103101148.[dbo].t_ICItem t5 on t5.FItemID=t.FItemID
inner join AIS20141103101148.[dbo].t_MeasureUnit t6 on t6.FItemID=t5.FUnitID
WHERE t.fqty > 0

提取效果如下：