报表执行时提示临时表已经存在,但是DROP时又没有? 请问是怎么回事。代码如下: 太长了,分开发
------------------------------
--客户库收发存表(按客户时间)
------------------------------
if exists (select * from sysobjects where id = object_id(N'[dbo].[#IcBal]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#IcBal]Declare @Fyear int
Declare @Fperiod int Set @Fyear=2008 --Set @zFyear=''
Set @Fperiod=6 --Set @zFyear='' select * from object_id --select * from #IcBalIf @Fyear=2008 and @Fperiod=6
Begin
--期初余额表
Select t2.fitemid KHname,t3.fyear Fyear,t3.fperiod FPeriod,Sum(fqty) TBegin,0 as TRev,0 as TSend ,0 as TTH
into #IcBal
From t_bosicinvbalentry2 t1
left join t_organization t2 on t1.fitemid=t2.fitemid
left join t_bosicinvbalentry4 t3 on t1.fitemid=t3.fitemid
Where t1.fitemid=t2.fitemid
and t1.fyear=@Fyear
and t1.fperiod=@Fperiod
and fqty<>0
Group by t2.fitemid,t3.fyear ,t3.fperiod
Union all
--送货
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,Sum(fqty) TRev,0 as TSend ,0 as TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fdcstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType=41
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
Union all
--挂账
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,0 as TRev,Sum(fqty) TSend ,0 as TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fdcstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType=21
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
Union all
--退货
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,0 as TRev, 0 as TSend,Sum(fqty) TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fscstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType in (24,41)
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
--首先清除结存表中期间相同的数据 Select * from t_bosicinvbalentry3
Delete from t_bosicinvbalentry3 Where fyear=@Fyear and fperiod=@Fperiod--将本期数据插入到结存表中
Insert Into t_bosicinvbalentry3 (fid,fitem2,fbegin,fre,fsend,fth,fend,fyear,fperiod,fdatebg1,fdateend1)
Select 1002,KHname,Sum(TBegin) ,Sum(TRev) ,Sum(TSend) ,Sum(TTH) ,Sum(TBegin)+Sum(TRev)-Sum(TSend)-Sum(TTH) ,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend
From #IcBal
left join t_bosicinvbalentry4 t1 on #IcBal.KHname=t1.fitemid and t1.fyear=@Fyear and t1.fperiod=@Fperiod
Group by #IcBal.KHname,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend--将本期期末插入到结存表中作为下期期初数
Insert Into t_bosicinvbalentry3 (fid,fitem2,fbegin,fyear,fperiod)
Select 1002,KHname,Sum(TBegin)+Sum(TRev)-Sum(TSend)-Sum(TTH) ,@Fyear,@Fperiod+1
From #IcBal
left join t_bosicinvbalentry4 t1 on #IcBal.KHname=t1.fitemid and t1.fyear=@Fyear and t1.fperiod=@Fperiod
Group by #IcBal.KHname,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend
--删除临时表
Drop table #IcBal--显示结果 Select * from t_bosicinvbalentry3
Select t2.fshortname 客户名称,t1.fyear 年度, t1.fperiod 期间, t1.fdatebg1 本期起始日,t1.fdateend1 本期结束日,t1.fbegin 期初结存,t1.fre 本期送货, t1.fsend 本期挂账, t1.fth 本期退货,t1.fend 期末结存
From t_bosicinvbalentry3 t1 ,t_organization t2
Where t1.fitem2=t2.fitemid
and t1.fyear=@Fyear and t1.fperiod=@Fperiod End
------------------------------
--客户库收发存表(按客户时间)
------------------------------
if exists (select * from sysobjects where id = object_id(N'[dbo].[#IcBal]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#IcBal]Declare @Fyear int
Declare @Fperiod int Set @Fyear=2008 --Set @zFyear=''
Set @Fperiod=6 --Set @zFyear='' select * from object_id --select * from #IcBalIf @Fyear=2008 and @Fperiod=6
Begin
--期初余额表
Select t2.fitemid KHname,t3.fyear Fyear,t3.fperiod FPeriod,Sum(fqty) TBegin,0 as TRev,0 as TSend ,0 as TTH
into #IcBal
From t_bosicinvbalentry2 t1
left join t_organization t2 on t1.fitemid=t2.fitemid
left join t_bosicinvbalentry4 t3 on t1.fitemid=t3.fitemid
Where t1.fitemid=t2.fitemid
and t1.fyear=@Fyear
and t1.fperiod=@Fperiod
and fqty<>0
Group by t2.fitemid,t3.fyear ,t3.fperiod
Union all
--送货
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,Sum(fqty) TRev,0 as TSend ,0 as TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fdcstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType=41
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
Union all
--挂账
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,0 as TRev,Sum(fqty) TSend ,0 as TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fdcstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType=21
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
Union all
--退货
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,0 as TRev, 0 as TSend,Sum(fqty) TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fscstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType in (24,41)
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
--首先清除结存表中期间相同的数据 Select * from t_bosicinvbalentry3
Delete from t_bosicinvbalentry3 Where fyear=@Fyear and fperiod=@Fperiod--将本期数据插入到结存表中
Insert Into t_bosicinvbalentry3 (fid,fitem2,fbegin,fre,fsend,fth,fend,fyear,fperiod,fdatebg1,fdateend1)
Select 1002,KHname,Sum(TBegin) ,Sum(TRev) ,Sum(TSend) ,Sum(TTH) ,Sum(TBegin)+Sum(TRev)-Sum(TSend)-Sum(TTH) ,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend
From #IcBal
left join t_bosicinvbalentry4 t1 on #IcBal.KHname=t1.fitemid and t1.fyear=@Fyear and t1.fperiod=@Fperiod
Group by #IcBal.KHname,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend--将本期期末插入到结存表中作为下期期初数
Insert Into t_bosicinvbalentry3 (fid,fitem2,fbegin,fyear,fperiod)
Select 1002,KHname,Sum(TBegin)+Sum(TRev)-Sum(TSend)-Sum(TTH) ,@Fyear,@Fperiod+1
From #IcBal
left join t_bosicinvbalentry4 t1 on #IcBal.KHname=t1.fitemid and t1.fyear=@Fyear and t1.fperiod=@Fperiod
Group by #IcBal.KHname,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend
--删除临时表
Drop table #IcBal--显示结果 Select * from t_bosicinvbalentry3
Select t2.fshortname 客户名称,t1.fyear 年度, t1.fperiod 期间, t1.fdatebg1 本期起始日,t1.fdateend1 本期结束日,t1.fbegin 期初结存,t1.fre 本期送货, t1.fsend 本期挂账, t1.fth 本期退货,t1.fend 期末结存
From t_bosicinvbalentry3 t1 ,t_organization t2
Where t1.fitem2=t2.fitemid
and t1.fyear=@Fyear and t1.fperiod=@Fperiod End
解决方案 »
- SQL只要数字
- 一个表上能否同时建立 Instead Of 和 After update,Insert触发器
- insert 关联表?
- 关于一条sql的优化问题,在线等
- 關於對Excel導入數據之問題??
- SQL Server 2005移植MySQL的问题 请各位不吝赐教
- 急:数据库错误:进程 90 发生了严重的异常 c0000005 EXCEPTION_ACCESS_VIOLATION
- 求一反向搜索BOM的SQL语句
- 请问哪里有《数据库原理》之类的电子书籍下载?
- vc++调用SQL SERVER存储过程疑难问题,100分赠送。
- 100W数据库模糊查询统计搜索结果问题,请支招
- 求一个adventure works 数据库
续上面代码
--========================= Else If @Fyear<=2008 and @Fperiod<6
--嵌套一个判断 区分查新一个月数据还是查历史数据 如果是查新一月的数据则 取数并计算,如果是查历史数据则直接取数 (此法暂不进行)
Begin Print '本表不能查询小于2008年第6期的数据' End Else
--@Fyear>=2008 and @Fperiod>6
--and @Fperiod<=(Select max(fperiod)+1 From t_bosicinvbalentry3) -- Select * from t_bosicinvbalentry3
Begin --期初余额表
Select t2.fitemid KHname,t3.fyear Fyear,t3.fperiod FPeriod,Sum(fqty) TBegin,0 as TRev,0 as TSend ,0 as TTH
into #IcBal
From t_bosicinvbalentry3 t1
left join t_organization t2 on t1.fitem2=t2.fitemid
left join t_bosicinvbalentry4 t3 on t1.fitem2=t3.fitemid
Where t1.fitemid=t2.fitemid
and t1.fyear=@Fyear
and t1.fperiod=@Fperiod
and fqty<>0
Group by t2.fitemid,t3.fyear ,t3.fperiod
Union all
--送货
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,Sum(fqty) TRev,0 as TSend ,0 as TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fdcstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType=41
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
Union all
--挂账
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,0 as TRev,Sum(fqty) TSend ,0 as TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fdcstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType=21
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
Union all
--退货
Select t4.fitemid KHname,t5.fyear Fyear,t5.fperiod FPeriod,0 as TBegin,0 as TRev, 0 as TSend,Sum(fqty) TTH
From icstockbillentry t1
left join icstockbill t2 on t1.finterid=t2.finterid
left join t_stock t3 on t1.fscstockid=t3.fitemid
left join t_organization t4 on t3.f_104=t4.fitemid
left join t_bosicinvbalentry4 t5 on t4.fitemid=t5.fitemid --and t5.fyear=@Fyear and t5.fperiod=@Fperiod
Where FTranType in (24,41)
and fcancellation=0
and fdate>=t5.fdatebg and fdate<=t5.fdateend
and t5.fyear=@Fyear and t5.fperiod=@Fperiod
and t3.fnumber like '01.5.%' and fqty<>0
Group by t4.fitemid ,t5.fyear ,t5.fperiod
--首先清除结存表中期间相同的数据 Select * from t_bosicinvbalentry3
Delete from t_bosicinvbalentry3 Where fyear=@Fyear and fperiod=@Fperiod--将本期数据插入到结存表中
Insert Into t_bosicinvbalentry3 (fid,fitem2,fbegin,fre,fsend,fth,fend,fyear,fperiod,fdatebg1,fdateend1)
Select 1002,KHname,Sum(TBegin) ,Sum(TRev) ,Sum(TSend) ,Sum(TTH) ,Sum(TBegin)+Sum(TRev)-Sum(TSend)-Sum(TTH) ,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend
From #IcBal
left join t_bosicinvbalentry4 t1 on #IcBal.KHname=t1.fitemid and t1.fyear=@Fyear and t1.fperiod=@Fperiod
Group by #IcBal.KHname,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend--将本期期末插入到结存表中作为下期期初数
Insert Into t_bosicinvbalentry3 (fid,fitem2,fbegin,fyear,fperiod)
Select 1002,KHname,Sum(TBegin)+Sum(TRev)-Sum(TSend)-Sum(TTH) ,@Fyear,@Fperiod+1
From #IcBal
left join t_bosicinvbalentry4 t1 on #IcBal.KHname=t1.fitemid and t1.fyear=@Fyear and t1.fperiod=@Fperiod
Group by #IcBal.KHname,t1.Fyear,t1.FPeriod,t1.fdatebg,t1.fdateend
--删除临时表
Drop table #IcBal--显示结果
Select t2.fshortname 客户名称,t1.fyear 年度, t1.fperiod 期间, t1.fdatebg1 本期起始日,t1.fdateend1 本期结束日,t1.fbegin 期初结存,t1.fre 本期送货, t1.fsend 本期挂账, t1.fth 本期退货,t1.fend 期末结存
From t_bosicinvbalentry3 t1 ,t_organization t2
Where t1.fitem2=t2.fitemid
and t1.fyear=@Fyear and t1.fperiod=@Fperiod
End --内判断结束
if object_id('Tempdb..#IcBal') is not null
drop table #IcBal--删除临时表
if object_id('Tempdb..#IcBal') is not null
Drop table #IcBal
go
If Object_Id('tempdb..#1') Is Not null
Drop Table #1Select * Into #1 From sys.tablesIf Object_Id('tempdb..#1') Is Not null
Drop Table #1Select * Into #1 From sys.tables/*
消息 2714,级别 16,状态 1,第 9 行
数据库中已存在名为 '#1' 的对象。
*/除非你之间加了go。
判断并drop就可以了.
if object_id('Tempdb..#IcBal') is not null
drop table #IcBal
drop table #IcBal建议搂住先 create table #IcBal
()..然后用INSERT INTO
试一试就可以了 !
1、
if exists (select * from sysobjects where id = object_id(N'[dbo].[#IcBal]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[#IcBal]
后立即建临时表:create table #icbal(...)
后面的select ... into #icbal 改为 insert @icbal select ...2、
将你的第一个select ... into #icbal 改为 select ... into #icbal1,并将后面的相应修改。
将你的第二个select ... into #icbal 改为 select ... into #icbal2,并将后面的相应修改。