Syntax Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Syntax Error

Author

Topic

Balatros
Starting Member

8 Posts

Posted - 2012-09-18 : 09:50:24

Hi all, Am making a query and it say is have "Incorrect syntax near '='" If anyone can see it and help me out I would appreciate it

SELECT
*
FROM
( SELECT
'Company Name : ' + JE.NAME AS CompanyName,
+ '' + '# of locals: ' + CAST(COUNT (CoJC.Code) AS varchar(25)) = (CoLocalCount) ,
-- JWL.NAME AS WorklocationName,
JWL.NAME AS WorklocationName,
--WLJC.Code AS WLLocalCode
COUNT(WLJC.Code) AS WLLocalCount
FROM dbo.Job_Employer JE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 10:34:37

is this the complete query ?

KH
[spoiler]Time is always against us[/spoiler]

Balatros
Starting Member

8 Posts

Posted - 2012-09-18 : 11:16:51

The Entire query is (have played with the Select up top since posting this morning.

SELECT *
FROM
(
SELECT 'Company Name : ' + JE.NAME AS CompanyName,
'# of locals: ' + (CAST(COUNT (CoJC.Code) AS varchar(25)) AS CoLocalCount ,

--
JWL.NAME AS WorklocationName,
--WLJC.Code AS WLLocalCode
COUNT(WLJC.Code) AS WLLocalCount
FROM dbo.Job_Employer JE
LEFT JOIN
(
SELECT
CASE
WHEN EntityID1 = 27 THEN EntityValueID1
WHEN EntityID2 = 27 THEN EntityValueID2
END AS WorklocationID,
CASE
WHEN EntityID1 = 8 THEN EntityValueID1
WHEN EntityID2 = 8 THEN EntityValueID2
END AS EmployerID
FROM
dbo.Job_EntityLinking JEL
WHERE
(
EntityID1 = 8
AND EntityID2 = 27
)
OR (
EntityID2 = 8
AND EntityID1 = 27
)
) AS EmpWl
ON JE.EmployerID = EmpWl.EmployerID
LEFT JOIN
dbo.Job_WorkLocation JWL
ON EmpWl.WorklocationID = JWL.WorkLocationID
LEFT JOIN
dbo.v_CDerivedChapterID CoChp
ON JE.EmployerID = CoChp.EntityValueID
AND CoChp.EntityID = 8
LEFT JOIN
dbo.Job_Chapter CoJC
ON CoChp.ChapterID = CoJC.ChapterID --
LEFT JOIN
dbo.v_DerivedChapterID WLChp
ON JWL.WorklocationID = WLChp.EntityValueID
AND WLChp.EntityID = 27
LEFT JOIN
dbo.Job_Chapter WLJC
ON WLChp.ChapterID = WLJC.ChapterID
WHERE
JE.Active = 1
OR JWL.Active = 1 --
-- AND ISNULL(JWL.Active,1) = 1 --
GROUP BY
JE.NAME,
-- Company Name
JWL.Name
-- Work Location Name ) AS InnerTable
WHERE
(
-- Include all "Case 1" conditions - Companies/Work Locations with more than one Local--
( CoLocalCount > 1 AND WLLocalCount = 0 )
) -- Limit to test companies only: --

) --
ORDER BY
CompanyName,
WorklocationName -- Note: -- EntityID -- 8 - Employer (Company) -- 27 - Worklocation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 11:26:55

[code]
SELECT *
FROM
(
SELECT 'Company Name : ' + JE.NAME AS CompanyName,
'# of locals: ' + CAST(COUNT (CoJC.Code) AS varchar(25)) AS CoLocalCount ,

--
JWL.NAME AS WorklocationName,
--WLJC.Code AS WLLocalCode
COUNT(WLJC.Code) AS WLLocalCount
FROM dbo.Job_Employer JE
LEFT JOIN
(
SELECT
CASE
WHEN EntityID1 = 27 THEN EntityValueID1
WHEN EntityID2 = 27 THEN EntityValueID2
END AS WorklocationID,
CASE
WHEN EntityID1 = 8 THEN EntityValueID1
WHEN EntityID2 = 8 THEN EntityValueID2
END AS EmployerID
FROM
dbo.Job_EntityLinking JEL
WHERE
(
EntityID1 = 8
AND EntityID2 = 27
)
OR (
EntityID2 = 8
AND EntityID1 = 27
)
) AS EmpWl
ON JE.EmployerID = EmpWl.EmployerID
LEFT JOIN
dbo.Job_WorkLocation JWL
ON EmpWl.WorklocationID = JWL.WorkLocationID
LEFT JOIN
dbo.v_CDerivedChapterID CoChp
ON JE.EmployerID = CoChp.EntityValueID
AND CoChp.EntityID = 8
LEFT JOIN
dbo.Job_Chapter CoJC
ON CoChp.ChapterID = CoJC.ChapterID --
LEFT JOIN
dbo.v_DerivedChapterID WLChp
ON JWL.WorklocationID = WLChp.EntityValueID
AND WLChp.EntityID = 27
LEFT JOIN
dbo.Job_Chapter WLJC
ON WLChp.ChapterID = WLJC.ChapterID
WHERE
JE.Active = 1
OR JWL.Active = 1 --
-- AND ISNULL(JWL.Active,1) = 1 --
GROUP BY JE.NAME,
-- Company Name
JWL.Name
)r
-- Work Location Name ) AS InnerTable
WHERE
(
-- Include all "Case 1" conditions - Companies/Work Locations with more than one Local--
( CoLocalCount > 1 AND WLLocalCount = 0 )
) -- Limit to test companies only: --

--
ORDER BY
CompanyName,
WorklocationName -- Note: -- EntityID -- 8 - Employer (Company) -- 27 - Worklocation
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-18 : 11:28:47

so have you resolved that syntax error ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 11:44:09

quote:
Originally posted by khtan

so have you resolved that syntax error ?

KH
[spoiler]Time is always against us[/spoiler]

the posted code had a few syntax issues

i've done some tweaks to fix it and latest posted code compiles fine now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Balatros
Starting Member

8 Posts

Posted - 2012-09-18 : 12:01:00

Odd it works for you? when I plug that it it gives me the error of trying to convert the '# of locals :1' to data type INT.

I wonder where it is breaking that..

when i put Brackets around the (CAST(COUNT (CoJC.Code) AS varchar(25)) AS CoLocalCount) it goes back to just saying "Error running query! (-2147217900) Incorrect syntax near the keyword 'AS'. Source: Microsoft OLE DB Provider for SQL Server Module: modMain Function: GetDatabaseQueryResult "

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-18 : 13:04:34

quote:
Originally posted by Balatros

Odd it works for you? when I plug that it it gives me the error of trying to convert the '# of locals :1' to data type INT.

I wonder where it is breaking that..

when i put Brackets around the (CAST(COUNT (CoJC.Code) AS varchar(25)) AS CoLocalCount) it goes back to just saying "Error running query! (-2147217900) Incorrect syntax near the keyword 'AS'. Source: Microsoft OLE DB Provider for SQL Server Module: modMain Function: GetDatabaseQueryResult "

I told it compiles not that it works succesfully!
the error you're getting is runtime error as its related to data and not something I could get from here without your tables/data

can i ask where exactly you're including this query as error doesnt seem to be standard error from SSMS

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Balatros
Starting Member

8 Posts

Posted - 2012-09-18 : 14:03:37

The way I have it set up at the moment is to have a batch file (which will be called every morning at 6:30) calling an XML file which calls data pulled from this SQL query (which is its own file). the actual Query works fine when i keep the bare bones of it (don't add in the data in quotations to make it more understandable), but Many of the people here have random resolutions on their PC's which means that the XML format would occasionally appear wonky to people who did not have the same size as what I wrote the spacing for.

hopefully that makes more sense to you, Also I know I have not been the best about describing the problem and whatnot so I wanted to say thank-you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources