我怎么...'='附近的语法不正确。同时使用水晶报表和SQL [英] How do i...incorrect syntax near '='. While using crystal reporting and SQL

问题描述

使用（SqlCommand cmd = new SqlCommand（））
 {
 cmd.CommandText = 
SELECT com。[Com_Sequence] AS [rptSequence]，com。 [Com_Date] AS [rptDate]，+ 
SWITCH（+ 
len（com。[Com_Line]）= 1，com。[Com_Line]，+ 
true， '0'+ com。[Com_Line]）AS [rptLine]，+ 
com。[Com_Shift] AS [rptShift]，com。[Com_Product] AS [rptProduct]，com。[Com_Source] AS [rptSource ]，+ 
SWITCH（+ 
com。[Com_Time_Hour] = '12'，'00'，+ 
true，com。[Com_Time_Hour]）AS [rptSHour ]，+ 
com。[Com_Time_Minute] AS [rptSMinute]，com。[Com_Time_ampm] AS [rptSAMPM]，+ 
com。[Com_Comment] AS [rptComment]，com。[Com_Action ] AS [rptAction]，dev。[Dev_Description] AS [rptDevDesc]，+ 
dev。[Dev_ ID] AS [rptDevID]，'AS [rptEhour]，'AS [rptEMinute]，'AS [rptEAMPM]+ 
FROM [Comments] com+ 
LEFT JOIN [偏差] ] dev+ 
ON com。[Com_DeviationEvent] = dev。[Dev_ID]+ 
WHERE com。[Com_Date] =（SELECT [SC_Date] FROM [Company_Data]）;; 
 cmd.Connection = cnn; 
 
 ReportDataSet dataSet = new ReportDataSet（）; 
使用（SqlDataAdapter adapter = new SqlDataAdapter（cmd））
 adapter.Fill（dataSet.rptComments）; 

我尝试了什么：



我试图在语法中找到错误，但我没有在任何地方看到错误。

解决方案

而不是尝试处理这样的长字符串而不是首先在SSMS或查询分析器中尝试查询。



除非你编写了一个名为SWITCH的标量函数，否则你将开始遇到错误

引用：

- - '='附近的语法不正确。

- 'SWITCH'不是公认的内置函数名。

- 无效的列名'true'

我怀疑你的查询应该更像这样

  SELECT  com。[Com_Sequence]  AS  [rptSequence]，com。[Com_Date ]  AS  [rptDate]，
 
  CASE   WHEN  len（com。[Com_Line]）=  1  那么 com。[ Com_Line]  ELSE  '  0' + com 。[Com_Line]  END   AS  [rptLine]，
 
 com。[Com_Shift ]  AS  [rptShift]，com。[Com_Product]  AS  [rptProduct]，com。[Com_Source] < span class =code-keyword> AS  [rptSource]，
 
  CASE   WHEN  com。[Com_Time_Hour] = '  12'  THEN  '  00'  ELSE  com。[Com_Time_Hour]  END   AS  [rptshour]，
 
 com。[Com_Time_Minute]  AS  [rptSMinute]，com。[Com_Time_ampm]  AS  [rptSAMPM]，
 com。[Com_Comment]  AS  [rptComment]，com。[Com_Action]  AS  [rptAction] ，dev。[Dev_Description]  AS  [rptDevDesc]，
 dev。[Dev_ID]  AS  [ rptDevID]，' '  AS  [rptEHour ]，' '  AS  [rptEMinute] ，' '  AS  [rptEAMPM] 
  FROM  [评论] com 
  LEFT   JOIN  [Deviations] dev  ON  com。[Com_DeviationEvent] = dev。 [Dev_ID] 
  WHERE  com。[Com_Date] =（ SELECT  [SC_Date]  FROM  [Company_Data]）; 

但我不知道你想用真实做什么。



如果你的数据库上有一个名为switch的标量函数，你仍然会在尝试传递 len（com。[Com_Line]）= 1 时出错和 true 因为它们在语法上也是不正确的。



关于你的查询的一些一般性说明：



