Ajuda com uma query que esta retornando erro abaixo

Question

Pessoal, alguém pode me ajudar com o erro na query abaixo?

with temp as (
select distinct 
	convert(int, t.CODENUMBER) as id_divisao,
	t.EMPRESA,
	f.id_filial,
	left(ltrim(rtrim(replace(t.cod_emissor,char(10),''))),3) cod_emissor,
	left(t.nome_emissor,50) as nome_emissor,
	CONVERT(varchar(6), 
		case when rtrim(ltrim(dt_faturamento)) = ''	
		then convert(datetime, dt_emissao, 103)
		else convert(datetime, dt_faturamento, 103)
	end,112) as id_mes,
	case when convert(int, t.CODENUMBER) = 9999 
		then Replace(RTRIM(LTRIM(t.CODENUMBER)), CHAR(10), '') + Convert(varchar(10), Convert(Int, Replace(RTRIM(LTRIM(t.[EMPRESA])), CHAR(10), '')))
		else -1
		end as id_empresa,		
	cod_empresa_contabil = case t.CODENUMBER when '1000' 
							then (select min(cod_empresa_contabil )
										from stg_representante_contabil 
										where cod_representante_contabil = Replace(RTRIM(LTRIM(t.CODIGO_REPRESENTANTE)), CHAR(10), '')
										  and cod_divisao = '1000')
							else (select min(cod_empresa_contabil )
										from stg_representante_contabil 
										where cod_representante_contabil = Replace(RTRIM(LTRIM(t.[empresa])), CHAR(10), '')
										  and cod_divisao = t.CODENUMBER)
							end
from 
TB_TERRESTRE_IMPORT t
left join
dim_filial		f
on  f.sgl_filial = Replace(RTrim(LTrim(t.filial)), CHAR(10), '')
and f.id_divisao = t.CODENUMBER

)

--insert into tab_meta_produtividade (sgl_emissor,id_divisao,id_mes,vl_produtividade,id_emissor)
select DISTINCT
	temp.cod_emissor,
	temp.id_divisao,
	temp.id_mes,
	0,
	em.id_emissor
	from 
	temp
	-- Le da Stg_Filial_Franquia somente se a divisao for 9999
	left join
	stg_filial_franquia	ff
	on	ff.id_filial = temp.id_filial
 	and ff.id_divisao = 9999
 	and ff.id_empresa = temp.id_empresa
	left join
	dim_empresa_contabil ec
	on	ec.cod_empresa_contabil = temp.cod_empresa_contabil
	and ec.id_divisao = temp.id_divisao
	left join 
	dim_emissor em
	on em.id_empresa = temp.id_empresa and em.id_divisao = temp.id_divisao and em.sgl_emissor = temp.cod_emissor 
and em.id_empresa_contabil = case when temp.id_divisao = 9999
		then isnull(ff.id_empresa_contabil, ec.id_empresa_contabil)
		else -1
		end 
	 
	where not exists (select 1 from tab_meta_produtividade m where m.id_divisao = temp.id_divisao
and m.sgl_emissor = temp.cod_emissor
and m.id_mes = temp.id_mes)

Esta apresentando este erro:

Information: 0x40043009 at Insere Emissor, SSIS.Pipeline: Cleanup phase is beginning.
Error: 0x0 at Atualiza Tab_Meta_Colaborador: Violation of PRIMARY KEY constraint 'XPKtab_meta_produtividade'. Cannot insert duplicate key in object 'dbo.tab_meta_produtividade'.
Error: 0xC002F210 at Atualiza Tab_Meta_Colaborador, Execute SQL Task: Executing the query "with temp as (
select distinct 
	convert(int, t.CO..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Atualiza Tab_Meta_Colaborador
Warning: 0x80019002 at Carga_Terrestre: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "carga_terrestre.dtsx" finished: Failure.
The program '[27904] carga_terrestre.dtsx: DTS' has exited with code 0 (0x0).

Acredito que o problema esteja nesse case, mas não consigo resolver.

	on em.id_empresa = temp.id_empresa and em.id_divisao = temp.id_divisao and em.sgl_emissor = temp.cod_emissor 

and em.id_empresa_contabil = case when temp.id_divisao = 9999
		then isnull(ff.id_empresa_contabil, ec.id_empresa_contabil)
		else -1
		end 

Alguém pode me ajudar, por favor?

---

---

Atenciosamente;
Cleber A. dos Santos
Analista Programador Pleno - BI
E-mail: [e-mail]
Linkedin: cleberasantos
Skype: cbr.santos

---

Answer 1

Cleber,

Bom dia!

Qual a chave da tabela tab_meta_produtividade?

Answer 2

Information: 0x40043009 at Insere Emissor, SSIS.Pipeline: Cleanup phase is beginning.
Error: 0x0 at Atualiza Tab_Meta_Colaborador: Violation of PRIMARY KEY constraint 'XPKtab_meta_produtividade'. Cannot insert duplicate key in object 'dbo.tab_meta_produtividade'.
Error: 0xC002F210 at Atualiza Tab_Meta_Colaborador, Execute SQL Task: Executing the query "with temp as (
select distinct 
	convert(int, t.CO..." failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Atualiza Tab_Meta_Colaborador
Warning: 0x80019002 at Carga_Terrestre: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "carga_terrestre.dtsx" finished: Failure.
The program '[27904] carga_terrestre.dtsx: DTS' has exited with code 0 (0x0).

1) O primeiro erro e ocasionado pelo comando INSERT INTO

qual das colunas  da CTE vem de um campo PK ?? 

       temp.cod_emissor ,
        temp.id_divisao ,
        temp.id_mes ,
        em.id_emissor

executa esse comando encima do seu campo Primary key para saber os campos repetidos.

SELECT  temp.CampoPK ,quantidade = COUNT(CampoPK) 
			       
			 FROM temp
			GROUP BY CampoPK 
			HAVING COUNT(CampoPK) >1

2) posta a definição do campo CODENUMBER e verifica se esse campo pode ser convertido sem erros para INT

talvez esse campo de erro na conversão 

SELECT CONVERT(INT, t.CODENUMBER) AS CODENUMBER
FROM TB_TERRESTRE_IMPORT AS T

tente usar isso 

SELECT TRY_CONVERT(INT, t.CODENUMBER) AS CODENUMBER
FROM TB_TERRESTRE_IMPORT AS T

---

Wesley Neves

Answer 3

Obrigado amigos, era o campo que estava duplicado mesmo.

Consegui resolver.

---

---

Atenciosamente;
Cleber A. dos Santos
Analista Programador Pleno - BI
E-mail: [e-mail]
Linkedin: cleberasantos
Skype: cbr.santos

---