SQL server error

0

I'm doing select below and I can not execute:

SELECT DISTINCT U2.NM_ORG AS [Orgão Subordinado], 
    U1.NM_ORG AS [Unidade Gestora],
       E.NM_EPG  AS [Nome Funcionário(a)],
       C.NM_CG   AS [Cargo Funcionário(a)],
       D.CD_DOC_VIA,  
       R.SQ_ROT_VIA,         
       min(M1.NM_CID) AS [Cidade Partida],
       min(M2.NM_CID) AS [Cidade Chegada], 
       CONVERT(CHAR(10), MIN(D.DT_INI_VIA), 103) + ' A ' + CONVERT(CHAR(10), MIN(D.DT_FIM_VIA), 103) AS [Período Viagem],
       UPPER(T.NM_TP_VIA) AS [Motivo Viagem],
       CASE
          WHEN O.CD_ORI_CST = 3 THEN 'VEÍCULO PRÓPRIO'
          WHEN O.CD_ORI_CST = 4 THEN 'AVIÃO'         
          WHEN O.CD_ORI_CST = 7 THEN 'CARRO ALUGADO'
          WHEN O.CD_ORI_CST = 8 THEN 'ÔNIBUS'
          WHEN EXISTS(SELECT 1 FROM T056TAER T WHERE T.CD_DOC_VIA = D.CD_DOC_VIA)  THEN 'AVIÃO'
          ELSE 'NÃO IDENTIFICADO'          
       END AS [Meio Transporte],

        RTRIM(B.CD_TAR_AER) AS [Categoria Passagem],        

           (SELECT ISNULL(SUM(B1.VR_BIL_AER),0) FROM T056TAER A1, T056BIAG B1 
            WHERE A1.CD_DOC_VIA = D.CD_DOC_VIA                                
              AND A1.DT_PRV_PGT = B1.DT_PRV_PGT
              AND A1.SQ_BIL_AER = B1.SQ_BIL_AER) AS [Valor Passagem],

           (SELECT ISNULL(SUM(R2.QT_DIA_ROT), ISNULL(case when (SELECT DATEDIFF(d,MIN(D.DT_INI_VIA) , MIN(D.DT_FIM_VIA)) FROM T056DCVI D1 WHERE D1.CD_DOC_VIA = D.CD_DOC_VIA AND D1.TP_PRS_CTA = 2) = 0 then 0 else (SELECT DATEDIFF(d,MIN(D.DT_INI_VIA) , MIN(D.DT_FIM_VIA)) FROM T056DCVI D1 WHERE D1.CD_DOC_VIA = D.CD_DOC_VIA AND D1.TP_PRS_CTA = 2) +0.5  end,0)) FROM T056RTVI R2
            WHERE R2.CD_DOC_VIA = R.CD_DOC_VIA 
              AND O.CD_ORI_CST = 1 ) AS [Número Diárias],     

           (SELECT ISNULL(SUM(O1.VR_CST_ROT),0) FROM T056CROT O1
            WHERE O1.CD_DOC_VIA = O.CD_DOC_VIA 
            AND O1.CD_ORI_CST = 1 ) AS [Valor Total Diárias],

           ((SELECT ISNULL(SUM(O1.VR_CST_ROT),0) FROM T056CROT O1
             WHERE O1.CD_DOC_VIA = O.CD_DOC_VIA )+        
           ( SELECT ISNULL(SUM(B1.VR_BIL_AER),0) FROM T056TAER A1, T056BIAG B1 
             WHERE A1.CD_DOC_VIA = D.CD_DOC_VIA                                
               AND A1.DT_PRV_PGT = B1.DT_PRV_PGT
               AND A1.SQ_BIL_AER = B1.SQ_BIL_AER)) AS [Total Viagem]

    FROM         V849EMPT E
    inner join   V849CARG C on E.CD_CG_EPG   =  C.CD_CG
    inner join   T056DCVI D on E.MT_EPG      = D.MT_EPG
    inner join   T056RTVI R on D.CD_DOC_VIA  = R.CD_DOC_VIA
    inner join   T056CROT O on R.CD_DOC_VIA  = O.CD_DOC_VIA and R.SQ_ROT_VIA = O.SQ_ROT_VIA
    inner join   T056TPVI T on D.CD_TP_VIA   = T.CD_TP_VIA
    inner join   T056TAER A on D.CD_DOC_VIA  = A.CD_DOC_VIA
    inner join   T056BIAG B on A.DT_PRV_PGT  = B.DT_PRV_PGT and A.SQ_BIL_AER = B.SQ_BIL_AER
    inner join   T056TARI I on B.CD_CIA_AER  = I.CD_CIA_AER and B.CD_TAR_AER =  I.CD_TAR_AER
    inner join   V400UNOP U1 on D.CD_ORG_DEB = U1.CD_ORG
    inner join   V400UNOP U2 on D.CD_ORG_ALC = U2.CD_ORG
    inner join   V400MUNI M1 on R.CD_CID_PTD = M1.CD_CID
    inner join   V400MUNI M2 on R.CD_CID_CGD = M2.CD_CID
    WHERE  D.DT_INI_VIA BETWEEN  '2014-05-12' and  '2015-05-20' 
      AND  D.ST_DOC_VIA = 12
      AND  O.CD_ORI_CST NOT IN (13,14)
    GROUP BY U2.NM_ORG, U1.NM_ORG, E.NM_EPG, C.NM_CG, D.CD_DOC_VIA,          
              T.NM_TP_VIA, B.DT_PRV_PGT, B.SQ_BIL_AER, O.CD_ORI_CST, 
              R.SQ_ROT_VIA, R.QT_DIA_ROT, R.CD_DOC_VIA, R.CD_CID_PTD,
              R.CD_CID_CGD, O.CD_DOC_VIA, O.SQ_ROT_VIA, B.CD_TAR_AER, 
              I.DE_TAR_AER  

This error is appearing:

  

Can not specify outer join operators in a query containing joined   tables. View or function 'V400UNOP' contains outer join operators.

Someone can help me.

    
asked by anonymous 18.05.2016 / 15:57

1 answer

1

The error occurs because within V400UNOP there is Outer Join And SQL does not allow to chain Join with view with Outer Join

The snippet is in the where (script with SQL 2000 compatibility) clause

 and n.cd_und =* cd_und_dec  

I would also recommend reviewing this query as it has very low performance

    
08.06.2016 / 19:59