金蝶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           

提取效果如下：