在数据集中使用sql server的表变量，由于需要查询的数据比较复杂，用存储过程不方便迁移和调试，因此写在了一起。但是执行结果提示不对，而同样的内容在sql server中执行OK
数据查询代码如下：
declare @CurrPeriodDate datetime,@CurrPeriodNum char(6)
declare @Balance table
(
FItemNum varchar(80),
FItemName varchar(255),
FItemModel Varchar(255),
FBatchNo varchar(255),
FSupplierName varchar(255),
FUnitName varchar(20),
FStockName varchar(255),
FStockPlace Varchar(255),
FStockQty decimal(28,10)
)
select @currperiodDate=STARTDATE,@CurrPeriodNum=curr.FCURRENTPERIOD
from ERP_SYSTEMTIME_INFO curr
inner join ERP_GETMATTIME_INFO sysdate on curr.FCURRENTPERIOD=sysdate.PERIODNUM
where curr.id=1
if @CurrPeriodDate<'${DateFrom}'
insert into @balance(FItemNum,FItemName,FItemModel,FSupplierName,FBatchNo,FUnitName,FStockName,FStockPlace,FStockQty)
select bal.MATERIEL,bal.MATERIELNAME,bal.SPECIFICATIONS,st.SUPPLIER,bal.BATCH,bal.Unit,bal.PLOCATION,bal.LOCATIONUM,bal.QIKUCUN
from ERP_STARTKC_YUE bal
left join CK_STORAGELOCATION_INFO st on bal.MATERIEL=st.MATERIEL and bal.BATCH=st.BATCHNUMBER
where bal.QIJIANTIME=@CurrPeriodNum
insert into @balance(FItemNum,FItemName,FItemModel,FSupplierName,FBatchNo,FUnitName,FStockName,FStockPlace,FStockQty)
select
bal.MATERIEL,bal.MATERIELNAME,bal.SPECIFICATIONS,SUPPLIER,BATCHNUMBER,bal.Unit,bal.StockName,StockPlace,sum(bal.Qty)
from
(
select
MATERIEL
,PRODUCTNAME MATERIELNAME
,SPECIFICATIONS
,SUPPLIER
,BATCH BATCHNUMBER
,MEASUREMENTUNIT unit
,WAREHOUSETYPE StockName
,WAREHOUSECODING StockPlace
,sum(MINSTORAGETOTAL)Qty
from CK_WAREHOUSE_INSTOCK_INFO ----采购入库
where 1=1
AND DATEOFSTORAGE>=@CurrPeriodDate
AND DATEOFSTORAGE<'${DateFrom}'
group by MATERIEL
,PRODUCTNAME
,SPECIFICATIONS
,SUPPLIER
,BATCH
,MEASUREMENTUNIT
,WAREHOUSETYPE
,WAREHOUSECODING
UNION ALL
select
MATERIEL
,MATERIELNAME
,PRODUCTS
,'' SUPPLIER
,BATCHNUMBER
,unit
,stp.HWTYPE
,zz.LOCATIONUM
,sum(INSTOCKNUM)Qty
from PROT_PRODUCTINWAREHOUSE_INFO zz----成品入库
left join CK_GOODSSHELF_INFO stp on zz.LOCATIONUM=stp.RESERVEL3
where 1=1
AND zz.EDITTIME>=@CurrPeriodDate
AND zz.EDITTIME<'${DateFrom}'
group by MATERIEL
,MATERIELNAME
,PRODUCTS
,BATCHNUMBER
,UNIT
,stp.HWTYPE
,zz.LOCATIONUM
) bal
group by MATERIEL,MATERIELNAME,SPECIFICATIONS,BATCHNUMBER,SUPPLIER,Unit,StockName,StockPlace
select FItemNum,FItemName,FItemModel,FSupplierName,FBatchNo,FUnitName,FStockName,FStockPlace,FStockQty from @balance
报表系统提示：
119392
SQL Server自己执行，有返回结果
119393
