试图创建一个视图，但得到和SQL错误 [英] trying to create a view but getting and sql error

查看：86
本文介绍了试图创建一个视图，但得到和SQL错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

嗨
我正在尝试创建一个视图但是得到和sql错误

i我正在使用fllowing sql代码

错误我正在寻找是

SQL执行错误

错误消息：关键字AS附近的语法不正确

：关键字AS附近的语法错误

：关键字AS附近的语法不正确



请帮助我。



  SELECT  
 nN 
，DATEADD（day，nN， 0 ） AS 天  -    nn不正确 
 ，n.EnrollNo 
，n.EMachineNo 
，Start.StartDT 
，Finish.FinishDT 
，CAST（Finish.FinishDT  -  Start.StartDT  AS  时间） AS  ElapsedTime 
  FROM （
  SEL ECT  
 nn.N 
，DATEADD（day，nn.N， 0 ） AS 天
，t_1.EnrollNo 
，t_1.EMachineNo 
  FROM  dbo.Number  AS  nn 
 
  CROSS   JOIN （ 
  SELECT   DISTINCT  
 tt.EnrollNo 
，tt.EMachineNo 
  FROM  dbo.tblEmpGenralLog  AS  tt 
） AS  t_1 
  WHERE  
（dateadd（day，nn.N， 0 ）> = '  1900-05-01' 
 和 dateadd（day，nn.N， 0 ）< '  2030-06-01' 
） AS  n 
  LEFT   OUTER  加入（
   -   为什么你不在这里使用trable别名 
  SELECT  
 EnrollNo 
，EMachineNo 
，DATEDIFF（day， 0 ，EmpDateTime） AS  StartDayNo   -    DateTime is一个保留字，因此是列名的错误选择。你需要划定这个 
，MIN（EmpDateTime） AS  StartDT 
  FROM  dbo.tblEmpGenralLog  AS  t 
  GROUP   BY  
 DATEDIFF（日， 0 ，EmpDateTime）
，EnrollNo 
，EMachineNo 
） AS 开始
  ON  Start.StartDayNo = nN 
  AND  Start.EnrollNo = n.EnrollNo 
  AND  Start.EMachineNo = n.EMachineNo 
  LEFT   OUTER   JOIN （
   -   你为什么不在这里使用trable别名 
  SELECT  
 EnrollNo 
，EMachineNo 
，DATEDIFF（日， 0 ，EmpDateTime） AS  FinishDayNo   -    DateTime是一个保留字，因此是列名的错误选择。你需要划定这个 
，MAX（EmpDateTime） AS  FinishDT 
  FROM  dbo.tblEmpGenralLog  AS  t1 
  GROUP   BY  
 DATEDIFF（日， 0 ，EmpDateTime）
，EnrollNo 
，EMachineNo 
）< span class =code-keyword> AS 完成
  ON  Finish.FinishDayNo = nN 
  AND  Finish.EnrollNo = n.EnrollNo 
  AND  Finish.EMachineNo = n.EMachineNo 
;

解决方案

仅供参考，请务必将您的回复发布到我们的评论和/或更新原始帖子。如果您发表评论作为解决方案，我们将不会收到通知。



我必须将您的代码转到SQL Management Studio才能看到真正的问题。单词 DAY 是SQL Server中的关键字，您尝试将其用作列名。只要你有一个关键字或保留字（在SQL Server Management Studio中会变成蓝色或粉红色）用作列名，你就需要将其转义或更改名称。



所以如果你改变这些行：

 DATEADD（day，nn.N， 0 ） AS  day



to 

 DATEADD（day，nn.N， 0 ） AS  [day]





这应解决您的问题。


感谢virusstom回复



i已经改变了一天到Empday

但仍然得到同样的错误

以下代码行我已更改



，DATEADD（day，nn.N，0）AS Empday



还有更改吗？？



提前致谢。



现在代码看起来像这样。



  SELECT  
 nN 
