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.