- 为什么要打扰[Comments]表的所有列的前缀 com _ 和带有<$的[Deviations]表c $ c> Dev _ ，它只是用于额外输入 - 特别是因为你使用了表别名而通常不建议作为命名约定。



- 您将时间作为小时和分钟存储在char或varchar字段中（ com。[Com_Time_Hour] ='12'）最糟糕的是，这些应该是整数。更好的方法是将Com_Date字段存储为 DateTime 字段



- 您正在SQL查询中进行格式化 - 格式化应保留在UI层 - 尤其是在将其加载到Crystal Reports中时。如果你真的必须在你的数据库中这样做，那么不要使用CASE语句，使用类似REPLICATE的东西 - 例如：

  DECLARE   @ x   int  =  7  
  SELECT  replicate（'  0 '， 3   -  len（ @ x ））+ cast（ @ x   as   varchar ）
   -    result 007  
  SET   @ x  =  13  
  SELECT  replicate（ '  0'， 3   -  len（ @ x ））+ cast（ @ x   as   varchar ）
   -   结果013  

甚至

 选择 右（'  000' + rtrim（ @x ）， 3 ）

感谢您的帮助。我发现我在switch语句中缺少一个参数。我还删除了switch语句并用case语句替换它们。这是我更新的工作代码：

 SELECT com。[Com_Sequence] AS [rptSequence]，com。[Com_Date] AS [rptDate]， 
（CASE len（com。[Com_Line]）
 WHEN 1 THEN com。[Com_Line] 
 ELSE'0'+ com。[Com_Line] 
 END 
） AS [rptLine]，
 com。[Com_Shift] AS [rptShift]，com。[Com_Product] AS [rptProduct]，com。[Com_Source] AS [rptSource]，
（CASE com。[Com_Time_Hour] 
 WHEN'12'THEN'00'
 ELSE com。[Com_Time_Hour] 
 END 
）AS [rptSHour]，
 com。[Com_Time_Minute] AS [rptSMinute] ，com。[Com_Time_ampm] AS [rptSAMPM]，
 com。[Com_Comment] AS [rptComment]，com。[Com_Action] AS [rptAction]，dev。[Dev_Description] AS [rptDevDesc]，
 dev 。[Dev_ID] AS [rptDevID]，'AS [rptEhour]，'AS [rptEMinute]，'AS [rptEAMPM] 
 FROM [评论] AS com 
 LEFT JOIN [Deviations] dev 
 ON com。[Com_DeviationEvent] = dev。[Dev_ID] 
 WHERE com。[Com_Date] =（SELECT [SC_Date] FROM [Company_Data]）; 

using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText =
                "SELECT com.[Com_Sequence] AS [rptSequence], com.[Com_Date] AS [rptDate], " +
                    "SWITCH ( " +
                        "len(com.[Com_Line]) = 1, com.[Com_Line], " +
                        "true, '0' + com.[Com_Line] ) AS [rptLine], " +
                    "com.[Com_Shift] AS [rptShift], com.[Com_Product] AS [rptProduct], com.[Com_Source] AS [rptSource], " +
                    "SWITCH ( " +
                        "com.[Com_Time_Hour] = '12', '00', " +
                        "true, com.[Com_Time_Hour] ) AS [rptSHour], " +
                    "com.[Com_Time_Minute] AS [rptSMinute], com.[Com_Time_ampm] AS [rptSAMPM], " +
                    "com.[Com_Comment] AS [rptComment], com.[Com_Action] AS [rptAction], dev.[Dev_Description] AS [rptDevDesc], " +
                    "dev.[Dev_ID] AS [rptDevID], '' AS [rptEHour], '' AS [rptEMinute], '' AS [rptEAMPM] " +
                "FROM [Comments] com " +
                "LEFT JOIN [Deviations] dev " +
                "ON com.[Com_DeviationEvent] = dev.[Dev_ID] " +
                "WHERE com.[Com_Date] = ( SELECT [SC_Date] FROM [Company_Data] );";
                cmd.Connection = cnn;

                ReportDataSet dataSet = new ReportDataSet();
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    adapter.Fill(dataSet.rptComments);

What I have tried:

I have tried to find the error in the syntax, but I am not seeing the error anywhere.

解决方案

