COUNT字段不正确或语法错误

What would be the error when I get following error message

当我收到以下错误消息时会出现什么错误

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error'...

致命错误：带有消息'SQLSTATE [07002]的未捕获异常'PDOException'：[Microsoft] [SQL Server的ODBC驱动程序11] COUNT字段不正确或语法错误'...

This is the query I'm using

这是我正在使用的查询

$sql = $pdo->prepare("SELECT stockamount, stockname, stockbalance.stockid, SUM(ABS(reservationtransaction.stockquantity)) AS reservedamount FROM stockbalance
    JOIN stock ON stockbalance.stockid = stock.stockid
    LEFT JOIN reservationtransaction ON reservationtransaction.articleid = :artid
    WHERE stockbalance.articleid = :artid AND ((changeddate > DATEADD(yy,-1,GETDATE()) AND inventorydate > DATEADD(yy,-1,GETDATE())) OR stockbalance.stockamount <> 0)
    GROUP BY stockbalance.stockid");
$sql->bindValue(':artid', $productId);
$sql->execute();

I have searched questions in SO, but no one was similar or helpful.
Thanks in advance.

我已经搜索了SO中的问题，但没有人相似或有帮助。提前致谢。

Edit: This query is working fine when executing it with Microsoft SQL Server Management Studio, but when using PDO, I'm getting the error.

编辑：此查询在使用Microsoft SQL Server Management Studio执行时工作正常，但在使用PDO时，我收到错误。

3 个解决方案

#1

The number of parameters specified in SQLBindParameter was less than the number of parameters in the SQL statement contained in *StatementText. SQLBindParameter was called with ParameterValuePtr set to a null pointer, StrLen_or_IndPtr not set to SQL_NULL_DATA or SQL_DATA_AT_EXEC, and InputOutputType not set to SQL_PARAM_OUTPUT, so that the number of parameters specified in SQLBindParameter was greater than the number of parameters in the SQL statement contained in *StatementText. SQLExecute Function

SQLBindParameter中指定的参数数量小于* StatementText中包含的SQL语句中的参数数量。调用SQLBindParameter时，ParameterValuePtr设置为空指针，StrLen_or_IndPtr未设置为SQL_NULL_DATA或SQL_DATA_AT_EXEC，InputOutputType未设置为SQL_PARAM_OUTPUT，因此SQLBindParameter中指定的参数数量大于* StatementText中包含的SQL语句中的参数数量。 SQLExecute功能

placeholders must have unique names even if they have the same value

占位符必须具有唯一的名称，即使它们具有相同的值

$sql = $pdo->prepare("SELECT stockamount, stockname, stockbalance.stockid, SUM(ABS(reservationtransaction.stockquantity)) AS reservedamount FROM stockbalance
JOIN stock ON stockbalance.stockid = stock.stockid
LEFT JOIN reservationtransaction ON reservationtransaction.articleid = :artid
WHERE stockbalance.articleid = :artid2 AND ((changeddate > DATEADD(yy,-1,GETDATE()) AND inventorydate > DATEADD(yy,-1,GETDATE())) OR stockbalance.stockamount <> 0)
GROUP BY stockbalance.stockid");
$sql->bindValue(':artid', $productId);
$sql->bindValue(':artid2', $productId);
$sql->execute();

#2

All the columns that are not in any arithmetic function must go in the GROUP BY clause. see below:

不在任何算术函数中的所有列都必须包含在GROUP BY子句中。见下文：

SELECT stockamount, 
       stockname, 
       stockbalance.stockid, 
       Sum(Abs(reservationtransaction.stockquantity)) AS reservedamount 
FROM   stockbalance 
       INNER JOIN stock 
               ON stockbalance.stockid = stock.stockid 
       LEFT JOIN reservationtransaction 
              ON reservationtransaction.articleid = :artid 
WHERE  stockbalance.articleid = :artid 
       AND ( ( changeddate > Dateadd(yy, -1, Getdate()) 
               AND inventorydate > Dateadd(yy, -1, Getdate()) ) 
              OR stockbalance.stockamount <> 0 ) 
GROUP  BY stockamount, 
          stockname, 
          stockbalance.stockid 

#3

Another possibility, if you want to avoid supplying data multiple times (replace the datatype of @artid with the correct data type):

另一种可能性，如果您想避免多次提供数据（用正确的数据类型替换@artid的数据类型）：

$sql = $pdo->prepare("DECLARE @artid int = :artid
    SELECT stockamount, stockname, stockbalance.stockid, SUM(ABS(reservationtransaction.stockquantity)) AS reservedamount FROM stockbalance
    JOIN stock ON stockbalance.stockid = stock.stockid
    LEFT JOIN reservationtransaction ON reservationtransaction.articleid = @artid
    WHERE stockbalance.articleid = @artid AND ((changeddate > DATEADD(yy,-1,GETDATE()) AND inventorydate > DATEADD(yy,-1,GETDATE())) OR stockbalance.stockamount <> 0)
    GROUP BY stockbalance.stockid");
$sql->bindValue(':artid', $productId);
$sql->execute();

This will only work in an RDBMS that supports DECLARE statements.

这只适用于支持DECLARE语句的RDBMS。