Paste code

DELETE FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS
/*
DROP TABLE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS  (
	  PERIODO varchar(25) NULL,      
	  PERIODO_2 varchar(25) NULL,
	  FUENTE varchar(25) NULL,
      TIPO_ORIGEN varchar(20) NULL,
      TIPO varchar(255) NULL,
      CU_PROVEEDOR varchar(255) NULL,
      NUM_DOC_PROVEEDOR varchar(255) NULL,
      CU_HUB varchar(255) NULL,
      NUM_DOC_HUB varchar(255) NULL
)  PRIMARY INDEX (FUENTE, TIPO_ORIGEN, TIPO,CU_PROVEEDOR,NUM_DOC_PROVEEDOR,CU_HUB,NUM_DOC_HUB);

select count(1) ,  FUENTE , TIPO_ORIGEN from DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS GROUP BY FUENTE , TIPO_ORIGEN;
select * from DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS;
*/
SELECT COUNT(1) FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS   -- 6843669


SELECT COUNT(1) FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS 
WHERE LENGTH(NUM_DOC_PROVEEDOR) IN (8)

SELECT COUNT(1) FROM E_DW_VIEWS.V_MST_VPC_SUNAT_REDUCIDO


UPDATE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS
SET
  PERIODO = TRIM(BOTH &#039; &#039; FROM PERIODO),
  PERIODO_2 = TRIM(BOTH &#039; &#039; FROM PERIODO_2),
  FUENTE = TRIM(BOTH &#039; &#039; FROM FUENTE),
  TIPO_ORIGEN = TRIM(BOTH &#039; &#039; FROM TIPO_ORIGEN),
  TIPO = TRIM(BOTH &#039; &#039; FROM TIPO),
  CU_PROVEEDOR = OREPLACE(OREPLACE(OREPLACE(TRIM(BOTH &#039; &#039; FROM CU_PROVEEDOR), &#039;C&#039;, &#039;&#039;), &#039;€&#039;, &#039;&#039;), &#039;G&#039;, &#039;&#039;),
  NUM_DOC_PROVEEDOR = OREPLACE(OREPLACE(OREPLACE(OREPLACE(TRIM(BOTH &#039; &#039; FROM NUM_DOC_PROVEEDOR), &#039;C&#039;, &#039;&#039;), &#039;€&#039;, &#039;&#039;), &#039;G&#039;, &#039;&#039;), &#039;-&#039;, &#039;&#039;),
  CU_HUB = OREPLACE(OREPLACE(OREPLACE(TRIM(BOTH &#039; &#039; FROM CU_HUB), &#039;C&#039;, &#039;&#039;), &#039;€&#039;, &#039;&#039;), &#039;G&#039;, &#039;&#039;),
  NUM_DOC_HUB = OREPLACE(OREPLACE(OREPLACE(TRIM(BOTH &#039; &#039; FROM NUM_DOC_HUB), &#039;C&#039;, &#039;&#039;), &#039;€&#039;, &#039;&#039;), &#039;G&#039;, &#039;&#039;);

UPDATE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS
SET
   NUM_DOC_PROVEEDOR = SUBSTRING(NUM_DOC_PROVEEDOR,1,11)
--SELECT * FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS
WHERE LENGTH(NUM_DOC_PROVEEDOR) IN (12) 
AND SUBSTRING(NUM_DOC_PROVEEDOR,12,1) = &#039;6&#039;
AND NUM_DOC_PROVEEDOR LIKE &#039;20%&#039;

---8
---- RESCATANDO ruc 10 


DROP TABLE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT;		
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT AS (
			SELECT MM.PERIODO, 
			       MM.PERIODO_2,
				   MM.FUENTE, 
				   MM.TIPO_ORIGEN, 
				   MM.TIPO, 
			       MM.CU_PROVEEDOR, 
				   MM.NUM_DOC_PROVEEDOR AS NUM_DOC_PROVEEDOR_INIT,
				   CASE WHEN LENGTH(MM.NUM_DOC_PROVEEDOR) = 8 AND SUNAT8.Ruc_Val IS NOT NULL THEN SUNAT8.Ruc_Val
				        WHEN LENGTH(MM.NUM_DOC_PROVEEDOR) = 8 AND YY.NUM_DOC_PROVEEDOR IS NOT NULL THEN YY.NUM_DOC_PROVEEDOR
					ELSE MM.NUM_DOC_PROVEEDOR END NUM_DOC_PROVEEDOR, 
				   MM.CU_HUB, 
				   MM.NUM_DOC_HUB
			FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS MM
			LEFT JOIN E_DW_VIEWS.V_MST_VPC_SUNAT_REDUCIDO SUNAT8
			ON MM.NUM_DOC_PROVEEDOR = SUBSTRING(SUNAT8.Ruc_Val, 3, 8)
			AND LENGTH(MM.NUM_DOC_PROVEEDOR) = 8
			AND SUNAT8.Ruc_Val LIKE &#039;10%&#039;
			LEFT JOIN (SELECT DISTINCT NUM_DOC_PROVEEDOR FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS) YY
			ON MM.NUM_DOC_PROVEEDOR = SUBSTRING(YY.NUM_DOC_PROVEEDOR, 3, 8)
			AND LENGTH(MM.NUM_DOC_PROVEEDOR) = 8
			AND YY.NUM_DOC_PROVEEDOR LIKE &#039;10%&#039;
) WITH DATA PRIMARY INDEX (NUM_DOC_PROVEEDOR);

SELECT COUNT(1) FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS 
WHERE LENGTH(NUM_DOC_PROVEEDOR) IN (8)   -- 1,532,676

SELECT COUNT(1) FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT 
WHERE LENGTH(NUM_DOC_PROVEEDOR) IN (8)   -- 1,045,414

SELECT TOP 100 * FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT
WHERE LENGTH(NUM_DOC_PROVEEDOR) IN (8)   
--- QUITANDO ESPACIOS EN BLANCO


SELECT * FROM E_DW_VIEWS.V_MST_VPC_SUNAT_REDUCIDO WHERE RUC_VAL LIKE &#039;%71606001%&#039; --&#039;%41535660%&#039;
SELECT * FROM E_DW_VIEWS.V_DATOS_PRINCIPALES_SUNAT WHERE NUMRUC LIKE &#039;%71606001%&#039; --&#039;%41535660%&#039;


---- hasheando la data -- 	
-- ojo si todo sale bien eliminar el ruc 

DROP TABLE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT_HASH;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT_HASH AS (
SELECT A.*,
	   B.KEY_VALUE AS NUM_DOC_PROVEEDOR_INIT_HASH,
	   C.KEY_VALUE AS NUM_DOC_PROVEEDOR_HASH,
	   D.KEY_VALUE AS NUM_DOC_HUB_HASH
	
FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT A 
	LEFT JOIN E_DW_VIEWS.V_MST_ENCRIP_NRO_DOC_MDL_HISTDIA B ON A.NUM_DOC_PROVEEDOR_INIT = B.NRO_DOC
	LEFT JOIN E_DW_VIEWS.V_MST_ENCRIP_NRO_DOC_MDL_HISTDIA C ON A.NUM_DOC_PROVEEDOR = C.NRO_DOC
	LEFT JOIN E_DW_VIEWS.V_MST_ENCRIP_NRO_DOC_MDL_HISTDIA D ON A.NUM_DOC_HUB = D.NRO_DOC
) WITH DATA PRIMARY INDEX (NUM_DOC_PROVEEDOR);


/*Eliminando tablas 
 delete from DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT_HASH;
 delete from DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT;
 delete from DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDORES_MODELOS;
 
 */
2051836448-1
205654683926
205024949206
2000002264
SELECT  COUNT(1) FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT_HASH
WHERE NUM_DOC_PROVEEDOR IS NOT NULL AND NUM_DOC_PROVEEDOR_HASH IS NULL
AND NUM_DOC_PROVEEDOR LIKE &#039;20%&#039; AND LENGTH(NUM_DOC_PROVEEDOR) =  11


SELECT TOP 100 * FROM DLAB_PROGRAMADATOSVPC.MM_JRR_BASE_PROVEEDOR_RESCATE_DNI_SUNAT_HASH
WHERE NUM_DOC_PROVEEDOR IS NOT NULL AND NUM_DOC_PROVEEDOR_HASH IS NULL
AND NUM_DOC_PROVEEDOR LIKE &#039;20%&#039;


SELECT TOP 100 * FROM E_DW_VIEWS.V_MST_ENCRIP_NRO_DOC_MDL_HISTDIA WHERE KEY_VALUE LIKE &#039;FD46734D3DB7C58CF42033BB8DB8B02B2A4F3CD81C9%&#039;