Instead of trying to handle a long string like this first attempt the query in SSMS or Query Analyser.

Unless you have written a scalar-function called SWITCH you are going to start hitting errors

Quote:

-- Incorrect syntax near '='.
--'SWITCH' is not a recognized built-in function name.
--Invalid column name 'true'

I suspect your query should look more like this

SELECT com.[Com_Sequence] AS [rptSequence], com.[Com_Date] AS [rptDate], 

CASE WHEN len(com.[Com_Line]) = 1 THEN com.[Com_Line] ELSE '0' + com.[Com_Line] END AS [rptLine], 

	com.[Com_Shift] AS [rptShift], com.[Com_Product] AS [rptProduct], com.[Com_Source] AS [rptSource], 

CASE WHEN com.[Com_Time_Hour] = '12' THEN '00' ELSE com.[Com_Time_Hour] END AS [rptSHour],

	com.[Com_Time_Minute] AS [rptSMinute], com.[Com_Time_ampm] AS [rptSAMPM], 
	com.[Com_Comment] AS [rptComment], com.[Com_Action] AS [rptAction], dev.[Dev_Description] AS [rptDevDesc], 
	dev.[Dev_ID] AS [rptDevID], '' AS [rptEHour], '' AS [rptEMinute], '' AS [rptEAMPM] 
	FROM [Comments] com 
	LEFT JOIN [Deviations] dev ON com.[Com_DeviationEvent] = dev.[Dev_ID] 
	WHERE com.[Com_Date] = ( SELECT [SC_Date] FROM [Company_Data] );

but I have no idea what you are trying to do with that "true".

If you do have a scalar function called switch on your database you will still get errors trying to pass len(com.[Com_Line]) = 1 and true as they are syntactically incorrect as well.

Some general notes on your query:

- Why bother prefixing all of the columns of the [Comments] table with com_ and the [Deviations] table with Dev_, it just makes for extra typing - especially as you have used a table alias and is not generally recommended as a naming convention.

- You are storing the time as hours and minutes in char or varchar fields (com.[Com_Time_Hour]= '12') At worst these should be integers. Better would be to store the Com_Date field as a DateTime field

- You are doing formatting within your SQL query - formatting should be left to the UI layer - especially as you are loading this into Crystal Reports. If you really must do it in your database then don't use a CASE statement, use something like REPLICATE - example:

DECLARE @x int = 7
SELECT replicate('0', 3 - len(@x)) + cast (@x as varchar) 
-- result 007
SET @x = 13
SELECT replicate('0', 3 - len(@x)) + cast (@x as varchar) 
-- result 013

or even

select right('000'+ rtrim(@x), 3)

Thanks for the help. I found out that I was missing a parameter in the switch statement. I also removed the switch statements and replaced them with case statements. Here is my updated working code:

SELECT com.[Com_Sequence] AS [rptSequence], com.[Com_Date] AS [rptDate], 
(CASE len(com.[Com_Line])
 WHEN 1 THEN com.[Com_Line] 
 ELSE '0' + com.[Com_Line]
 END
) AS [rptLine], 
com.[Com_Shift] AS [rptShift], com.[Com_Product] AS [rptProduct], com.[Com_Source] AS [rptSource], 
(CASE com.[Com_Time_Hour]
 WHEN '12' THEN '00'
 ELSE com.[Com_Time_Hour]
 END
) AS [rptSHour], 
com.[Com_Time_Minute] AS [rptSMinute], com.[Com_Time_ampm] AS [rptSAMPM], 
com.[Com_Comment] AS [rptComment], com.[Com_Action] AS [rptAction], dev.[Dev_Description] AS [rptDevDesc], 
dev.[Dev_ID] AS [rptDevID], '' AS [rptEHour], '' AS [rptEMinute], '' AS [rptEAMPM] 
FROM [Comments] AS com 
LEFT JOIN [Deviations] dev 
ON com.[Com_DeviationEvent] = dev.[Dev_ID] 
WHERE com.[Com_Date] = ( SELECT [SC_Date] FROM [Company_Data] );

这篇关于我怎么...'='附近的语法不正确。同时使用水晶报表和SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！