Need help to convert this Outer apply to Left Join

Question

I have been struggling with this for the past days. I am trying to use the following SQL query which is used on SQL Server 2008 into SQL Server 2000.

The problem occurred because SQL Server 2000 doesn't support outer apply command. Is it possible to convert my query so it'll be applicable on SQL Server 2000 ?

 SELECT [ta_kib_b].*,
       ( CASE
           WHEN newkondisi IS NULL THEN kondisi
           ELSE newkondisi
         END ) AS lastKondisi,
       ( CASE
           WHEN kondisisebelumta2018 IS NULL THEN kondisi
           ELSE kondisisebelumta2018
         END ) AS KondisiSebelumTA2018,
       lastupdate,
       tanggalperubahankondisiterakhir
FROM   [ta_kib_b]
       OUTER apply (SELECT TOP 1 [ta_kibbr].kondisi AS NewKondisi
                    FROM   [ta_kibbr]
                    WHERE  [ta_kib_b].kd_bidang = [ta_kibbr].kd_bidang
                           AND [ta_kib_b].kd_unit = [ta_kibbr].kd_unit
                           AND [ta_kib_b].kd_sub = [ta_kibbr].kd_sub
                           AND [ta_kib_b].kd_upb = [ta_kibbr].kd_upb
                           AND [ta_kib_b].kd_aset1 = [ta_kibbr].kd_aset1
                           AND [ta_kib_b].kd_aset2 = [ta_kibbr].kd_aset2
                           AND [ta_kib_b].kd_aset3 = [ta_kibbr].kd_aset3
                           AND [ta_kib_b].kd_aset4 = [ta_kibbr].kd_aset4
                           AND [ta_kib_b].kd_aset5 = [ta_kibbr].kd_aset5
                           AND [ta_kib_b].no_register = [ta_kibbr].no_register
                           AND kd_riwayat = 1
                           AND [ta_kibbr].tgl_dokumen <= '2018-12-31'
                    ORDER  BY [ta_kibbr].tgl_dokumen DESC) AS bb
       OUTER apply (SELECT TOP 1 [ta_kibbr].kondisi AS KondisiSebelumTA2018
                    FROM   [ta_kibbr]
                    WHERE  [ta_kib_b].kd_bidang = [ta_kibbr].kd_bidang
                           AND [ta_kib_b].kd_unit = [ta_kibbr].kd_unit
                           AND [ta_kib_b].kd_sub = [ta_kibbr].kd_sub
                           AND [ta_kib_b].kd_upb = [ta_kibbr].kd_upb
                           AND [ta_kib_b].kd_aset1 = [ta_kibbr].kd_aset1
                           AND [ta_kib_b].kd_aset2 = [ta_kibbr].kd_aset2
                           AND [ta_kib_b].kd_aset3 = [ta_kibbr].kd_aset3
                           AND [ta_kib_b].kd_aset4 = [ta_kibbr].kd_aset4
                           AND [ta_kib_b].kd_aset5 = [ta_kibbr].kd_aset5
                           AND [ta_kib_b].no_register = [ta_kibbr].no_register
                           AND kd_riwayat = 1
                           AND [ta_kibbr].tgl_dokumen <= '2017-12-31'
                    ORDER  BY [ta_kibbr].tgl_dokumen DESC) AS cc
       OUTER apply (SELECT TOP 1 [ta_kibbr].tgl_dokumen AS
                                 TanggalPerubahanKondisiTerakhir
                    FROM   [ta_kibbr]
                    WHERE  [ta_kib_b].kd_bidang = [ta_kibbr].kd_bidang
                           AND [ta_kib_b].kd_unit = [ta_kibbr].kd_unit
                           AND [ta_kib_b].kd_sub = [ta_kibbr].kd_sub
                           AND [ta_kib_b].kd_upb = [ta_kibbr].kd_upb
                           AND [ta_kib_b].kd_aset1 = [ta_kibbr].kd_aset1
                           AND [ta_kib_b].kd_aset2 = [ta_kibbr].kd_aset2
                           AND [ta_kib_b].kd_aset3 = [ta_kibbr].kd_aset3
                           AND [ta_kib_b].kd_aset4 = [ta_kibbr].kd_aset4
                           AND [ta_kib_b].kd_aset5 = [ta_kibbr].kd_aset5
                           AND [ta_kib_b].no_register = [ta_kibbr].no_register
                           AND kd_riwayat = 1
                           AND [ta_kibbr].tgl_dokumen <= '2018-12-31'
                    ORDER  BY [ta_kibbr].tgl_dokumen DESC) AS dd
       OUTER apply (SELECT TOP 1 [ta_kibbr].tgl_dokumen AS LastUpdate
                    FROM   [ta_kibbr]
                    WHERE  [ta_kib_b].kd_bidang = [ta_kibbr].kd_bidang
                           AND [ta_kib_b].kd_unit = [ta_kibbr].kd_unit
                           AND [ta_kib_b].kd_sub = [ta_kibbr].kd_sub
                           AND [ta_kib_b].kd_upb = [ta_kibbr].kd_upb
                           AND [ta_kib_b].kd_aset1 = [ta_kibbr].kd_aset1
                           AND [ta_kib_b].kd_aset2 = [ta_kibbr].kd_aset2
                           AND [ta_kib_b].kd_aset3 = [ta_kibbr].kd_aset3
                           AND [ta_kib_b].kd_aset4 = [ta_kibbr].kd_aset4
                           AND [ta_kib_b].kd_aset5 = [ta_kibbr].kd_aset5
                           AND [ta_kib_b].no_register = [ta_kibbr].no_register
                           AND [ta_kibbr].tgl_dokumen <= '2019-12-31'
                    ORDER  BY [ta_kibbr].tgl_dokumen DESC) AS ee  

Any help appreciated, thanks

Answer 1

You can move the outer apply to correlated sub-query.

 SELECT [ta_kib_b].*,
       ( CASE
           WHEN newkondisi IS NULL THEN kondisi
           ELSE newkondisi
         END ) AS LASTKONDISI
FROM   (SELECT [ta_kib_b].*,
               (SELECT TOP 1 [ta_kibbr].kondisi AS NEWKONDISI
                FROM   [ta_kibbr]
                WHERE  [ta_kib_b].kd_bidang = [ta_kibbr].kd_bidang
                       AND [ta_kib_b].kd_unit = [ta_kibbr].kd_unit
                       AND [ta_kib_b].kd_sub = [ta_kibbr].kd_sub
                       AND [ta_kib_b].kd_upb = [ta_kibbr].kd_upb
                       AND [ta_kib_b].kd_aset1 = [ta_kibbr].kd_aset1
                       AND [ta_kib_b].kd_aset2 = [ta_kibbr].kd_aset2
                       AND [ta_kib_b].kd_aset3 = [ta_kibbr].kd_aset3
                       AND [ta_kib_b].kd_aset4 = [ta_kibbr].kd_aset4
                       AND [ta_kib_b].kd_aset5 = [ta_kibbr].kd_aset5
                       AND [ta_kib_b].no_register = [ta_kibbr].no_register
                       AND kd_riwayat = 1
                       AND [ta_kibbr].tgl_dokumen <= '2018-12-31'
                ORDER  BY [ta_kibbr].tgl_dokumen DESC) AS newkondisi
        FROM   ta_kib_b) ta_kib_b