Problem in TSQL Query while conveting from PLSQL_问答_开发者

i am new at sql server 2008 and tsql.I am migrating my queries from oracle to sql server.I came across that problem in one query while migrating.I dont know how to solve and what causes that error?

The error is Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

Thanks for your help.

Complete query is

SELECT MAIN.ID_MAMUL, 
       MAIN.ID_BIRIM, 
       MAIN.MAMUL_NO, 
       MAIN.DS_MAMUL, 
       MAIN.DS_BIRIM, 
       SAYIM.MT_SAYIM, 
       SAYIM.DS_TARIH, 
       (SELECT Isnull (SUM (CASE 
                              WHEN TRSH.GIRIS_TARIHI > Isnull (SAYIM.DS_TARIH, 
                                                       DBO.To_date ('01.01.2006' 
                                                       , 
                                                       'dd.mm.yyyy')) THEN 
                              dbo.F_BIRIM_CEVRIM_KATSAYI (LUM.ID_MAMUL, 
                              CRMB.ID_BIRIM, 
                              LUM.ID_BIRIM) * 
                              TRSD.MT_MIKTAR 
                              ELSE 0 
                            END), 0) 
        FROM   TR_STOK_HEADER TRSH, 
               TR_STOK_DETAY TRSD, 
               CR_MAMUL_BARKOD CRMB, 
               LU_MAMUL LUM 
        WHERE  TRSH.ID_STOK_HEADER = TRSD.ID_STOK_HEADER 
               AND TRSD.ID_MAMUL_BARKOD = CRMB.ID_MAMUL_BARKOD 
               AND CRMB.ID_MAMUL = LUM.ID_MAMUL 
               AND LUM.ID_MAMUL = MAIN.ID_MAMUL 
               AND TRSH.ID_DEPO = 327 
               AND TRSH.ID_STOK_HAREKET_TIPI IN ( 6, 15 ) 
               AND TRSH.AKTIF = 1 
               AND TRSD.VALID = 1)           MT_ALIS, 
       (SELECT Isnull (SUM (CASE 
                              WHEN TRSH.GIRIS_TARIHI > Isnull (SAYIM.DS_TARIH, 
                                                       DBO.To_date ('01.01.2006' 
                                                       , 
                                                       'dd.mm.yyyy')) THEN 
                              DBO.F_BIRIM_CEVRIM_KATSAYI (LUM.ID_MAMUL, 
                              CRMB.ID_BIRIM, 
                              LUM.ID_BIRIM) * 
                              TRSD.MT_MIKTAR 
                              ELSE 0 
                            END), 0) 
        FROM   TR_STOK_HEADER TRSH, 
               TR_STOK_DETAY TRSD, 
               CR_MAMUL_BARKOD CRMB, 
               LU_MAMUL LUM 
        WHERE  TRSH.ID_STOK_HEADER = TRSD.ID_STOK_HEADER 
               AND TRSD.ID_MAMUL_BARKOD = CRMB.ID_MAMUL_BARKOD 
               AND CRMB.ID_MAMUL = LUM.ID_MAMUL 
               AND LUM.ID_MAMUL = MAIN.ID_MAMUL 
               AND TRSH.ID_DEPO = 327 
               AND TRSH.ID_STOK_HAREKET_TIPI = 8 
               AND TRSH.AKTIF = 1 
               AND TRSD.VALID = 1 
               AND TRSD.FL_DURUM = 0 
               AND TRSD.FL_BIREBIR_IADE = 0) MT_NOKTA_IADE, 
       (SELECT Isnull (SUM (CASE 
                              WHEN TRSH.GIRIS_TARIHI > Isnull (SAYIM.DS_TARIH, 
                                                       DBO.To_date ('01.01.2006' 
                                                       , 
                                                       'dd.mm.yyyy')) THEN 
                            DBO.F_BIRIM_CEVRIM_KATSAYI (LUM.ID_MAMUL, 
                            CRMB.ID_BIRIM, 
                            LUM.ID_BIRIM) * 
                       Abs 
                       ( 
                       TRSD.MT_MIKTAR) 
                         ELSE 0 
                       END), 0) 
        FROM   TR_STOK_HEADER TRSH, 
               TR_STOK_DETAY TRSD, 
               CR_MAMUL_BARKOD CRMB, 
               LU_MAMUL LUM 
        WHERE  TRSH.ID_STOK_HEADER = TRSD.ID_STOK_HEADER 
               AND TRSD.ID_MAMUL_BARKOD = CRMB.ID_MAMUL_BARKOD 
               AND CRMB.ID_MAMUL = LUM.ID_MAMUL 
               AND LUM.ID_MAMUL = MAIN.ID_MAMUL 
               AND TRSH.ID_DEPO = 327 
               AND TRSH.ID_STOK_HAREKET_TIPI = 9 
               AND TRSH.AKTIF = 1 
               AND TRSD.VALID = 1 
               AND TRSD.FL_DURUM = 0 
               AND TRSD.FL_BIREBIR_IADE = 0) MT_PLASIYER_IADE, 
       (SELECT Isnull (SUM (CASE 
                              WHEN TRSH.GIRIS_TARIHI > Isnull (SAYIM.DS_TARIH, 
                                                       DBO.To_date ('01.01.2006' 
                                                       , 
                                                       'dd.mm.yyyy')) THEN 
                            DBO.F_BIRIM_CEVRIM_KATSAYI (LUM.ID_MAMUL, 
                            CRMB.ID_BIRIM, 
                            LUM.ID_BIRIM) * 
                       Abs 
                       ( 
                       TRSD.MT_MIKTAR) 
                         ELSE 0 
                       END), 0) 
        FROM   TR_STOK_HEADER TRSH, 
               TR_STOK_DETAY TRSD, 
               CR_MAMUL_BARKOD CRMB, 
               LU_MAMUL LUM 
        WHERE  TRSH.ID_STOK_HEADER = TRSD.ID_STOK_HEADER 
               AND TRSD.ID_MAMUL_BARKOD = CRMB.ID_MAMUL_BARKOD 
               AND CRMB.ID_MAMUL = LUM.ID_MAMUL 
               AND LUM.ID_MAMUL = MAIN.ID_MAMUL 
               AND TRSH.ID_DEPO = 327 
               AND TRSH.ID_STOK_HAREKET_TIPI = 8 
               AND TRSH.AKTIF = 1 
               AND TRSD.VALID = 1 
               AND TRSD.FL_DURUM = 0 
               AND TRSD.FL_BIREBIR_IADE = 1) MT_NOKTA_BIREBIR_IADE, 
       (SELECT Isnull (SUM (CASE 
                              WHEN TRSH.GIRIS_TARIHI > Isnull (SAYIM.DS_TARIH, 
                                                       DBO.To_date ('01.01.2006' 
                                                       , 
                                                       'dd.mm.yyyy')) THEN 
                            DBO.F_BIRIM_CEVRIM_KATSAYI (LUM.ID_MAMUL, 
                            CRMB.ID_BIRIM, 
                            LUM.ID_BIRIM) * 
                       Abs 
                       ( 
                       TRSD.MT_MIKTAR) 
                         ELSE 0 
                       END), 0) 
        FROM   TR_STOK_HEADER TRSH, 
               TR_STOK_DETAY TRSD, 
               CR_MAMUL_BARKOD CRMB, 
               LU_MAMUL LUM 
        WHERE  TRSH.ID_STOK_HEADER = TRSD.ID_STOK_HEADER 
               AND TRSD.ID_MAMUL_BARKOD = CRMB.ID_MAMUL_BARKOD 
               AND CRMB.ID_MAMUL = LUM.ID_MAMUL 
               AND LUM.ID_MAMUL = MAIN.ID_MAMUL 
               AND TRSH.ID_DEPO = 327 
               AND TRSH.ID_STOK_HAREKET_TIPI = 5 
               AND TRSH.AKTIF = 1 
               AND TRSD.VALID = 1 
               AND TRSD.FL_DURUM = 0 
               AND TRSD.FL_BIREBIR_IADE = 0) MT_SATIS, 
       MAIN.MT_STOK 