，DATEADD（day，nN， 0 ）  AS 天  -    nn不正确 
，n.EnrollNo 
，n.EMachineNo 
，Start.StartDT 
，Finish.FinishDT 
，CAST（Finish.FinishDT  -  Start.StartDT  AS  时间） AS  ElapsedTime 
  FROM （
  SELECT  
 nn.N 
，DATEADD（day，nn.N ， 0 ） AS  Empday 
，t_1.EnrollNo 
，t_1.EMachineNo 
  FROM  dbo.Number  AS  nn 
 
  CROSS   JOIN （
  SELECT   DISTINCT  
 tt.EnrollNo 
，tt.EMachineNo 
  FROM  dbo.tblEmpGenralLog  AS  tt 
） AS  t_1 
  WHERE  
（dateadd（day，nn.N， 0 ）> = '  1900-05-01' 
 和 dateadd（day， nn.N， 0 ）< '  2030-06-01' 
） AS  n 
  LEFT   OUTER  加入（
   -   为什么你不在这里使用trable别名 
  SELECT  
 EnrollNo 
，EMachineNo 
，DATEDIFF（day， 0 ，EmpDateTime） AS  StartDayNo   -    DateTime is一个保留字，因此是列名的错误选择。你需要划定这个 
，MIN（EmpDateTime） AS  StartDT 
  FROM  dbo.tblEmpGenralLog  AS  t 
  GROUP   BY  
 DATEDIFF（日， 0 ，EmpDateTime）
，EnrollNo 
，EMachineNo 
） AS 开始
  ON  Start.StartDayNo = nN 
  AND  Start.EnrollNo = n.EnrollNo 
  AND  Start.EMachineNo = n.EMachineNo 
  LEFT   OUTER   JOIN （
   -   你为什么不在这里使用trable别名 
  SELECT  
 EnrollNo 
，EMachineNo 
，DATEDIFF（日， 0 ，EmpDateTime） AS  FinishDayNo   -    DateTime是一个保留字，因此是列名的错误选择。你需要划定这个 
，MAX（EmpDateTime） AS  FinishDT 
  FROM  dbo.tblEmpGenralLog  AS  t1 
  GROUP   BY  
 DATEDIFF（日， 0 ，EmpDateTime）
，EnrollNo 
，EMachineNo 
）< span class =code-keyword> AS 完成
  ON  Finish.FinishDayNo = nN 
  AND  Finish.EnrollNo = n.EnrollNo 
  AND  Finish.EMachineNo = n.EMachineNo 
;


Hi I am trying to create a view but getting and sql error
i am using fllowing sql code
Error i am geiting is
"SQL Execution error"
Error Message : Incorrect syntax near the keyword AS
: Incorrect syntax near the keyword AS
: Incorrect syntax near the keyword AS

Please Assist me.

SELECT     
	n.N
	, DATEADD(day, n.N, 0) AS day -- nn is incorrect
	, n.EnrollNo
	, n.EMachineNo
	, Start.StartDT
	, Finish.FinishDT
	, CAST(Finish.FinishDT - Start.StartDT AS time) AS ElapsedTime
