August 27, 2008 at 8:35 am
I have the following stored procedure and it works great, but it names all of my joins as "Exprxxxx." I have been trying to alias them, but I'm not having any luck. I put one example in there of what I've been trying. It is in bold. I've tried it with and without quotes around the desired column name. Does anyone know what I'm doing wrong? I get the following error listed 3 times in the error prompt:
Error 107: The column prefix 'dbo.tblTicketStatus' does not match with a table name or alias name used in the query.
Thanks for your help!!
CREATE PROCEDURE usp_rptOpenTicketsByYard
AS
SELECT
dbo.tblTickets.TicketNumber,
dbo.tblTickets.ProjectTicketOption,
dbo.tblTickets.[User],
dbo.tblTickets.ContactInfo,
dbo.tblTickets.Yard,
dbo.tblTickets.CallType,
dbo.tblTickets.CallTypeSub,
dbo.tblTickets.Summary,
dbo.tblTickets.AssignedOn,
dbo.tblTickets.LastUpdatedOn,
dbo.tblTickets.ClosedOn,
dbo.tblTickets.TicketCreator,
dbo.tblTickets.Status,
dbo.tblTicketStatus.Status,
dbo.tblTicketPriority.Priority,
dbo.tblCallTypeSub.[Description],
dbo.tblTicketCallTypes.CallType,
dbo.tblUsers.First_Name,
dbo.tblUsers.Last_Name,
dbo.tblYardInfo.YardName,
dbo.tblTickets.CreatedOn,
dbo.tblTickets.Yard,
dbo.tblTicketPriority.PriorityStatus,
CASE
WHEN dbo.tbltickets.ProjectTicketOption = 0
THEN "Ticket"
ELSE "Project"
END
AS "Type"
FROM
dbo.tblTickets
RIGHT JOIN dbo.tblTicketStatus AS Status ON dbo.tblTicketStatus.StatusID = dbo.tblTickets.Status
RIGHT JOIN dbo.tblTicketPriority ON dbo.tblTicketPriority.PriorityID = dbo.tblTickets.Priority
RIGHT JOIN dbo.tblCallTypeSub ON dbo.tblCallTypeSub.CallTypeSubID = dbo.tblTickets.CallTypeSub
RIGHT JOIN dbo.tblTicketCallTypes ON dbo.tblTicketCallTypes.CallTypeID = dbo.tblTickets.CallType
RIGHT JOIN dbo.tblUsers ON dbo.tblUsers.UserID = dbo.tblTickets.[User]
INNER JOIN dbo.tblYardInfo ON dbo.tblYardInfo.YardNumber = dbo.tblTickets.Yard
WHERE
((dbo.tblTicketStatus.Status <> N'closed') AND
(dbo.tblTickets.Yard = N'1004'))
GO
August 27, 2008 at 8:47 am
Once you have the alias, use the alias.column_name format. Also, avoid putting aliases that are reserved keywords, like status
August 27, 2008 at 9:02 am
Do I have the alias in the correct spot to rename the Exprxxxx as the column heading for the joined column?
August 27, 2008 at 9:13 am
When I use this format and change all of the names of tables to match the Alias, the Syntax Check is successful, but it does not rename the expressions in the column headings for the joins. Is there some other way to do this?
August 27, 2008 at 12:32 pm
Never mind. I just modified my reports to use the new names. Thanks for all the help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply