SQL - Outer Apply does not work in SQL Server 2000?

Question

I previously tested in my company test server to create a new view design

SELECT     
    a.[CourseID], a.[Course Code], a.[Course Title] AS [Course Description], 
    a.[Result], a.[Employee No], a.[Employee Name], a.[Section], 
    CONVERT(VARCHAR, b.ActStartDate, 105) AS [Last Training Date], 
    CONVERT(VARCHAR, b.ValidTo, 105) AS [Validity Period], 
    a.[Status], a.[Flag]
FROM
    (SELECT     
         c.CourseID, c.CourseCode AS [Course Code], c.CourseTitle AS [Course Title], 
         c.CourseCode + ' - ' + c.CourseTitle AS Result, 
         b.EMPLOYEE_NO AS [Employee No], b.EMPLOYEE_NAME AS [Employee Name], 
         b.SECTION_CODE + ' - ' + b.SECTION_DESCRIPTION AS Section, 
         b.EMPLOYEE_STATUS AS Status, a.Flag
     FROM         
         dbo.ZOJT_EMP_COURSE AS a 
     INNER JOIN
         dbo.vEMPLOYEE_PROFILE AS b ON a.Employee_No = b.EMPLOYEE_NO 
     INNER JOIN
         dbo.tTMS_Course AS c ON c.CourseCode = a.CourseCode) A 
OUTER APPLY
    (SELECT TOP 1 *
     FROM
         (SELECT     
              b.CourseID, a.EmpNo, b.ActStartDate, a.ValidTo
          FROM         
              dbo.tTMS_Training_Record AS a 
          INNER JOIN
              (SELECT     
                   SchID, CourseID, MAX(ActStartDate) AS ActStartDate
               FROM          
                   dbo.tTMS_Training_Schedule AS z
               GROUP BY 
                   SchID, CourseID) AS b ON a.SchID = b.SchID) AS y
    WHERE 
        ActStartDate IS NOT NULL 
        AND CourseID = A.CourseID 
        AND EmpNo = A.[Employee No]
    ORDER BY 
        ActStartDate DESC, ValidTo DESC) B

However when I try to move this query in my live environment, I get 2 incorrect syntax errors.

One near Outer Apply and another near Order By ActStartDate. When I asked about this issue, my seniors thinks it's probably due to the fact that the test server I used to test this was SQL Server 2008 while the live server is SQL Server 2000.

The question now is, is that really the case? If yes, is there a workaround? Note that I have no way or authority to upgrade my live server.

Answer 1

You got the error because SQL Server allow APPLY operator from SQL Server 2005 to later version. To use APPLY, the database compatibility level must be at least 90 which is available from 2005 to later. you have to replace outer apply by using sub query

    SELECT 

        a.[CourseID], a.[Course Code], a.[Course Title] AS [Course Description], a.[Result], a.[Employee No], a.[Employee Name], a.[Section], CONVERT(varchar, b.ActStartDate, 
                      105) AS [Last Training Date], CONVERT(varchar, b.ValidTo, 105) AS [Validity Period], a.[Status], a.[Flag]
    FROM         (SELECT     c.CourseID, c.CourseCode AS [Course Code], c.CourseTitle AS [Course Title], c.CourseCode + ' - ' + c.CourseTitle AS Result, b.EMPLOYEE_NO AS [Employee No], 
                      b.EMPLOYEE_NAME AS [Employee Name], b.SECTION_CODE + ' - ' + b.SECTION_DESCRIPTION AS Section, b.EMPLOYEE_STATUS AS Status, a.Flag
    FROM         dbo.ZOJT_EMP_COURSE AS a INNER JOIN
                      dbo.vEMPLOYEE_PROFILE AS b ON a.Employee_No = b.EMPLOYEE_NO INNER JOIN
                      dbo.tTMS_Course AS c ON c.CourseCode = a.CourseCode) 
    A left join
                          (SELECT  max(ActStartDate) as ActStartDate,
  max(ValidTo) as ValidTo,CourseID,EmpNo
                            FROM          (SELECT     b.CourseID, a.EmpNo, b.ActStartDate, a.ValidTo
                                            FROM         dbo.tTMS_Training_Record AS a INNER JOIN
                                            (SELECT     SchID, CourseID, MAX(ActStartDate) AS ActStartDate
                                            FROM          dbo.tTMS_Training_Schedule AS z
                                            GROUP BY SchID, CourseID) AS b ON a.SchID = b.SchID) as y

                            WHERE ActStartDate IS NOT NULL 
                           --AND CourseID = A.CourseID AND EmpNo = A.[Employee No]
                            --ORDER BY ActStartDate DESC, ValidTo DESC
                         group by CourseID,EmpNo
                         ) B 
                         on a.courseid = b.courseid and a.[employee no] = b.empno