Ola
Estou corrigindo esta view e precisei adicionar nas querys da view a função MAX para retornar a maior data de cada query da view, mas esta apresentando erro e não entendi este erro, o erro é:
ORA-01789: query block has incorrect number of result columns
01789. 00000 - “query block has incorrect number of result columns”
*Cause:
*Action:
Erro na linha: 119 Coluna: 3
SEGUE A QUERY DA VIEW:
WITH MOT AS
(SELECT NUM_MOTI, NOM_MOTI, DES_MOTI FROM TBMANI0004
)
SELECT LPAD (NUM_PROT, 10, '0') NUM_MANIF,
CAST (COD_ORIG_MANIF AS VARCHAR2 (10)) COD_ORIG_MANIF,
COD_SITU_MANI COD_SIT_MANIF,
COD_ORG_VENDA,
COD_CANAL_DISTR,
COD_SETOR_ATIV,
LPAD (COD_SAP_PES, 10, '0') COD_CLI_REV,
COD_CENTRO,
TO_CHAR (DAT_INCL_MANI, 'YYYYMMDD') DTA_GER_MANIF,
TO_CHAR (DAT_INCL_MANI, 'HH24MISS') HOR_GER_MANIF,
LPAD (NUM_PESS_ASSI_TECN, 10, '0') COD_ASS_TECN,
TO_CHAR (DAT_ATEN, 'YYYYMMDD') DTA_LIM_ACAO,
TO_CHAR (DAT_ATEN, 'HH24MISS') HOR_LIM_ACAO,
TO_CHAR (DAT_ENCE, 'YYYYMMDD') DTA_ENCER_MANIF,
TO_CHAR (DAT_ENCE, 'HH24MISS') HOR_ENCER_MANIF,
LPAD (NUM_SEGM, 5, '0') COD_SEGM,
LPAD (NUM_MOTI, 5, '0') COD_MOTIV,
LPAD (NUM_CANA, 5, '0') COD_CANAL_CAPT,
LPAD (NUM_ASNT, 5, '0') COD_ASSUN,
LPAD (NUM_ASNT_DETA, 5, '0') COD_DET_ASSUN,
LPAD (NUM_MANI_ORIG_CUST, 10, '0') NUM_MANIF_ORIG,
LPAD (NUM_PEDI_ORIG_CUST, 10, '0') NUM_PED_ORIG,
CAST (DES_BAIR AS VARCHAR2 (40)) NOM_BAI_MANIF,
CAST (DES_CIDA AS VARCHAR2 (40)) NOM_MUN_MANIF,
CAST (COD_ESTA AS VARCHAR2 (2)) SGL_UF_MANIF,
LPAD (NUM_PESS, 10, '0') COD_CONSUM,
LPAD (NUM_CLAS, 5, '0') COD_CLAS,
SUBSTR (TRIM (COD_DDDN_CONT_CUST), 1, 2) NUM_DDD_CONT,
SUBSTR (TRIM (COD_TELE_CONT_CUST), 1, 30) NUM_TEL_CONT,
SUBSTR (TRIM (REPLACE (NOM_CONT_CUST, CHR (9))), 1, 60) NOM_CONT,
RESPONS_LIQ,
SUTEC
FROM
(
--
--
SELECT MAN.NUM_PROT,
CASE
WHEN UPPER (ASN.NOM_ASNT) LIKE 'ASSIS%'
THEN 'SAC'
WHEN UPPER (ASN.NOM_ASNT) LIKE 'PEDIDO%'
THEN 'CCC'
WHEN UPPER (DET.NOM_ASNT_DETA) LIKE 'PRECO%'
OR UPPER (DET.NOM_ASNT_DETA) LIKE 'PREÇO%'
OR UPPER (DET.NOM_ASNT_DETA) LIKE '%ENTREGA'
OR UPPER (SUBSTR (DET.NOM_ASNT_DETA, - 3, 3)) = 'CAL'
THEN 'CCC'
ELSE 'SAC'
END COD_ORIG_MANIF,
MAN.COD_SITU_MANI,
'Z011' COD_ORG_VENDA,
'Z2' COD_CANAL_DISTR,
'90' COD_SETOR_ATIV,
REV.COD_R3RE_CUST COD_SAP_PES,
MAN.NUM_PESS,
MAN.DAT_INCL,
PES.NUM_CLAS,
NVL (NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA) NUM_CANA,
MAN.NUM_SEGM,
MAN.NUM_MOTI,
MAN.NUM_ASNT,
MAN.NUM_ASNT_DETA,
NULL COD_CENTRO,
MAN.DAT_INCL DAT_INCL_MANI,
MAN.NUM_PESS_ASSI_TECN_CUST NUM_PESS_ASSI_TECN,
MAX(W5.DAT_PRAZ_EXEC) DAT_ATEN,
MAN.DAT_ENCE_MANI DAT_ENCE,
MAN.NUM_PEDI_ORIG_CUST,
MAN.NUM_MANI_ORIG_CUST,
EDR.DES_BAIR,
EDR.DES_CIDA,
EDR.COD_ESTA,
MAN.COD_DDDN_CONT_CUST,
MAN.COD_TELE_CONT_CUST,
MAN.NOM_CONT_CUST,
NULL RESPONS_LIQ,
NULL SUTEC
FROM TBMANI0001 MAN
INNER JOIN TBPESS0001 PES
ON PES.NUM_PESS = MAN.NUM_PESS
INNER JOIN TBENDE0001 EDR
ON EDR.NUM_PESS = PES.NUM_PESS
AND EDR.NUM_ENDE = PES.NUM_ENDE
INNER JOIN TBMANI0005 ASN
ON ASN.NUM_ASNT = MAN.NUM_ASNT
INNER JOIN TBMANI0006 DET
ON DET.NUM_ASNT = MAN.NUM_ASNT
AND DET.NUM_ASNT_DETA = MAN.NUM_ASNT_DETA
INNER JOIN TBWORK0004 W4
ON MAN.NUM_PROT=W4.NUM_PROT
INNER JOIN TBWORK0005 W5
ON W4.NUM_FLUX=W5.NUM_FLUX
LEFT JOIN TBPESS0001 REV
ON REV.NUM_PESS = MAN.NUM_PESS_ASSI_TECN_CUST
WHERE PES.NUM_CLAS IN ( 1, 4 )
AND MAN.DAT_INCL >= TO_DATE ('01/01/2013', 'DD/MM/YYYY')
GROUP BY
MAN.NUM_PROT, ASN.NOM_ASNT, DET.NOM_ASNT_DETA, COD_SITU_MANI, REV.COD_R3RE_CUST,
MAN.NUM_PESS, MAN.DAT_INCL, PES.NUM_CLAS, MAN.NUM_CANA_ATEN_CUST, MAN.NUM_CANA,
MAN.NUM_SEGM, MAN.NUM_MOTI, MAN.NUM_ASNT, MAN.NUM_ASNT_DETA, MAN.DAT_INCL,
MAN.NUM_PESS_ASSI_TECN_CUST, MAN.DAT_ENCE_MANI, MAN.NUM_PEDI_ORIG_CUST, MAN.NUM_MANI_ORIG_CUST, EDR.DES_BAIR,
EDR.DES_CIDA, EDR.COD_ESTA, MAN.COD_DDDN_CONT_CUST, MAN.COD_TELE_CONT_CUST, MAN.NOM_CONT_CUST,
CASE WHEN UPPER (ASN.NOM_ASNT) LIKE 'ASSIS%' THEN 'SAC' WHEN UPPER (ASN.NOM_ASNT) LIKE 'PEDIDO%' THEN 'CCC' WHEN UPPER (DET.NOM_ASNT_DETA) LIKE 'PRECO%' OR UPPER (DET.NOM_ASNT_DETA) LIKE 'PREÇO%' OR UPPER (DET.NOM_ASNT_DETA) LIKE '%ENTREGA' OR UPPER (SUBSTR (DET.NOM_ASNT_DETA, - 3, 3)) = 'CAL' THEN 'CCC' ELSE 'SAC' END, UPPER (ASN.NOM_ASNT), ASN.NOM_ASNT, 'ASSIS%', 'SAC',
UPPER (ASN.NOM_ASNT), ASN.NOM_ASNT, 'PEDIDO%', 'CCC', UPPER (DET.NOM_ASNT_DETA),
DET.NOM_ASNT_DETA, 'PRECO%', UPPER (DET.NOM_ASNT_DETA), DET.NOM_ASNT_DETA, 'PREÇO%',
UPPER (DET.NOM_ASNT_DETA), DET.NOM_ASNT_DETA, '%ENTREGA', UPPER (SUBSTR (DET.NOM_ASNT_DETA, - 3, 3)), SUBSTR (DET.NOM_ASNT_DETA, - 3, 3),
DET.NOM_ASNT_DETA, - 3, 3, 3, 'CAL',
'CCC', 'SAC', MAN.COD_SITU_MANI, 'Z011', 'Z2',
'90', NVL (NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA), NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA_ATEN_CUST, 0,
MAN.NUM_CANA, NULL
UNION ALL
--
SELECT MAN.NUM_PROT,
CASE
WHEN UPPER (ASN.NOM_ASNT) LIKE 'ASSIS%'
THEN 'SAC'
WHEN UPPER (ASN.NOM_ASNT) LIKE 'GEST_O CAL ATIV%'
OR UPPER (MOT.NOM_MOTI) LIKE 'GEST_O CAL ATIV%'
THEN 'CAL-ATV'
ELSE 'MI'
END COD_ORIG_MANIF,
MAN.COD_SITU_MANI,
'Z012' COD_ORG_VENDA,
'Z1' COD_CANAL_DISTR,
'91' COD_SETOR_ATIV,
PES.COD_R3RE_CUST COD_SAP_PES,
NULL NUM_PESS,
MAN.DAT_INCL,
PES.NUM_CLAS,
NVL (NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA) NUM_CANA,
MAN.NUM_SEGM,
MAN.NUM_MOTI,
MAN.NUM_ASNT,
MAN.NUM_ASNT_DETA,
NULL COD_CENTRO,
MAN.DAT_INCL DAT_INCL_MANI,
MAN.NUM_PESS_ASSI_TECN_CUST NUM_PESS_ASSI_TECN,
MAX(W5.DAT_PRAZ_EXEC) DAT_ATEN,
MAN.DAT_ATEN_ASSI_TECN_CUST DAT_ATEN,
MAN.DAT_ENCE_MANI DAT_ENCE,
MAN.NUM_PEDI_ORIG_CUST,
MAN.NUM_MANI_ORIG_CUST,
EDR.DES_BAIR,
EDR.DES_CIDA,
EDR.COD_ESTA,
MAN.COD_DDDN_CONT_CUST,
MAN.COD_TELE_CONT_CUST,
MAN.NOM_CONT_CUST,
NULL RESPONS_LIQ,
NULL SUTEC
FROM TBMANI0001 MAN
INNER JOIN TBPESS0001 PES
ON PES.NUM_PESS = MAN.NUM_PESS
INNER JOIN TBENDE0001 EDR
ON EDR.NUM_PESS = PES.NUM_PESS
AND EDR.NUM_ENDE = PES.NUM_ENDE
INNER JOIN MOT
ON MOT.NUM_MOTI = MAN.NUM_MOTI
INNER JOIN TBMANI0005 ASN
ON ASN.NUM_ASNT = MAN.NUM_ASNT
INNER JOIN TBWORK0004 W4
ON MAN.NUM_PROT=W4.NUM_PROT
INNER JOIN TBWORK0005 W5
ON W4.NUM_FLUX=W5.NUM_FLUX
WHERE PES.NUM_CLAS = 26
AND MAN.DAT_INCL >= TO_DATE ('01/01/2013', 'DD/MM/YYYY')
AND MAN.NUM_PROT=1167413
GROUP BY
MAN.NUM_PROT, CASE WHEN UPPER (ASN.NOM_ASNT) LIKE 'ASSIS%' THEN 'SAC' WHEN UPPER (ASN.NOM_ASNT) LIKE 'GEST_O CAL ATIV%' OR UPPER (MOT.NOM_MOTI) LIKE 'GEST_O CAL ATIV%' THEN 'CAL-ATV' ELSE 'MI' END, MAN.COD_SITU_MANI, 'Z012', 'Z1',
'91', PES.COD_R3RE_CUST, NULL, MAN.DAT_INCL, PES.NUM_CLAS,
NVL (NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA), MAN.NUM_SEGM, MAN.NUM_MOTI, MAN.NUM_ASNT, MAN.NUM_ASNT_DETA,
NULL, MAN.DAT_INCL, MAN.NUM_PESS_ASSI_TECN_CUST, MAN.DAT_ATEN_ASSI_TECN_CUST, MAN.DAT_ENCE_MANI,
MAN.NUM_PEDI_ORIG_CUST, MAN.NUM_MANI_ORIG_CUST, EDR.DES_BAIR, EDR.DES_CIDA, EDR.COD_ESTA,
MAN.COD_DDDN_CONT_CUST, MAN.COD_TELE_CONT_CUST, MAN.NOM_CONT_CUST, NULL, NULL
--
UNION ALL
--
SELECT MAN.NUM_PROT,
CASE
WHEN UPPER (ASN.NOM_ASNT) LIKE 'ASSIS%'
THEN 'SAC'
ELSE 'CAL'
END COD_ORIG_MANIF,
MAN.COD_SITU_MANI,
'Z011' COD_ORG_VENDA,
'Z2' COD_CANAL_DISTR,
'90' COD_SETOR_ATIV,
PES.COD_R3RE_CUST COD_SAP_PES,
NULL NUM_PESS,
MAN.DAT_INCL,
PES.NUM_CLAS,
NVL (NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA) NUM_CANA,
MAN.NUM_SEGM,
MAN.NUM_MOTI,
MAN.NUM_ASNT,
MAN.NUM_ASNT_DETA,
NVL (PED.COD_CENT_FORN_CUST, PES.COD_CENT_FORN_CUST) COD_CENTRO,
MAN.DAT_INCL DAT_INCL_MANI,
MAN.NUM_PESS_ASSI_TECN_CUST NUM_PESS_ASSI_TECN,
MAX(W5.DAT_PRAZ_EXEC) DAT_ATEN,
MAN.DAT_ENCE_MANI DAT_ENCE,
MAN.NUM_PEDI_ORIG_CUST,
MAN.NUM_MANI_ORIG_CUST,
EDR.DES_BAIR,
EDR.DES_CIDA,
EDR.COD_ESTA,
MAN.COD_DDDN_CONT_CUST,
MAN.COD_TELE_CONT_CUST,
Man.Nom_Cont_Cust,
NULL RESPONS_LIQ,
NULL SUTEC
FROM TBMANI0001 MAN
INNER JOIN TBPESS0001 PES
ON PES.NUM_PESS = MAN.NUM_PESS
INNER JOIN TBENDE0001 EDR
ON EDR.NUM_PESS = PES.NUM_PESS
AND EDR.NUM_ENDE = PES.NUM_ENDE
INNER JOIN TBMANI0005 ASN
ON ASN.NUM_ASNT = MAN.NUM_ASNT
LEFT JOIN TBPEDI0001 PED
ON PED.NUM_PEDI = MAN.NUM_PEDI_ORIG_CUST
INNER JOIN TBWORK0004 W4
ON MAN.NUM_PROT=W4.NUM_PROT
INNER JOIN TBWORK0005 W5
ON W4.NUM_FLUX=W5.NUM_FLUX
WHERE PES.NUM_CLAS = 3
AND MAN.DAT_INCL >= TO_DATE ('01/01/2013', 'DD/MM/YYYY')
GROUP BY MAN.NUM_PROT, ASN.NOM_ASNT,MAN.COD_SITU_MANI, PES.COD_R3RE_CUST,
MAN.NUM_PESS, MAN.DAT_INCL, PES.NUM_CLAS, MAN.NUM_CANA_ATEN_CUST, MAN.NUM_CANA,
MAN.NUM_SEGM, MAN.NUM_MOTI, MAN.NUM_ASNT, MAN.NUM_ASNT_DETA, PED.COD_CENT_FORN_CUST,
MAN.DAT_INCL, MAN.NUM_PESS_ASSI_TECN_CUST, MAN.DAT_ENCE_MANI, MAN.NUM_PEDI_ORIG_CUST, MAN.NUM_MANI_ORIG_CUST,
EDR.DES_BAIR, EDR.DES_CIDA, EDR.COD_ESTA, MAN.COD_DDDN_CONT_CUST, MAN.COD_TELE_CONT_CUST,
Man.Nom_Cont_Cust, CASE WHEN UPPER (ASN.NOM_ASNT) LIKE 'ASSIS%' THEN 'SAC' ELSE 'CAL' END, 'Z011', 'Z2', '90',
NULL, NVL (NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA), NVL (PED.COD_CENT_FORN_CUST, PES.COD_CENT_FORN_CUST)
--
UNION ALL
--
SELECT MAN.NUM_PROT,
CASE
WHEN UPPER (ASN.NOM_ASNT) LIKE 'ASSIS%'
OR UPPER (SUBSTR (ASN.NOM_ASNT, - 5, 5)) = '(SAC)'
OR UPPER (ASN.NOM_ASNT) LIKE '%CNICA'
OR UPPER (ASN.NOM_ASNT) LIKE 'PRODUTO%'
THEN 'SAC'
WHEN UPPER (ASN.NOM_ASNT) LIKE 'GEST_O CAL ATIV%'
OR UPPER (MOT.NOM_MOTI) LIKE 'GEST_O CAL ATIV%'
THEN 'CAL-ATV'
ELSE 'CAL'
END COD_ORIG_MANIF,
MAN.COD_SITU_MANI,
'Z012' COD_ORG_VENDA,
'Z1' COD_CANAL_DISTR,
CASE
WHEN PES.NUM_CLAS = 27
OR MAN.NUM_SEGM = 22
THEN '90'
ELSE '91'
END COD_SETOR_ATIV,
PES.COD_R3RE_CUST COD_SAP_PES,
NULL NUM_PESS,
MAN.DAT_INCL,
PES.NUM_CLAS,
NVL (NULLIF (MAN.NUM_CANA_ATEN_CUST, 0), MAN.NUM_CANA) NUM_CANA,
MAN.NUM_SEGM,
MAN.NUM_MOTI,
MAN.NUM_ASNT,
MAN.NUM_ASNT_DETA,
NVL (PED.COD_CENT_FORN_CUST, PES.COD_CENT_FORN_CUST) COD_CENTRO,
MAN.DAT_INCL DAT_INCL_MANI,
MAN.NUM_PESS_ASSI_TECN_CUST NUM_PESS_ASSI_TECN,
PAS.DAT_PRAZ_EXEC DAT_ATEN,
MAN.DAT_ENCE_MANI DAT_ENCE,
MAN.NUM_PEDI_ORIG_CUST,
MAN.NUM_MANI_ORIG_CUST,
EDR.DES_BAIR,
EDR.DES_CIDA,
EDR.COD_ESTA,
MAN.COD_DDDN_CONT_CUST,
Man.Cod_Tele_Cont_Cust,
Man.Nom_Cont_Cust,
CASE
WHEN Pas.Ind_Resp_Liqu_Cust IN ('S','N')
THEN Pas.Ind_Resp_Liqu_Cust
ELSE NULL
END RESPONS_LIQ,
CASE
WHEN asn.ind_assi_tecn_cust = 'S'
THEN Pas.Sutec
ELSE NULL
END Sutec
FROM TBMANI0001 MAN
INNER JOIN TBPESS0001 PES
ON PES.NUM_PESS = MAN.NUM_PESS
INNER JOIN TBENDE0001 EDR
ON EDR.NUM_PESS = PES.NUM_PESS
AND EDR.NUM_ENDE = PES.NUM_ENDE
INNER JOIN MOT
ON MOT.NUM_MOTI = MAN.NUM_MOTI
INNER JOIN TBMANI0005 ASN
ON ASN.NUM_ASNT = MAN.NUM_ASNT
LEFT JOIN TBPEDI0001 PED
ON PED.NUM_PEDI = MAN.NUM_PEDI_ORIG_CUST
LEFT JOIN TBWORK0004 PRO
ON PRO.NUM_PROT = MAN.NUM_PROT
LEFT JOIN
(SELECT NUM_FLUX,
Num_Pass,
Ind_Resp_Liqu_Cust,
MAX(num_seqn_flux_pass) SUTEC,
MAX (DAT_PRAZ_EXEC) DAT_PRAZ_EXEC
FROM TBWORK0005
WHERE DAT_PRAZ_EXEC IS NOT NULL
AND DAT_INCL >= TO_DATE ('01/01/2013', 'DD/MM/YYYY')
GROUP BY Num_Flux,
Num_Pass,
Ind_Resp_Liqu_Cust
) Pas
ON Pas.Num_Flux = Pro.Num_Flux
AND Pas.Num_Pass = Pro.Num_Pass_Atua
WHERE Pes.Num_Clas IN ( 2, 27 )
AND Man.Dat_Incl >= To_Date ('01/01/2013', 'DD/MM/YYYY')
);