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  

Answer 2

Here is my updated code for future reference, thanks for help @Pரதீப்

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
                (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,

            (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 KondisiSebelumTA2018,

 (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 TanggalPerubahanKondisiTerakhir,

 (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 LastUpdate                              

    FROM   ta_kib_b) as [ta_kib_b]