SQL error
-
Hi all, I have a sql statement that occur error when I run in sql server 2005.... "The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes." update TMP_TABLE_1 set CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO) from TMP_TABLE_1 A, TMP_TABLE_2 B where A.CLASS_NO *= B.STUDENT_NO and A.REQUEST_ID = @requestID But How can I change the sql to Right outer join or left outer join. Please kindly help. Thanks
-
Hi all, I have a sql statement that occur error when I run in sql server 2005.... "The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes." update TMP_TABLE_1 set CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO) from TMP_TABLE_1 A, TMP_TABLE_2 B where A.CLASS_NO *= B.STUDENT_NO and A.REQUEST_ID = @requestID But How can I change the sql to Right outer join or left outer join. Please kindly help. Thanks
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A LEFT JOIN
TMP_TABLE_2 B
ON A.CLASS_NO = B.STUDENT_NO
where
A.CLASS_NO *= B.STUDENT_NO
and A.REQUEST_ID = @requestIDStability. What an interesting concept. -- Chris Maunder
-
update
TMP_TABLE_1
set
CLASS_NO = coalesce(B.CLASS_NO , B.STUDENT_NO)
from TMP_TABLE_1 A LEFT JOIN
TMP_TABLE_2 B
ON A.CLASS_NO = B.STUDENT_NO
where
A.CLASS_NO *= B.STUDENT_NO
and A.REQUEST_ID = @requestIDStability. What an interesting concept. -- Chris Maunder