SQL语句在Access中通过,在Delphi中动态加入为什么不行? ( 积分: 50 )

G

gmsft

Unregistered / Unconfirmed
GUEST, unregistred user!
SELECT Current.CompanyID AS CompanyID, Current.CompanyName AS CompanyName, Current.CompanyOrder AS ListOrder, Current.TEamount AS CurrentE, Yester.TEamount AS YesterE, Current.TIamount AS CurrentI, Yester.TIamount AS YesterI, Current.TIEamount AS CurrentIE, Yester.TIEamount AS YesterIE
FROM [SELECT CompanyID, CompanyName, CompanyOrder,
SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount
FROM Company LEFT JOIN
(SELECT Company, Eamount, Iamount, IEamount
FROM Business WHERE Term='0509'
UNION ALL
SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount
FROM Company) AS
Business
ON Company.CompanyID=Business.Company
GROUP BY CompanyID, CompanyName, CompanyOrder]. AS [Current] LEFT JOIN [SELECT CompanyID, CompanyName, CompanyOrder,
SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount
FROM Company
LEFT JOIN
(SELECT Company, Eamount, Iamount, IEamount
FROM Business WHERE Term='0309'
UNION ALL
SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount
FROM Company) AS
Business
ON Company.CompanyID=Business.Company
GROUP BY CompanyID, CompanyName, CompanyOrder]. AS Yester ON Current.CompanyID=Yester.CompanyID
ORDER BY Current.CompanyOrder;


ADOQuery1.SQL.Add(''
+' SELECT Current.CompanyID AS CompanyID, Current.CompanyName AS CompanyName, Current.CompanyOrder AS ListOrder, Current.TEamount AS CurrentE, Yester.TEamount AS YesterE, Current.TIamount AS CurrentI,'
+' Yester.TIamount AS YesterI, Current.TIEamount AS CurrentIE, Yester.TIEamount AS YesterIE'
+' FROM [SELECT CompanyID, CompanyName, CompanyOrder,'
+' SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount'
+' FROM Company LEFT JOIN'
+' (SELECT Company, Eamount, Iamount, IEamount'
+' FROM Business WHERE Term=''0509'''
+' UNION ALL'
+' SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount'
+' FROM Company) AS'
+' Business'
+' ON Company.CompanyID=Business.Company'
+' GROUP BY CompanyID, CompanyName, CompanyOrder]. AS [Current] LEFT JOIN [SELECT CompanyID, CompanyName, CompanyOrder,'
+' SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount'
+' FROM Company'
+' LEFT JOIN'
+' (SELECT Company, Eamount, Iamount, IEamount'
+' FROM Business WHERE Term=''0309'''
+' UNION ALL'
+' SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount'
+' FROM Company) AS'
+' Business'
+' ON Company.CompanyID=Business.Company'
+' GROUP BY CompanyID, CompanyName, CompanyOrder]. AS Yester ON Current.CompanyID=Yester.CompanyID'
+' ORDER BY Current.CompanyOrder');

ADOQuery1.Open;
提示:“未指定的错误”
 
SELECT Current.CompanyID AS CompanyID, Current.CompanyName AS CompanyName, Current.CompanyOrder AS ListOrder, Current.TEamount AS CurrentE, Yester.TEamount AS YesterE, Current.TIamount AS CurrentI, Yester.TIamount AS YesterI, Current.TIEamount AS CurrentIE, Yester.TIEamount AS YesterIE
FROM [SELECT CompanyID, CompanyName, CompanyOrder,
SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount
FROM Company LEFT JOIN
(SELECT Company, Eamount, Iamount, IEamount
FROM Business WHERE Term='0509'
UNION ALL
SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount
FROM Company) AS
Business
ON Company.CompanyID=Business.Company
GROUP BY CompanyID, CompanyName, CompanyOrder]. AS [Current] LEFT JOIN [SELECT CompanyID, CompanyName, CompanyOrder,
SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount
FROM Company
LEFT JOIN
(SELECT Company, Eamount, Iamount, IEamount
FROM Business WHERE Term='0309'
UNION ALL
SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount
FROM Company) AS
Business
ON Company.CompanyID=Business.Company
GROUP BY CompanyID, CompanyName, CompanyOrder]. AS Yester ON Current.CompanyID=Yester.CompanyID
ORDER BY Current.CompanyOrder;


