All Forums Database
superjuanito88 17 posts Joined 05/12
06 Sep 2012
2616: Numeric overflow occurred During computation

Running the "Insert - Select" I get the following error message "2616: Numeric overflow occurred During computation.", But if you just run the "Select" does independent normal, the error occurs when I run next to "insert".

Thanks in advance for the help.

 

INSERT INTO EDW_ODS.AGENDA_CITA (  
	  FECHA_HORA_CITA 
	 ,CLIENTE_PROFESIONAL_ID 
	 ,CODIGO_CLIENTE_PROFESIONAL_OP
	 ,CODIGO_TIPO_IDENT_PRO_OP
	 ,SERVICIO_CITA_ID 
	 ,NUMERO_CONS_HORA_CORRELATIVO 
	 ,CLIENTE_IPS_ID 
         ,CODIGO_CLIENTE_IPS_OP
	 ,CODIGO_TIPO_IDENT_IPS_OP
	 ,CLIENTE_PACIENTE_ID 
	 ,CODIGO_CLIENTE_PACIENTE_OP
	 ,CODIGO_TIPO_IDENT_PAC_OP
	 ,PLAN_CITA_ID 
	 ,PLAN_AGRUPADOR_CITA_ID 
	 ,NUMERO_INTERVALO_CREACIONCITA 
	 ,OBSERVACION_CITA_TXT 
	 ,ESTADO_CITA_ID 
	 ,USUARIO_RED_TXT 
	 ,CAUSA_OPORTUNIDAD_ID 
	 ,NUMERO_DIAS_PROMESA_SERVICIO 
	 ,FECHA_SOLICITUD_CITA 
	 ,TIPO_PROMESA_ID 
	 ,IND_ATENDIDA_IPS 
	 ,NUMERO_CONS_ACT_EDUCATIVA 
	 ,CAUSA_MODIF_POSTERIOR_TXT 
	 ,NUMERO_IDENTIF_CARGA_CITA 
	 ,ESPECIALIDAD_CITA_ID
	 ,NUMERO_DIAS_OPORTUNIDAD 
	 ,IND_TIPO_FAM_PROFESIONAL
	 ,NUMERO_EXPEDIENTE_ARP
	 ,NUMERO_AUTORIZACION_ARP 
	 ,EDW_AUDIT_TRAIL_USER_NAME 
	 ,EDW_AUDIT_TRAIL_DTTM
	 ,EDW_AUDIT_TRAIL_PROCESS_CD
	 )  
	 SELECT     
		  CAST(FECHA_HORA_CITA AS TIMESTAMP(0)) FECHA_HORA_CITA
		 ,CAST(B6.CLIENTE_PROFESIONAL_ID  AS INTEGER) CLIENTE_PROFESIONAL_ID
		 ,TEMP.CODIGO_CLIENTE_PROFESIONAL_OP
		 ,TEMP.CODIGO_TIPO_IDENT_PRO_OP
		 ,CAST(C8.SERVICIO_CITA_ID  AS INTEGER) SERVICIO_CITA_ID
		 ,NUMERO_CONS_HORA_CORRELATIVO 
		 ,CAST(D4.CLIENTE_IPS_ID  AS INTEGER) CLIENTE_IPS_ID
		 ,TEMP.CODIGO_CLIENTE_IPS_OP
		 ,'CSIPSAGE'
		 ,CAST(COALESCE(E9.CLIENTE_PACIENTE_ID,-2) AS INTEGER) CLIENTE_PACIENTE_ID 
		 ,TEMP.CODIGO_CLIENTE_PACIENTE_OP
		 ,TEMP.CODIGO_TIPO_IDENT_PAC_OP 
		 ,CAST(
		 	(CASE
		 		WHEN TEMP.CODIGO_PLAN_CITA_OP = '0' THEN -2
		 		ELSE COALESCE(P.PLAN_CITA_ID, -1)
		 	END) AS INTEGER) PLAN_CITA_ID
		 ,CAST(COALESCE(G9.PLAN_AGRUPADOR_CITA_ID,-2) AS INTEGER) PLAN_AGRUPADOR_CITA_ID
		 ,NUMERO_INTERVALO_CREACIONCITA 
		 ,OBSERVACION_CITA_TXT 
		 ,CAST(COALESCE(E.ESTADO_CITA_ID,-2)    AS INTEGER) ESTADO_CITA_ID
		 ,USUARIO_RED_TXT 
		 ,CAST(I1.CAUSA_OPORTUNIDAD_ID AS INTEGER) CAUSA_OPORTUNIDAD_ID
		 ,NUMERO_DIAS_PROMESA_SERVICIO 
		 ,CAST(FECHA_SOLICITUD_CITA  AS TIMESTAMP(0)) FECHA_SOLICITUD_CITA
		 ,CAST(COALESCE(T.TIPO_PROMESA_ID,-2)  AS INTEGER) TIPO_PROMESA_ID
		 ,IND_ATENDIDA_IPS 
		 ,NUMERO_CONS_ACT_EDUCATIVA 
		 ,CAUSA_MODIF_POSTERIOR_TXT
		 ,NUMERO_IDENTIF_CARGA_CITA 
		 ,CAST(H1.ESPECILIDAD_CITA_ID AS INTEGER) ESPECILIDAD_CITA_ID
		 ,NUMERO_DIAS_OPORTUNIDAD 
		 ,IND_TIPO_FAM_PROFESIONAL
		 ,NUMERO_EXPEDIENTE_ARP
		 ,NUMERO_AUTORIZACION_ARP 
		 ,USER
		 ,CURRENT_TIMESTAMP(0)
		 ,1600010 
	FROM EDW_STAGE.PE711_AGCITA TEMP
	
	LEFT OUTER JOIN  EDW_VIEW.V1_IDENTIFICATION_TYPE Z5 
	ON (Z5.EXT_IDENTIFICATION_TYPE_OP = TEMP.CODIGO_TIPO_IDENT_PRO_OP )
	
	LEFT OUTER JOIN EDW_STAGE.PE711_T1 B6 
	ON (B6.CODIGO_CLIENTE_PROFESIONAL_OP = TEMP.CODIGO_CLIENTE_PROFESIONAL_OP  
	AND Z5.EXT_IDENTIFICATION_TYPE_CD  = B6.TIPO_IDENTIFICACION_CD)
	
	LEFT OUTER JOIN  EDW_VIEW.V1_IDENTIFICATION_TYPE T1 
	ON (T1.EXT_IDENTIFICATION_TYPE_OP = 'CSIPSAGE')
	
	LEFT OUTER JOIN  EDW_STAGE.PE711_T3 D4 
	ON (D4.CODIGO_CLIENTE_IPS_OP = TEMP.CODIGO_CLIENTE_IPS_OP  
	AND T1.EXT_IDENTIFICATION_TYPE_CD  = D4.TIPO_IDENTIFICACION_CD)
	
	LEFT OUTER JOIN  EDW_VIEW.V1_IDENTIFICATION_TYPE T10 
	ON (T10.EXT_IDENTIFICATION_TYPE_OP = TEMP.CODIGO_TIPO_IDENT_PAC_OP )
	
	LEFT OUTER JOIN  EDW_STAGE.PE711_T2  E9 
	ON (E9.CODIGO_CLIENTE_PACIENTE_OP = TEMP.CODIGO_CLIENTE_PACIENTE_OP 
	AND T10.EXT_IDENTIFICATION_TYPE_CD  = E9.TIPO_IDENTIFICACION_CD )
	 
	LEFT OUTER JOIN  MDB_CONSULTAS.VC_SERVICIO_CITA C8 
	ON (C8.CODIGO_SERVICIO_CITA_OP = TEMP.CODIGO_SERVICIO_CITA_OP ) 
  
        LEFT OUTER JOIN MDB_CONSULTAS.VC_PLAN_CITA P
  	ON P.CODIGO_PLAN_CITA_OP = TEMP.CODIGO_PLAN_CITA_OP
  
	LEFT OUTER JOIN  MDB_CONSULTAS.VC_PLAN_AGRUPADOR_CITA G9 
	ON (G9.CODIGO_PLAN_AGRUPADOR_CITA_OP = TEMP.CODIGO_PLAN_AGRUPADOR_CITA_OP )

	LEFT OUTER JOIN MDB_CONSULTAS.VC_ESTADO_CITA E 
	ON (E.CODIGO_ESTADO_CITA_OP = TEMP.CODIGO_ESTADO_CITA_OP )
	
 	LEFT OUTER JOIN MDB_CONSULTAS.VC_CAUSA_OPORTUNIDAD_AGENDA I1 
  	ON (I1.CODIGO_CAUSA_OPORTUNIDAD_OP = TEMP.CODIGO_CAUSA_OPORTUNIDAD_OP )
  	
  	LEFT OUTER JOIN MDB_CONSULTAS.VC_TIPO_PROMESA_AGENDA T  
  	ON (T.CODIGO_TIPO_PROMESA_OP = TEMP.CODIGO_TIPO_PROMESA_OP )
  	
  	LEFT OUTER JOIN  MDB_CONSULTAS.VC_ESPECIALIDAD_CITA H1 
  	ON (H1.CODIGO_ESPECILIDAD_CITA_OP = TEMP.CODIGO_ESPECIALIDAD_CITA_OP )
  	
  	WHERE
	B6.CLIENTE_PROFESIONAL_ID IS NOT NULL
	AND C8.SERVICIO_CITA_ID IS NOT NULL
	AND D4.CLIENTE_IPS_ID IS NOT NULL
	AND I1.CAUSA_OPORTUNIDAD_ID  IS NOT NULL
	AND H1.ESPECILIDAD_CITA_ID IS NOT NULL
	
		/* AÑO 2009 */
	AND TEMP.FECHA_HORA_CITA BETWEEN '2009-01-01 00:00:00' AND '2009-12-31 23:59:59'
	;
TD_DEV245 20 posts Joined 08/12
06 Sep 2012

Hi,

 Please check if the datatypes of columns in source and target match exactly if not use cast()

 

Thanks,

Vijayshankar

''Human race acts as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about''-Albert Einstein

TD_DEV245 20 posts Joined 08/12
10 Sep 2012

Hi Juan,

           Found out which column was creating problem?

 

''Human race acts as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about''-Albert Einstein

Prabhu_Teradata 17 posts Joined 06/12
10 Sep 2012

Hi Juan,

          This error will occur in Numerical data types(BYTEINT,SMALLINT,INT,BIGINT,DECIMAL(T,D)), So please check you numeric data types are matching and returing value with in the specific limits.

Thanks,
Prabhu

You must sign in to leave a comment.