FROM   (SELECT MA.ID_MAMUL, 
               MA.MAMUL_NO, 
               MA.DS_MAMUL, 
               BR.ID_BIRIM, 
               BR.DS_BIRIM_KISA DS_BIRIM, 
               ( DBO.F_BIRIM_CEVRIM_KATSAYI (MA.ID_MAMUL, MB.ID_BIRIM, 
                 MA.ID_BIRIM) * 
                 DS.MT_MIKTAR ) MT_STOK 
        FROM   CR_DEPO_STOK DS, 
               CR_MAMUL_BARKOD MB, 
               LU_MAMUL MA, 
               LU_BIRIM BR 
        WHERE  DS.ID_MAMUL_BARKOD = MB.ID_MAMUL_BARKOD 
               AND MB.ID_MAMUL = MA.ID_MAMUL 
               AND MA.ID_BIRIM = BR.ID_BIRIM 
               AND DS.ID_DEPO = 327 
               AND DS.FL_DURUM = 0) MAIN 
       LEFT JOIN (SELECT MA.ID_MAMUL, 
                         SS.DS_TARIH, 
                         SUM (DBO.F_BIRIM_CEVRIM_KATSAYI (MA.ID_MAMUL, 
                              MB.ID_BIRIM, 
                              MA.ID_BIRIM) 
                              * 
                              SD.MT_SAYIM) MT_SAYIM 
                  FROM   (SELECT S.ID_DEPO, 
                                 B.ID_MAMUL, 
                                 MAX (S.DS_TARIH) DS_TARIH 
                          FROM   TR_STOK_SAYIM S, 
                                 TR_STOK_SAYIM_DETAY D, 
                                 CR_MAMUL_BARKOD B 
  开发者_运维知识库                        WHERE  S.ID_STOK_SAYIM = D.ID_STOK_SAYIM 
                                 AND D.ID_MAMUL_BARKOD = B.ID_MAMUL_BARKOD 
                                 AND D.FL_ONAY = 1 
                                 AND S.ID_DEPO = 327 
                          GROUP  BY S.ID_DEPO, 
                                    B.ID_MAMUL) SON_SAY, 
                         TR_STOK_SAYIM SS, 
                         TR_STOK_SAYIM_DETAY SD, 
                         CR_MAMUL_BARKOD MB, 
                         LU_MAMUL MA 
                  WHERE  SS.ID_STOK_SAYIM = SD.ID_STOK_SAYIM 
                         AND SD.ID_MAMUL_BARKOD = MB.ID_MAMUL_BARKOD 
                         AND MB.ID_MAMUL = MA.ID_MAMUL 
                         AND SS.ID_DEPO = 327 
                         AND SS.ID_DEPO = SON_SAY.ID_DEPO 
                         AND SS.DS_TARIH = SON_SAY.DS_TARIH 
                         AND MA.ID_MAMUL = SON_SAY.ID_MAMUL 
                         AND SD.FL_ONAY = 1 
                  GROUP  BY MA.ID_MAMUL, 
                            SS.DS_TARIH) SAYIM 
         ON MAIN.ID_MAMUL = SAYIM.ID_MAMUL 
ORDER  BY 3, 
          2

I would never write such a long query as it is to much for a human being to parse as pointed out by @Andomar. If you go through the different "sections" of the joins one by one you will probably spot the error.