Microsoft KB Archive/193757

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

Article ID: 193757

Article Last Modified on 2/24/2004

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 4
 * Microsoft SQL Server 6.5 Service Pack 3

-



This article was previously published under Q193757



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: kbbug kbfix kbsqlserv650sp5fix KB193757

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.