MS-Access中的Delphi SQL subselect错误

I'm using Delphi 7 with ADO components, and MS Access 2003. The SQL sentence

我正在使用带有ADO组件的Delphi 7和MS Access 2003.SQL语句

SELECT CMCB.Name, 
     (SELECT SUM(amount) FROM movement MCB
     WHERE MCB.movement_classification_id=CMCB.movement_classification_id
     AND MCB.operation_date >= #01/01/2013#
     AND MCB.operation_date < #01/01/2014#
 ) AS MyYear 
 FROM movement_classification CMCB

is working fine in MS Access console but through a Delphi application launches the following error when I am opening the DataSet (TADOQuery):

在MS Access控制台中正常工作，但是当我打开DataSet（TADOQuery）时，通过Delphi应用程序启动以下错误：

Data provider or other service returned an E_FAIL status

数据提供者或其他服务返回E_FAIL状态

Any idea why it happens? Is it related with the ADO component (TADOQuery in this case)

知道为什么会这样吗？它与ADO组件有关（在这种情况下是TADOQuery）

I tried a similar query from the database dbdemos.mdb (Program Files\Common Files\Borland Shared\Data) and it works

我从数据库dbdemos.mdb（Program Files \ Common Files \ Borland Shared \ Data）尝试了类似的查询，它的工作原理

SELECT CustNo, 
    (SELECT SUM(AmountPaid) FROM orders O 
    WHERE O.CustNo = C.CustNo 
    AND O.SaleDate >= #01/01/1994# 
    AND O.SaleDate < #01/01/1995#
) AS AmountPaid 
FROM customer C

The code I used in Delphi is the following:

我在Delphi中使用的代码如下：

procedure TForm1.Button1Click(Sender: TObject);
begin
    ADOConnection1.Connected := False;
    ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Xiber\Delphi\*\Subquerys\dbdemos.mdb';
    ADOConnection1.Connected := True;

    ADOQuery1.SQL.Text := 'SELECT CustNo, (SELECT SUM(AmountPaid) FROM orders O WHERE O.CustNo = C.CustNo AND O.SaleDate >= #01/01/1994# AND O.SaleDate < #01/01/1995#) AS AmountPaid FROM customer C';
    ADOQuery1.Open;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
    sSQL: string;
begin
    ADOConnection1.Connected := False;
    ADOConnection1.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=D:\Xiber\Delphi\*\Subquerys\XiGest-CASA.mdb';
    ADOConnection1.Connected := True;

    sSQL := ' SELECT CMCB.Name, ' +
        ' (SELECT SUM(amount) FROM movement MCB ' +
        ' WHERE MCB.movement_classification_id=CMCB.movement_classification_id ' +
        ' AND MCB.operation_date >= #01/01/2013# ' +
        ' AND MCB.operation_date < #01/01/2014# ' +
        ' ) AS MyYear ' +
        ' FROM movement_classification CMCB ';
    ADOQuery1.SQL.Text := sSQL;
    ADOQuery1.Open;
end;

2 个解决方案

#1

As it stands, your query should return the same value for sum(qty) for each row of employee. If Access is "hiding" the relationship between the two tables, that could explain why it works in Access. I would expect a query something like:

就目前而言，您的查询应为每行员工返回相同的sum（qty）值。如果Access“隐藏”两个表之间的关系，这可以解释它在Access中的工作原理。我希望查询类似于：

    select e.name, sum(i.qty)
    from
        employee e,
        items i
    where
        i.employeeid = e.employeeid

would be something more like what you're after. This, of course, assumes a direct relationship (the foreign key of employeeid in the items table) between the two tables, which is not very realistic.

会更像你所追求的东西。当然，这假定两个表之间存在直接关系（items表中的employeeid的外键），这是不太现实的。

As others have commented, more information would permit a more precise answer!

正如其他人所评论的那样，更多信息可以提供更准确的答案！

Is there any reason you couldn't simplify the query (assuming it is an ADO bug) such as:

你有什么理由不能简化查询（假设它是一个ADO错误），例如：

    select CMCB.Name, SUM(MCB.amount)
    from
        movement_classification CMCB,
        movement MCB
    where
        MCB.movement_classification_id=CMCB.movement_classification_id
        AND MCB.operation_date >= #01/01/2013#
        AND MCB.operation_date < #01/01/2014#

#2

Finally I realised the difference between the two sums was that in dbdemos the field AmountPaid.mdb is double and in my case is decimal(8,2).

最后我意识到两个总和之间的区别是在dbdemos中字段AmountPaid.mdb是double，在我的例子中是decimal（8,2）。

It seems to be an ADO bug. You can reproduce by yourself.

这似乎是一个ADO错误。你可以自己复制。

So, If you change the field AmountPaid in dbdemos.mdb (provided by Borland, you can found at Program Files\Common Files\Borland Shared\Data) to decimal(8,2) and execute the query through Delphi 7 (with an ADOConnection and an ADOQuery), you'll get the error above mentioned.

因此，如果您将dbdemos.mdb中的字段AmountPaid（由Borland提供，您可以在Program Files \ Common Files \ Borland Shared \ Data中找到）更改为十进制（8,2）并通过Delphi 7（带有ADOConnection）执行查询和ADOQuery），你会得到上面提到的错误。

SELECT CustNo, 
    (SELECT SUM(AmountPaid) FROM orders O 
    WHERE O.CustNo = C.CustNo 
    AND O.SaleDate >= #01/01/1994# 
    AND O.SaleDate < #01/01/1995#
) AS AmountPaid 
FROM customer C

But if you execute this query inside MS Access, it works fine.

但是，如果您在MS Access中执行此查询，它可以正常工作。

#1

    select e.name, sum(i.qty)
    from
        employee e,
        items i
    where
        i.employeeid = e.employeeid