ADOQuery1.SQL.Add(''
+' SELECT Current.CompanyID AS CompanyID, Current.CompanyName AS CompanyName, Current.CompanyOrder AS ListOrder, Current.TEamount AS CurrentE, Yester.TEamount AS YesterE, Current.TIamount AS CurrentI,'
+' Yester.TIamount AS YesterI, Current.TIEamount AS CurrentIE, Yester.TIEamount AS YesterIE'
+' FROM [SELECT CompanyID, CompanyName, CompanyOrder,'
+' SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount'
+' FROM Company LEFT JOIN'
+' (SELECT Company, Eamount, Iamount, IEamount'
+' FROM Business WHERE Term=''0509'''
+' UNION ALL'
+' SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount'
+' FROM Company) AS'
+' Business'
+' ON Company.CompanyID=Business.Company'
+' GROUP BY CompanyID, CompanyName, CompanyOrder]. AS [Current] LEFT JOIN [SELECT CompanyID, CompanyName, CompanyOrder,'
+' SUM(Eamount) AS TEamount, SUM(Iamount) AS TIamount, SUM(IEamount) AS TIEamount'
+' FROM Company'
+' LEFT JOIN'
+' (SELECT Company, Eamount, Iamount, IEamount'
+' FROM Business WHERE Term=''0309'''
+' UNION ALL'
+' SELECT CompanyID AS Company, 0 AS Eamount, 0 AS Iamount, 0 AS IEamount'
+' FROM Company) AS'
+' Business'
+' ON Company.CompanyID=Business.Company'
+' GROUP BY CompanyID, CompanyName, CompanyOrder]. AS Yester ON Current.CompanyID=Yester.CompanyID'
+' ORDER BY Current.CompanyOrder');

ADOQuery1.Open;
提示:“未指定的错误”
 
将你上面的代码设计时直接拷贝到ADOQuery.SQL中再试试.
 
试过了,不行。

是不是Delphi,或 ADO 的 Bug ?
 
网上下载MADC2.8,安装试试看。
 
ADO的连接字符串ConnectionString指定是否正确?
 
既然你在Access可以中可以执行通过,那么就不是表是否存在的问题了,你看下是不是你给字符串字段赋值的时候引号用的对不对,你单步调试下,把要执行的语句赋值给一个字符串,调试到Add后里面的数据(也就是开始得到值的字符串的值)拷贝出来放到Access里面执行看出现什么提示!
 
上面的语句简化为:
SELECT * FROM
(SELECT ......)
LEFT JOIN
(SELECT ......)
ON ......
其中两个 (SELECT.....) 是完全一样的,只是参数不同;
在去掉 LEFT JOIN 以后的语句,则可以执行,
LEFT JOIN 之后,提示:“未指定的错误”
 
问题解决了!

子查询的别名不能用“Current”,改为“CurrentBiz”后,通过。

请问各位,Current 是保留字吗?
 
Current 应该是关键字,保留字
 
Current 应该是关键字
 
做一个视图吧,搞得很复杂
 
我同意boylafong 的说法,在ACCESS里通过了,在SQL应该就没问题
 
ACCESS里通过
delphi 里不一定行
因为 ACCESS好像 有dao连接的
我们 一般 用ado 连接。
两者有所 不同
 
把你所有的表名和字段名都用[]圈起来再试试。估计Current也没问题了。
 
设断点,取出sql语句,再放到access中执行看看行不行
 
你的字段有时间的吗?Access对时间的支持不是很好。
 
在access里通过delphi里不一定行,另外我觉得你用视图是不是会清楚些?
 
Current 是关键字,表示money类型
 
文本文件导入数据库的方法
 
顶部