July 17, 2008 at 3:03 pm
In my following query, everything works up to the joins. It gives me an error in the Syntax checker that says: "Incorrect Syntax Near Keyword 'user' "
I copied the joins over directly from the SQL in my DB and based on what I've read online for the past few hours, they seem to be written correctly for even T-SQL. Thanks so much for your help!
CREATE PROCEDURE usp_rptOpenTicketsByYard
AS
SELECT tblTickets.*, tblTicketStatus.status, tblTicketPriority.priority, tblCallTypeSub.Description, tblTicketCallTypes.CallType, tblUsers.First_Name, tblUsers.Last_Name, tblYardInfo.YardName, tblTickets.CreatedOn, tblTickets.Yard, tblTicketPriority.PriorityStatus
CASE
WHEN tbltickets.ProjectTicketOption = 0
THEN "Ticket"
ELSE "Project"
END
FROM tblYardInfo
INNER JOIN (tblTicketCallTypes RIGHT JOIN (tblUsers RIGHT JOIN (tblTicketPriority RIGHT JOIN (tblTicketStatus RIGHT JOIN (tblCallTypeSub RIGHT JOIN tblTickets ON tblCallTypeSub.CallTypeSubID = tblTickets.CallTypeSub) ON tblTicketStatus.StatusID = tblTickets.Status) ON tblTicketPriority.PriorityID = tblTickets.Priority) ON tblUsers.UserID = tblTickets.User) ON tblTicketCallTypes.CallTypeID = tblTickets.CallType) ON tblYardInfo.YardNumber = tblTickets.Yard
GO
July 17, 2008 at 3:07 pm
PS- This query will be a part of a stored procedure.
July 17, 2008 at 5:02 pm
I had to reformat this to get an idea of what is going on. Here is the reformatted query:
CREATE PROCEDURE usp_rptOpenTicketsByYard
AS
SELECT tblTickets.*
,tblTicketStatus.status
,tblTicketPriority.priority
,tblCallTypeSub.Description
,tblTicketCallTypes.CallType
,tblUsers.First_Name
,tblUsers.Last_Name
,tblYardInfo.YardName
,tblTickets.CreatedOn
,tblTickets.Yard
,tblTicketPriority.PriorityStatus
CASE
WHEN tbltickets.ProjectTicketOption = 0
THEN "Ticket"
ELSE "Project"
END
FROM tblYardInfo
INNER JOIN tblTicketCallTypes ON tblTicketCallTypes.CallTypeID = tblTickets.CallType
RIGHT JOIN tblUsers ON tblUsers.UserID = tblTickets.User
RIGHT JOIN tblTicketPriority ON tblTicketPriority.PriorityID = tblTickets.Priority
RIGHT JOIN tblTicketStatus ON tblTicketStatus.StatusID = tblTickets.Status
RIGHT JOIN tblCallTypeSub ON tblCallTypeSub.CallTypeSubID = tblTickets.CallTypeSub
RIGHT JOIN tblTickets ON tblYardInfo.YardNumber = tblTickets.Yard
GO
The first thing I noticed after reformatting is that you are missing a comma before the CASE expression. The second thing I noticed was how the joins were built, the third thing I noticed is that you used a reserved word User, and the fourth thing I noticed was the lack of any aliasing.
I think your problem is both the missing comma and the use of a reserved word for a column name.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 17, 2008 at 11:23 pm
You also are going to get an error if you use the symbol " to indicate the existence of a string, the compile process assumes that the word between the "" symbol is the name of a column, you should use the ' symbol instead of " when you are indicating a string.
But ... if you want to use the " symbol to indicate strings in your procedure you can use the following option when you are going compile your procedure:
set QUOTED_IDENTIFIER OFF
PD: Perdonen mi inglés eh ... :hehe:
Saludos,
José Miguel Gutiérrez Hilaccama
Database Developer - MCTS SQL Server 2005
No way Jose? ... bah
July 18, 2008 at 12:04 pm
Jose-cin (7/17/2008)
You also are going to get an error if you use the symbol " to indicate the existence of a string, the compile process assumes that the word between the "" symbol is the name of a column, you should use the ' symbol instead of " when you are indicating a string.But ... if you want to use the " symbol to indicate strings in your procedure you can use the following option when you are going compile your procedure:
set QUOTED_IDENTIFIER OFF
PD: Perdonen mi inglés eh ... :hehe:
Saludos,
Jose - good catch, I completely missed that one.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 11:14 am
Thank you guys for all your help! I've gotten the query working so far, but I'm trying to figure out Aliasing on Joins. I can't seem to find any good info on it online. If someone could point me in the right direction, or show me briefly how to do it, I think I could figure it out.
Thanks!!
July 22, 2008 at 11:27 am
gomikem (7/22/2008)
Thank you guys for all your help! I've gotten the query working so far, but I'm trying to figure out Aliasing on Joins. I can't seem to find any good info on it online. If someone could point me in the right direction, or show me briefly how to do it, I think I could figure it out.Thanks!!
You can lookup 'aliases' in BOL and it will show you everything you need to know.
Basically, aliasing is giving a different name for an object. You can alias column names in the select and tables in the from clause. A basic example is:
SELECT a.column1 AS col1
,b.column1 AS col2
FROM table1 AS a
INNER JOIN table2 AS b ON b.id = a.id
In the above, I have aliased table1 AS a, table2 AS b - column1 from table1 (using the alias 'a') as col1 and column1 from table2 (using the alias 'b') as col2.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 1:52 pm
My joins worked when I left them in the SQL from the Access query and escaped "user", but when I tried to create aliases, I had a lot of trouble, so I recreated my joins as shown in the response above. When I copied and pasted those joins into my procedure, I got the following error:
"The column prefix, 'TblTickets' does not match with a table name or alias name used in the query"
I got this error several times. When I used your example and rewrote the joins on my own as:
CREATE PROCEDURE usp_rptOpenTicketsByYard
AS
SELECT tblTickets.*, tblTicketStatus.status, tblTicketPriority.priority, tblCallTypeSub.Description, tblTicketCallTypes.CallType, tblUsers.First_Name, tblUsers.Last_Name, tblYardInfo.YardName, tblTickets.CreatedOn, tblTickets.Yard, tblTicketPriority.PriorityStatus,
CASE
WHEN tbltickets.ProjectTicketOption = 0
THEN 'Ticket'
ELSE 'Project'
END
FROM tblYardInfo
INNER JOIN tblTicketCallTypes ON tblTicketCallTypes.CallTypeID = tblTickets.CallType
RIGHT JOIN tblUsers tblUsers.UserID = tblTickets.[User]
RIGHT JOIN tblTicketPriority ON tblTicketPriority.PriorityID = tblTickets.Priority
RIGHT JOIN tblTicketStatus tblTicketStatus.StatusID = tblTickets.Status
RIGHT JOIN tblCallTypeSub ON tblCallTypeSub.CallTypeSubID = tblTickets.CallTypeSub
RIGHT JOIN tblTickets ON tblYardInfo.YardNumber = tblTickets.Yard
GO
I get the following error:
"Error 170: Line 15: Incorrect syntax near '.'.
Line 15 is bolded above.
If I delete line 15, I get the same error for each subsequent line.
Do I need to do something to terminate these lines or something? Is my "User" not escaped properly?
Thanks for all your help so far!!!
July 22, 2008 at 1:56 pm
Also, I'm looking to create an alias for my "CASE" statement, but I can't seem to get it to pass the Syntax Checker. I swear I look all over the internet for this stuff, but can never find anything that works. I always seem to be just a tiny bit off. Thanks for your help!
July 22, 2008 at 2:28 pm
Okay, I reformatted your query and used aliases. What I found is another problem you have:
CREATE PROCEDURE usp_rptOpenTicketsByYard
AS
SELECT t.*
,ts.status
,tp.priority
,cts.Description
,tct.CallType
,u.First_Name
,u.Last_Name
,yi.YardName
,t.CreatedOn
,t.Yard
,tp.PriorityStatus
,CASE WHEN t.ProjectTicketOption = 0
THEN 'Ticket'
ELSE 'Project'
END AS ProjectTicketOption
FROM tblYardInfo AS yi
INNER JOIN tblTicketCallTypes AS tct ON tct.CallTypeID = t.CallType
RIGHT JOIN tblUsers AS u ON u.UserID = t.[User]
RIGHT JOIN tblTicketPriority AS tp ON tp.PriorityID = t.Priority
RIGHT JOIN tblTicketStatus AS ts ON ts.StatusID = t.Status
RIGHT JOIN tblCallTypeSub AS cts ON cts.CallTypeSubID = t.CallTypeSub
RIGHT JOIN tblTickets AS t ON yi.YardNumber = t.Yard
GO
In the above, you have the table 'tblTickets' last as an outer join. This is not going to work because you are relating the other tables to columns in this table (e.g. tblUsers is related to the User column in tblTickets).
So, I modified your query as follows:
CREATE PROCEDURE usp_rptOpenTicketsByYard
AS
SELECT t.*
,ts.status
,tp.priority
,cts.Description
,tct.CallType
,u.First_Name
,u.Last_Name
,yi.YardName
,t.CreatedOn
,t.Yard
,tp.PriorityStatus
,CASE WHEN t.ProjectTicketOption = 0
THEN 'Ticket'
ELSE 'Project'
END AS ProjectTicketOption
FROM tblTickets AS t
INNER JOIN tblTicketCallTypes AS tct ON tct.CallTypeID = t.CallType
LEFT JOIN tblUsers AS u ON u.UserID = t.[User]
LEFT JOIN tblTicketPriority AS tp ON tp.PriorityID = t.Priority
LEFT JOIN tblTicketStatus AS ts ON ts.StatusID = t.Status
LEFT JOIN tblCallTypeSub AS cts ON cts.CallTypeSubID = t.CallTypeSub
LEFT JOIN tblYardInfo AS yi ON yi.YardNumber = t.Yard
GO
I hope that is the right relationships - but at least this will show you how to use aliases and see what is going on.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 3:12 pm
OK, now I'm running the following and getting a similar error:
CREATE PROCEDURE usp_rptOpenTicketsByYard
AS
SELECT tblTickets.*, tblTicketStatus.status, tblTicketPriority.priority, tblCallTypeSub.Description, tblTicketCallTypes.CallType, tblUsers.First_Name, tblUsers.Last_Name, tblYardInfo.YardName, tblTickets.CreatedOn, tblTickets.Yard, tblTicketPriority.PriorityStatus,
CASE
WHEN tbltickets.ProjectTicketOption = 0
THEN 'Ticket'
ELSE 'Project'
END
FROM tblTickets
INNER JOIN tblTicketCallTypes ON tblTicketCallTypes.CallTypeID = tblTickets.CallType
LEFT JOIN tblUsers tblUsers.UserID = tblTickets.[User]
LEFT JOIN tblTicketPriority ON tblTicketPriority.PriorityID = tblTickets.Priority
LEFT JOIN tblTicketStatus tblTicketStatus.StatusID = tblTickets.Status
LEFT JOIN tblCallTypeSub ON tblCallTypeSub.CallTypeSubID = tblTickets.CallTypeSub
LEFT JOIN tblYardinfo ON tblYardInfo.YardNumber = tblTickets.Yard
The error is: Line 15/ Incorrect syntax near "tblUsers."
I also added the AS statement to the CASE statement, but it does not name the column and makes all the data in that column show up as #NAME?
I'm using MS SQL Server 2000 if that makes a difference.
Thanks for all your help!! Once I get this query working, the rest should be a lot easier for me to do on my own.
July 22, 2008 at 4:23 pm
You missed an " ON " on line 15.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 22, 2008 at 4:35 pm
Oops, forgot that. Now it gives the attached error.
July 22, 2008 at 5:39 pm
That error tells me you have made additional changes and now you have a situation where the query is trying to use your tables as aliases and cannot figure it out.
What happens if you take the second query I gave you and run that?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply