FIX: Use of the RIGHT Function Within an ANSI Join Fails

ID: Q193757

The information in this article applies to:

Microsoft SQL Server versions 6.5 Service Pack 3 and later, 6.5 Service Pack 4 and later

BUG #: 17401 (SQLBUG_65)

SYMPTOMS

Using the RIGHT function within an ANSI join clause fails with a syntax error on servers with SQL Server 6.5 Service Pack 3 or Service Pack 4 installed.

WORKAROUND

To work around this problem, do either of the following:

Recode the query to use traditional (that is, non-ANSI) join syntax.

-or-

Consider whether the SUBSTRING function could be used. This would only be possible if the data value within the column was guaranteed to be of fixed length. For example, the au_id column in the authors table in the pubs sample database always contains a value of 11 bytes. So in this case, SUBSTRING (au_id,8,4) would always return the same values as RIGHT(au_id, 4).

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 3 and Service Pack 4. This problem has been corrected in the latest U.S. Service Pack 5a for SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The following is an example of a query that demonstrates this problem:


   SELECT a.au_lname, t.title_id
   FROM authors a JOIN titleauthor t
   ON right(t.au_id,11) = right(a.au_id,11)

The following error is generated:

Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'right'.

By recoding the query to use traditional join syntax, the problem does not occur:


   SELECT a.au_lname, t.title_id
   FROM authors a, titleauthor t
   where right(t.au_id,11) = right(a.au_id,11)

Additional query words: prodsqlsp sp3 sp4 sp5


Keywords          : SSrvTran_SQL kbbug6.50 kbbug6.50.sp3 kbbug6.50.sp4 kbfix6.50.SP5 
Version           : winnt:6.5 Service Pack 3 and later,6.5 Service Pack 4 and later
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: May 4, 1999