November 1, 2017 at 11:43 am
Greetings SQL Guru's... 1st post
I'm an Excel VBA programmer and very new to SQL...
I'm working with a pre-built Access App in which there are a lot of linked table to SQL 2008 Server.
I built a query in Access and pasted the SQL code into SQL Server window and I'm getting Syntax errors... I have fixed a few of them but still am unable to get past these:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'tri'.
The SQL Statement is as follows:
SELECT tri.AssocInfo.DB_ID, tri.AssocInfo.Assoc_ID, tri.AssocInfo.First_Nm + ' ' + tri.AssocInfo.Last_Nm AS [Full Name], tri.AssocInfo.Position, DateName(DW,Entered)) AS Day, tri.DailyHoursWorked.Entered, tri.DailyHoursWorked.HoursWorked
FROM tri.AssocInfo INNER JOIN tri.DailyHoursWorked ON tri.AssocInfo.DB_ID = tri.DailyHoursWorked.DB_ID
WHERE (((tri.AssocInfo.DB_ID) Like 'RA*') AND ((tri.AssocInfo.Assoc_ID) Like 'A*' Or (tri.AssocInfo.Assoc_ID) Like 'C*') AND ((tri.AssocInfo.Position) Like 'IR*') AND ((tri.DailyHoursWorked.Entered) Between '10/21/2017' And '10/30/2017'));
Thank you for any help
November 1, 2017 at 11:49 am
MarkW.Rhythm1 - Wednesday, November 1, 2017 11:43 AMGreetings SQL Guru's... 1st post
I'm an Excel VBA programmer and very new to SQL...
I'm working with a pre-built Access App in which there are a lot of linked table to SQL 2008 Server.
I built a query in Access and pasted the SQL code into SQL Server window and I'm getting Syntax errors... I have fixed a few of them but still am unable to get past these:Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'tri'.
The SQL Statement is as follows:
SELECT tri.AssocInfo.DB_ID, tri.AssocInfo.Assoc_ID, tri.AssocInfo.First_Nm + ' ' + tri.AssocInfo.Last_Nm AS [Full Name], tri.AssocInfo.Position, DateName(DW,Entered)) AS Day, tri.DailyHoursWorked.Entered, tri.DailyHoursWorked.HoursWorked
FROM tri.AssocInfo INNER JOIN tri.DailyHoursWorked ON tri.AssocInfo.DB_ID = tri.DailyHoursWorked.DB_ID
WHERE (((tri.AssocInfo.DB_ID) Like 'RA*') AND ((tri.AssocInfo.Assoc_ID) Like 'A*' Or (tri.AssocInfo.Assoc_ID) Like 'C*') AND ((tri.AssocInfo.Position) Like 'IR*') AND ((tri.DailyHoursWorked.Entered) Between '10/21/2017' And '10/30/2017'));
Thank you for any help
This would be a lot easier for you if you format your code. Remove unnecessary parenthesis and put each column, table and condition on an individual line. That would show you that you have characters that shouldn't be there. It's simple and easy and not something specific from SQL.
November 1, 2017 at 11:51 am
MarkW.Rhythm1 - Wednesday, November 1, 2017 11:43 AMGreetings SQL Guru's... 1st post
I'm an Excel VBA programmer and very new to SQL...
I'm working with a pre-built Access App in which there are a lot of linked table to SQL 2008 Server.
I built a query in Access and pasted the SQL code into SQL Server window and I'm getting Syntax errors... I have fixed a few of them but still am unable to get past these:Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'tri'.
The SQL Statement is as follows:
SELECT tri.AssocInfo.DB_ID, tri.AssocInfo.Assoc_ID, tri.AssocInfo.First_Nm + ' ' + tri.AssocInfo.Last_Nm AS [Full Name], tri.AssocInfo.Position, DateName(DW,Entered)) AS Day, tri.DailyHoursWorked.Entered, tri.DailyHoursWorked.HoursWorked
FROM tri.AssocInfo INNER JOIN tri.DailyHoursWorked ON tri.AssocInfo.DB_ID = tri.DailyHoursWorked.DB_ID
WHERE (((tri.AssocInfo.DB_ID) Like 'RA*') AND ((tri.AssocInfo.Assoc_ID) Like 'A*' Or (tri.AssocInfo.Assoc_ID) Like 'C*') AND ((tri.AssocInfo.Position) Like 'IR*') AND ((tri.DailyHoursWorked.Entered) Between '10/21/2017' And '10/30/2017'));
Thank you for any help
Try this:
SELECT
tri.AssocInfo.DB_ID
, tri.AssocInfo.Assoc_ID
, tri.AssocInfo.First_Nm + ' ' + tri.AssocInfo.Last_Nm AS [Full Name]
, tri.AssocInfo.Position
, DateName(DW,Entered) AS Day
, tri.DailyHoursWorked.Entered
, tri.DailyHoursWorked.HoursWorked
FROM
tri.AssocInfo
INNER JOIN tri.DailyHoursWorked
ON tri.AssocInfo.DB_ID = tri.DailyHoursWorked.DB_ID
WHERE
(
(tri.AssocInfo.DB_ID Like 'RA%')
AND (tri.AssocInfo.Assoc_ID Like 'A%'
OR tri.AssocInfo.Assoc_ID Like 'C%')
AND (tri.AssocInfo.Position Like 'IR%')
AND (tri.DailyHoursWorked.Entered Between '10/21/2017' And '10/30/2017')
);
There is more I would change but that would just be my SQL OCD kicking in.
November 1, 2017 at 12:09 pm
OMGee!!
Luis & Lynn,
I cannot thank the both of you enough!!!
I came here for help and enlightenment ~ and you provided both in my first post...
Thank You, both so very much!
And Lynn... you can impose your SQL OCD anytime you want ~ 🙂
November 1, 2017 at 12:22 pm
MarkW.Rhythm1 - Wednesday, November 1, 2017 12:09 PMOMGee!!Luis & Lynn,
I cannot thank the both of you enough!!!
I came here for help and enlightenment ~ and you provided both in my first post...
Thank You, both so very much!And Lynn... you can impose your SQL OCD anytime you want ~ 🙂
You're welcome. I hope that you can remember that the first part of fixing code is being able to read it easily.
To add more on that SQL OCD, I'm including a sample with notes. I'll let you decide if you want leading or trailing commas (just don't start that debate).
SELECT ai.DB_ID, --Use table alias for columns to maintain 2-part names (3-part and 4-part names are deprecated)
ai.Assoc_ID,
ai.First_Nm + ' ' + ai.Last_Nm AS [Full Name],
ai.Position,
DateName(DW, dhw.Entered) AS Day,
dhw.Entered,
dhw.HoursWorked
FROM tri.AssocInfo AS ai --Give alias to tables
INNER JOIN tri.DailyHoursWorked AS dhw ON ai.DB_ID = dhw.DB_ID
WHERE ai.DB_ID LIKE 'RA%' --Starts with RA
AND ai.Assoc_ID LIKE '[AC]%' --Starts with A or C
AND ai.Position LIKE 'IR%' --Starts with IR
AND dhw.Entered >= '20171021' --Dates in ISO 8601 compact format YYYYMMDD
AND dhw.Entered < '20171031'; --It's lower than the end date plus one to include times.
November 1, 2017 at 12:44 pm
Luis Cazares - Wednesday, November 1, 2017 12:22 PMMarkW.Rhythm1 - Wednesday, November 1, 2017 12:09 PMOMGee!!Luis & Lynn,
I cannot thank the both of you enough!!!
I came here for help and enlightenment ~ and you provided both in my first post...
Thank You, both so very much!And Lynn... you can impose your SQL OCD anytime you want ~ 🙂
You're welcome. I hope that you can remember that the first part of fixing code is being able to read it easily.
To add more on that SQL OCD, I'm including a sample with notes. I'll let you decide if you want leading or trailing commas (just don't start that debate).
SELECT ai.DB_ID, --Use table alias for columns to maintain 2-part names (3-part and 4-part names are deprecated)
ai.Assoc_ID,
ai.First_Nm + ' ' + ai.Last_Nm AS [Full Name],
ai.Position,
DateName(DW, dhw.Entered) AS Day,
dhw.Entered,
dhw.HoursWorked
FROM tri.AssocInfo AS ai --Give alias to tables
INNER JOIN tri.DailyHoursWorked AS dhw ON ai.DB_ID = dhw.DB_ID
WHERE ai.DB_ID LIKE 'RA%' --Starts with RA
AND ai.Assoc_ID LIKE '[AC]%' --Starts with A or C
AND ai.Position LIKE 'IR%' --Starts with IR
AND dhw.Entered >= '20171021' --Dates in ISO 8601 compact format YYYYMMDD
AND dhw.Entered < '20171031'; --It's lower than the end date plus one to include times.
I used to put commas at the end, but since using Redgate SQL Prompt (and yes, I could still put them at the end) I moved them to the front. Mainly because I have found myself doing a lot of commenting of columns and it has just been easier with them leading the columns (except when I have to comment the first column, so 50/50 choice).
Using Ctrl-K, Ctrl-Y is awesome.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply