SQL 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 2005 Forums

Transact-SQL (2005)

SQL Syntax Error

Author

Topic

hellfire45
Starting Member

10 Posts

Posted - 2015-03-04 : 13:23:54

I'm getting a syntax error on this query below. It says "Syntax error on Join". I'm writing this the way I think it would be written in My SQL server 2014 so maybe its not fitting into Access. Can somebody check for me? Thank you so much!

SELECT FCL.EntityID as "File#", FCL.GroupNumNum as "Numer of Actions", FCL.ActionType as "Action Type", FCL.StartAction as "Action StartDate", FCL.End, ENT.Party3IDText as "Client ID"
FROM dbo_FCLIT as "FCL"
LEFT OUTER JOIN dbo_Entities as "ENT" ON FCL.EntityNum = ENT.EntityNum
Where FCL.ActionType = "Answer Affir Defense" AND Party3IDText ="245204" OR Party3IDText = "327599";

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 13:36:04

Switch your double quotes to single quotes. Put square brackets around reserved words, such as End.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

hellfire45
Starting Member

10 Posts

Posted - 2015-03-04 : 14:01:59

Thanks. Now I've got it written like below but the error still persists. It is highlighting the first instance of FCL.

SELECT FCL.EntityID as 'File#', FCL.GroupNumNum as 'Numer of Actions', FCL.ActionType as 'Action Type', FCL.StartAction as 'Action StartDate', FCL.[End], ENT.Party3IDText as 'Client ID'
FROM dbo_FCLIT as 'FCL'
LEFT OUTER JOIN dbo_Entities as 'ENT' ON FCL.EntityNum = ENT.EntityNum
Where FCL.ActionType = 'Answer Affir Defense' AND Party3IDText ='245204' OR Party3IDText = '327599';

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 14:04:09

Remove the single quotes from the table aliases. If you need to use reserved words or special characters in aliases, use square brackets.

FROM db_FCLIT as FCL
LEFT JOIN dbo_Entities as ENT

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

hellfire45
Starting Member

10 Posts

Posted - 2015-03-04 : 14:23:39

I got it to work by just removing the table aliasing for now. Now I'm getting a different error. "Syntax error (missing operator) in query expression "client name" = Case......

Where is the syntax error on the CASE statement? I thought it was correct. Thank you!

SELECT dbo_FCLIT.EntityID as 'File#', dbo_FCLIT.GroupNumNum as 'Numer of Actions', dbo_FCLIT.ActionType as 'Action Type', dbo_FCLIT.StartAction as 'Action StartDate', dbo_FCLIT.[End], dbo_Entities.Party3IDText as 'Client ID', 'Client Name' = 

CASE
WHEN dbo_Entities.Party3IDText ='245204' THEN 'SN SERVICING CORPORATION'
WHEN dbo_Entities.Party3IDText ='327599' THEN 'STATEBRIDGE'
ELSE 'Not Found'
END

FROM dbo_FCLIT
LEFT OUTER JOIN dbo_Entities ON dbo_FCLIT.EntityNum = dbo_Entities.EntityNum
Where (dbo_FCLIT.ActionType = 'Answer Affir Defense') AND ((dbo_Entities.Party3IDText ='245204') OR (dbo_Entities.Party3IDText = '327599'));

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 14:29:12

Is this an Access query? That error is not a SQL Server error, and the syntax checks out fine in SQL Server Management Studio. I can only help with SQL Server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

hellfire45
Starting Member

10 Posts

Posted - 2015-03-04 : 14:48:42

Yes, its an access query unfortunately. I thought it seemed correct in terms of SQL server syntax.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-04 : 14:51:29

Not sure what's available in Access, but your syntax is fine in SQL Server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources