| 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/ |
|
|
|
|
SQL Syntax Error