FROM (
	SELECT     
		nn.N
		, DATEADD(day, nn.N, 0) AS day
		, t_1.EnrollNo
		, t_1.EMachineNo
	FROM dbo.Number AS nn 

	CROSS JOIN (
		SELECT DISTINCT 
			tt.EnrollNo
			, tt.EMachineNo
		FROM dbo.tblEmpGenralLog AS tt
	) AS t_1                       
	WHERE      
		(dateadd( day, nn.N, 0 ) >= '1900-05-01'
		and dateadd( day, nn.N, 0 ) < '2030-06-01'
) AS n 
LEFT OUTER JOIN (
	-- why are you not using trable alias's in here
	SELECT     
		EnrollNo
		, EMachineNo
		, DATEDIFF(day, 0, EmpDateTime) AS StartDayNo -- DateTime is a reserved word and thus a bad choice for a column name. You need to delimit this
		, MIN(EmpDateTime) AS StartDT
	FROM dbo.tblEmpGenralLog AS t
	GROUP BY 
		DATEDIFF(day, 0, EmpDateTime)
		, EnrollNo
		, EMachineNo
	) AS Start 
	ON Start.StartDayNo = n.N 
	AND Start.EnrollNo = n.EnrollNo 
	AND Start.EMachineNo = n.EMachineNo 
LEFT OUTER JOIN (
	-- why are you not using trable alias's in here
	SELECT     
		EnrollNo
		, EMachineNo
		, DATEDIFF(day, 0, EmpDateTime) AS FinishDayNo -- DateTime is a reserved word and thus a bad choice for a column name. You need to delimit this
		, MAX(EmpDateTime) AS FinishDT
	FROM dbo.tblEmpGenralLog AS t1
	GROUP BY 
		DATEDIFF(day, 0, EmpDateTime)
		, EnrollNo
		, EMachineNo
	) AS Finish 
	ON Finish.FinishDayNo = n.N 
	AND Finish.EnrollNo = n.EnrollNo 
	AND Finish.EMachineNo = n.EMachineNo
;

解决方案

FYI, make sure you post your reply to the comments our and/or update the original post. If you post your comments as a solution, we don't get notified.

I had to past your code into SQL Management Studio to see the real issue. The word DAY is a keyword in SQL Server and you are trying to use it as a column name. Anytime you have a keyword or reserved word (which will turn blue or pink in SQL Server Management Studio) being uses as a column name, you will need to escape it or change the name.

So if you change these lines:

DATEADD(day, nn.N, 0) AS day


to

DATEADD(day, nn.N, 0) AS [day]



This should resolve your issues.


Thanks virusstom for reply

i have changed day to Empday
But still getting same error
following line of code i have changed

,DATEADD(day, nn.N, 0) AS Empday

Is there any more changes..?

Thanks in advance.

now code is look like this.

SELECT     
	n.N
	, DATEADD(day, n.N, 0) AS day -- nn is incorrect
	, n.EnrollNo
	, n.EMachineNo
	, Start.StartDT
	, Finish.FinishDT
	, CAST(Finish.FinishDT - Start.StartDT AS time) AS ElapsedTime
FROM (
	SELECT     
		nn.N
		, DATEADD(day, nn.N, 0) AS Empday
		, t_1.EnrollNo
		, t_1.EMachineNo
	FROM dbo.Number AS nn 

	CROSS JOIN (
		SELECT DISTINCT 
			tt.EnrollNo
			, tt.EMachineNo
		FROM dbo.tblEmpGenralLog AS tt
	) AS t_1                       
	WHERE      
		(dateadd( day, nn.N, 0 ) >= '1900-05-01'
		and dateadd( day, nn.N, 0 ) < '2030-06-01'
) AS n 
LEFT OUTER JOIN (
	-- why are you not using trable alias's in here
	SELECT     
		EnrollNo
		, EMachineNo
		, DATEDIFF(day, 0, EmpDateTime) AS StartDayNo -- DateTime is a reserved word and thus a bad choice for a column name. You need to delimit this
		, MIN(EmpDateTime) AS StartDT
	FROM dbo.tblEmpGenralLog AS t
	GROUP BY 
		DATEDIFF(day, 0, EmpDateTime)
		, EnrollNo
		, EMachineNo
	) AS Start 
	ON Start.StartDayNo = n.N 
	AND Start.EnrollNo = n.EnrollNo 
	AND Start.EMachineNo = n.EMachineNo 
LEFT OUTER JOIN (
	-- why are you not using trable alias's in here
	SELECT     
		EnrollNo
		, EMachineNo
		, DATEDIFF(day, 0, EmpDateTime) AS FinishDayNo -- DateTime is a reserved word and thus a bad choice for a column name. You need to delimit this
		, MAX(EmpDateTime) AS FinishDT
	FROM dbo.tblEmpGenralLog AS t1
	GROUP BY 
		DATEDIFF(day, 0, EmpDateTime)
		, EnrollNo
		, EMachineNo
	) AS Finish 
	ON Finish.FinishDayNo = n.N 
	AND Finish.EnrollNo = n.EnrollNo 
	AND Finish.EMachineNo = n.EMachineNo
;


这篇关于试图创建一个视图，但得到和SQL错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