我的SQL语句如下:
SELECT * INTO #Temp_ViewReport067 FROM (SELECT SupTitle,QC=(SELECT ISNULL(SUM(Due),0) FROM QC_Due_200705 WHERE SysId=A.SysId)+(SELECT ISNULL(SUM(CASE BusCls WHEN '0001' THEN BusMoney-BusAgio WHEN '0002' THEN -(BusMoney-BusAgio) ELSE 0 END),0)+ISNULL(SUM(CASE BusPayType WHEN '0001' THEN -BusMoneyS WHEN '0002' THEN -BusMoneyS ELSE 0 END),0) From Jxc_Business_200705 WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate<'2007-5-10' AND BusCS=A.SysId AND BusCls IN ('0001','0002','0013')),BQZ=(SELECT ISNULL(SUM(CASE BusCls WHEN '0001' THEN BusMoney-BusAgio WHEN '0002' THEN -(BusMoney-BusAgio) ELSE 0 END),0)+ISNULL(SUM(CASE BusPayType WHEN '0003' THEN -BusMoneyS WHEN '0004' THEN BusMoneyS ELSE 0 END),0) From Jxc_Business_200705 WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate>='2007-5-10' AND BusDate<='2007-5-31' AND BusCS=A.SysId AND BusCls IN ('0001','0002','0013')),BQF=(SELECT ISNULL(SUM(CASE BusPayType WHEN '0001' THEN BusMoneyS WHEN '0002' THEN BusMoneyS ELSE 0 END),0) From Jxc_Business_200705 WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate>='2007-5-10' AND BusDate<='2007-5-31' AND BusCS=A.SysId AND BusCls IN ('0001','0002','0013'))FROM Jxc_Supplier A) T这句SQL在查询分析器里面执行很正常,但是放在程序里却总是提示“数据库中已存在名为 '#Temp_ViewReport067' 的对象。”,真是郁闷,我可以绝对保证执行语句前不存在#Temp_ViewReport067这个对象!!!
SELECT * INTO #Temp_ViewReport067 FROM (SELECT SupTitle,QC=(SELECT ISNULL(SUM(Due),0) FROM QC_Due_200705 WHERE SysId=A.SysId)+(SELECT ISNULL(SUM(CASE BusCls WHEN '0001' THEN BusMoney-BusAgio WHEN '0002' THEN -(BusMoney-BusAgio) ELSE 0 END),0)+ISNULL(SUM(CASE BusPayType WHEN '0001' THEN -BusMoneyS WHEN '0002' THEN -BusMoneyS ELSE 0 END),0) From Jxc_Business_200705 WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate<'2007-5-10' AND BusCS=A.SysId AND BusCls IN ('0001','0002','0013')),BQZ=(SELECT ISNULL(SUM(CASE BusCls WHEN '0001' THEN BusMoney-BusAgio WHEN '0002' THEN -(BusMoney-BusAgio) ELSE 0 END),0)+ISNULL(SUM(CASE BusPayType WHEN '0003' THEN -BusMoneyS WHEN '0004' THEN BusMoneyS ELSE 0 END),0) From Jxc_Business_200705 WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate>='2007-5-10' AND BusDate<='2007-5-31' AND BusCS=A.SysId AND BusCls IN ('0001','0002','0013')),BQF=(SELECT ISNULL(SUM(CASE BusPayType WHEN '0001' THEN BusMoneyS WHEN '0002' THEN BusMoneyS ELSE 0 END),0) From Jxc_Business_200705 WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate>='2007-5-10' AND BusDate<='2007-5-31' AND BusCS=A.SysId AND BusCls IN ('0001','0002','0013'))FROM Jxc_Supplier A) T这句SQL在查询分析器里面执行很正常,但是放在程序里却总是提示“数据库中已存在名为 '#Temp_ViewReport067' 的对象。”,真是郁闷,我可以绝对保证执行语句前不存在#Temp_ViewReport067这个对象!!!
into #t
from 表名
drop table #t
这种用法没有问题。
'建立临时表
strSql = "SELECT * INTO #Temp_ViewReport067 FROM ("
strSql = strSql & "SELECT SupTitle,"
strSql = strSql & "QC=(SELECT ISNULL(SUM(Due),0) FROM QC_Due_200705 WHERE SysId=A.SysId)+"
strSql = strSql & "(SELECT ISNULL(SUM(CASE BusCls WHEN '0001' THEN BusMoney-BusAgio WHEN '0002' THEN -(BusMoney-BusAgio) ELSE 0 END),0)+"
strSql = strSql & "ISNULL(SUM(CASE BusPayType WHEN '0001' THEN -BusMoneyS WHEN '0002' THEN -BusMoneyS ELSE 0 END),0) "
strSql = strSql & "From Jxc_Business_200705 "
strSql = strSql & "WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate<'2007-5-10' AND BusCS=A.SysId AND BusCls IN ('0001','0002','0013')),"
strSql = strSql & "BQZ=(SELECT ISNULL(SUM(CASE BusCls WHEN '0001' THEN BusMoney-BusAgio WHEN '0002' THEN -(BusMoney-BusAgio) ELSE 0 END),0)+"
strSql = strSql & "ISNULL(SUM(CASE BusPayType WHEN '0003' THEN -BusMoneyS WHEN '0004' THEN BusMoneyS ELSE 0 END),0) "
strSql = strSql & "From Jxc_Business_200705 "
strSql = strSql & "WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate>='2007-5-10' AND BusDate<='2007-5-31' AND "
strSql = strSql & "BusCS=A.SysId AND BusCls IN ('0001','0002','0013')),"
strSql = strSql & "BQF=(SELECT ISNULL(SUM(CASE BusPayType WHEN '0001' THEN BusMoneyS WHEN '0002' THEN BusMoneyS ELSE 0 END),0) From Jxc_Business_200705 "
strSql = strSql & "WHERE (BusRmentType='0000' OR BusRmentType='0001') AND BusDate>='2007-5-10' AND BusDate<='2007-5-31' AND "
strSql = strSql & "BusCS=A.SysId AND BusCls IN ('0001','0002','0013'))"
strSql = strSql & "FROM Jxc_Supplier A) T"
Conn.Execute (strSql) '删除临时表
Conn.Execute ("DROP TABLE #Temp_ViewReport067")
但如果在删除临时表之前加一句Set objRs = Conn.Execute("SELECT * FROM #Temp_ViewReport067")的话就出错,提示数据库中存在#Temp_ViewReport067对象