SQL查询错误 - 包含的引号和错误的语法

问题描述：

我们在我们的SAP Business One程序中查询了一位前同事创建的程序。SQL查询错误 - 包含的引号和错误的语法

查询自2015年创建以来每个月都一直在正常工作，但今天它正在抛出错误，我们在这里没有SQL专家。

错误：

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Unclosed quotation mark after the character string 'SALES PROMOT'. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near 'SALES PROMOT'. 'Document' (RDOC)

查询：

DECLARE @listCol VARCHAR(8000) 
 
DECLARE @Query VARCHAR(8000) 
 
DECLARE @OINV VARCHAR(8000) 
 
DECLARE @ORIN VARCHAR(8000) 
 
DECLARE @From varchar(15) 
 
DECLARE @To varchar(15) 
 
Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112) 
 
Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112) 
 
    
 
    
 
SET @OINV = isnull(STUFF((Select ',' + convert(varchar(10),Docentry) from OINV 
 
    where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0) 
 
SET @ORIN = isnull(STUFF((Select ',' + convert(varchar(10),Docentry) from ORIN 
 
    where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0) 
 
SELECT @listCol = STUFF((SELECT DISTINCT '],[' + ltrim((ItmsGrpNam)) from OITB 
 
ORDER BY '],[' + ltrim((ItmsgrpNam)) FOR XML PATH('')), 1, 2, '') + ']' 
 
    
 
SET @Query = 'Select * from (Select T0.CardCode, T0.CardName, T6.[GroupName], T4.ItmsGrpNam,sum(T1.LineTotal) as Sal from 
 
OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry 
 
Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod 
 
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode Inner Join OCRD T5 On T5.CardCode=T0.CardCode inner join OCRG T6 On T6.[GroupCode]=T5.[GroupCode] 
 
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'[email protected]+',112) 
 
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'[email protected]+',112) 
 
and t0.docentry not in ('[email protected]+') 
 
group by T0.CardName ,T4.ItmsGrpNam,T0.CardCode, T6.[GroupName] 
 
    
 
union all 
 
    
 
Select T0.CardCode, T0.CardName, T6.[GroupName], T4.ItmsGrpNam,sum(-T1.LineTotal) as Sal from 
 
ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry 
 
Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod 
 
Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode Inner Join OCRD T5 On T5.CardCode=T0.CardCode inner join OCRG T6 On T6.[GroupCode]=T5.[GroupCode] 
 
Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'[email protected]+',112) 
 
and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'[email protected]+',112) 
 
and t0.docentry not in ('[email protected]+') 
 
group by T0.CardName ,T4.ItmsGrpNam, T0.CardCode, T6.[GroupName] 
 

 
    
 
) src 
 
PIVOT (sum(Sal) for ItmsgrpNam IN ('[email protected]+')) AS pvt' 
 
EXECUTE (@Query)

任何人都可以建议我怎么能着手解决这个或看到任何明显的错误代码？

当时移除一个零件以找出问题零件的位置。 – jarlh

看起来你正在SQL上运行SAP，你有没有试过在SSMS中运行查询？你将不得不改变它一点，以摆脱SAP特定的任务'SET @FROM ='和'SET @TO =' –

答

此查询所做的工作是在@query中构建一个动态查询字符串，并在最后执行。在查询的某个地方，它添加了一个具有未封闭引号的值。

而不是有EXEC(@query)，您可以使用SELECT @query返回动态查询字符串，检查它，找到它出错的地方，并修复有问题的记录。

我的猜测是在OITB.ItmsGrpNam的项目组名称中的一个中有一个引号，将需要删除或正确转义。

SQL查询错误 - 包含的引号和错误的语法

相关推荐