我在程序中用了一个全程的连接.datgmis.congmis ,开始运行如下SQL.是正确的.SELECT INVOICECLASSNO AS 序号,INVOICECLASSNAME AS 发票名称,UNIT AS 单位,
(SELECT ISNULL(SUM(ALLYEAR),0) FROM INVOICEPLAN A LEFT JOIN COMPANY B ON A.COMPANYID LIKE B.COMPANYID+'%' WHERE PLANYEAR=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' AND B.COMPANYID ='01') AS 本年计划,
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREIN WHERE YEAR(STOREINDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' ) AS 本年领取,
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREOUT A LEFT JOIN COMPANY B ON A.COMPANYID LIKE B.COMPANYID+'%' LEFT JOIN STOREOUTDETAIL C ON A.STOREOUTID=C.STOREOUTID WHERE YEAR(STOREOUTDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' AND B.COMPANYID = '01') AS 本年发售,
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREIN WHERE YEAR(STOREINDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' ) -
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREOUT A LEFT JOIN COMPANY B ON A.COMPANYID LIKE B.COMPANYID+'%' LEFT JOIN STOREOUTDETAIL C ON A.STOREOUTID=C.STOREOUTID WHERE YEAR(STOREOUTDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' AND B.COMPANYID = '01') AS 现有库存
FROM INVOICECLASS ORDER BY INVOICECLASSID当作其他的操作,再回到这个模块,执行查询,结果就不对了.数据有翻倍的情况.
是这样取的数,
Set rs = datGMIS.conGMIS.Execute(strSQL)
很有意思的是,我把strSQL串在查询分析器中运行结果就对.
在程序中输出到EXCEL也对
.ObjExcel.activesheet.QueryTables.Add(Connection:="ODBC;" & gConnectionString, Destination:=ObjExcel.activesheet.range(sRange))
.SQL = strSQL
但就是这次用Set rs = datGMIS.conGMIS.Execute(strSQL)取出来不对了.并且我把连接断了又重连一次就对了.
datgmis.congmis.close
datgmis.congmis.open
很奇怪.难道是我的SQL语句写的有问题?但是为什么第一次运行对,在查询分析器中也对,输出到EXCEL中也对,就是执行了一些别的模块以后,回来就不对了.
(SELECT ISNULL(SUM(ALLYEAR),0) FROM INVOICEPLAN A LEFT JOIN COMPANY B ON A.COMPANYID LIKE B.COMPANYID+'%' WHERE PLANYEAR=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' AND B.COMPANYID ='01') AS 本年计划,
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREIN WHERE YEAR(STOREINDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' ) AS 本年领取,
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREOUT A LEFT JOIN COMPANY B ON A.COMPANYID LIKE B.COMPANYID+'%' LEFT JOIN STOREOUTDETAIL C ON A.STOREOUTID=C.STOREOUTID WHERE YEAR(STOREOUTDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' AND B.COMPANYID = '01') AS 本年发售,
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREIN WHERE YEAR(STOREINDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' ) -
(SELECT ISNULL(SUM(QUANTITY),0) FROM STOREOUT A LEFT JOIN COMPANY B ON A.COMPANYID LIKE B.COMPANYID+'%' LEFT JOIN STOREOUTDETAIL C ON A.STOREOUTID=C.STOREOUTID WHERE YEAR(STOREOUTDATE)=YEAR('2005-04-29') AND INVOICEID LIKE INVOICECLASS.INVOICECLASSID+'%' AND B.COMPANYID = '01') AS 现有库存
FROM INVOICECLASS ORDER BY INVOICECLASSID当作其他的操作,再回到这个模块,执行查询,结果就不对了.数据有翻倍的情况.
是这样取的数,
Set rs = datGMIS.conGMIS.Execute(strSQL)
很有意思的是,我把strSQL串在查询分析器中运行结果就对.
在程序中输出到EXCEL也对
.ObjExcel.activesheet.QueryTables.Add(Connection:="ODBC;" & gConnectionString, Destination:=ObjExcel.activesheet.range(sRange))
.SQL = strSQL
但就是这次用Set rs = datGMIS.conGMIS.Execute(strSQL)取出来不对了.并且我把连接断了又重连一次就对了.
datgmis.congmis.close
datgmis.congmis.open
很奇怪.难道是我的SQL语句写的有问题?但是为什么第一次运行对,在查询分析器中也对,输出到EXCEL中也对,就是执行了一些别的模块以后,回来就不对了.
估计是临时表在发生作用, 如果临时表内容没有被清除,那么就 很有可能重复统计
这应该和 ADO 的Execute 机制有关,你试试看在 SQL 最后加上 Go 看看
,看结果,中间不要隔其他操作