Click here to Skip to main content
15,911,306 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.

SQL
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
;
Posted
Comments
virusstorm 19-May-15 12:13pm    
You have "day" defined in your sub query which is causing binding issue. Change the column name in your sub query and you should be good.

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:
SQL
DATEADD(day, nn.N, 0) AS day

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


This should resolve your issues.
 
Share this answer
 
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.

SQL
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
;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900