Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted as SQL by registered user vvillacorta ( 2 years ago )
------ Universo SF ------
drop table DLAB_PROGRAMADATOSVPC.HM_TARGET_SF;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.HM_TARGET_SF as (
SELECT
DISTINCT
a.Periodo_val,
a.cod_sbs_val,
a.tipo_producto_rcc_desc,
A.Flg_Lin_Mercado,
a.NombreEmpresaFinanc_Desc,
a.Empresa_CD,
PRODUCTO_RCC_DESC,
SITUACION_RCC_DESC,
SALDO_AJUSTADO_AMT
from E_DW_VIEWS.V_FACT_VPC_IRCC_SALDO_AJUST a
where (A.Flg_Lin_Mercado='S' or a.tipo_producto_rcc_desc in ('REACTIVA','FAE','INMOBILIARIO','GARANTIAS'))
and PERIODO_VAL >= '202201'
and SITUACION_RCC_DESC IN ('VIGENTE', 'VENCIDO') AND PRODUCTO_RCC_DESC IN ('FACTORING', 'DESCUENTOS')
) WITH DATA PRIMARY INDEX (cod_sbs_val);
-- SACANDO EL RUC DE LAS PERSONAS
SELECT PERIODO_VAL, COUNT(1) FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF GROUP BY PERIODO_VAL;
---RCC PERSONA
DROP TABLE DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA
as
(
SELECT A.CODSBS,
A.TIPPERSONA,
CASE
WHEN A.CODDOC = '.' THEN '99'
ELSE A.TIPDOC
END AS TIPDOC,
CASE
WHEN A.CODDOC = '.' THEN A.CODSBS
ELSE A.CODDOC
END AS CODDOC,
A.CODUNICOCLI_F AS CODUNICOCLI,
A.NOMBRE_COMPLETO,
NVL(A.APELLIDO_PATERNO, '.') AS APELLIDO_PATERNO,
NVL(A.APELLIDO_MATERNO, '.') AS APELLIDO_MATERNO,
NVL(A.PRIMER_NOMBRE, '.') AS PRIMER_NOMBRE,
NVL(A.SEGUNDO_NOMBRE, '.') AS SEGUNDO_NOMBRE,
A.TIP_DOC,
A.NUM_DOC,
A.NUM_RUC,
A.FECACTUALIZACIONTABLA,
A.TIP_DOC_TRIB,
A.NUM_DOC_TRIB
FROM (
SELECT A.*,
RTRIM(
NVL(A.APELLIDO_PATERNO, '') || ' ' || NVL(A.APELLIDO_MATERNO, '') || ' ' || NVL(A.PRIMER_NOMBRE, '') || ' ' || NVL(A.SEGUNDO_NOMBRE, '')
) AS NOMBRE_COMPLETO,
CASE
WHEN NVL(A.TIPDOC, '') <> '2' THEN A.TIPDOC
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE '10%' THEN '1'
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE ANY ('15%', '17%', '20%') THEN '.'
ELSE A.TIPDOC
END AS TIP_DOC,
CASE
WHEN NVL(A.TIPDOC, '') <> '2' THEN A.CODDOC
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE '10%' THEN SUBSTRING(A.CODDOC, 3, 8)
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE ANY ('15%', '17%', '20%') THEN '.'
ELSE A.CODDOC
END AS NUM_DOC,
CASE
WHEN A.TIPDOC = '2'
AND LENGTH(A.CODDOC) = 11
AND A.CODDOC LIKE ANY ('10%', '15%', '17%', '20%') THEN A.CODDOC
WHEN A.TIPDOC = '1' THEN COALESCE(B.NUMRUC, R.RUC15, '.')
ELSE '.'
END AS NUM_RUC,
COALESCE (
A.CODUNICOCLI,
CASE
WHEN LENGTH(TRIM(C.CUC_NUM)) = 10 THEN TRIM(C.CUC_NUM)
ELSE NULL
END,
CASE
WHEN LENGTH(TRIM(D.CUC_NUM)) = 10 THEN TRIM(D.CUC_NUM)
ELSE NULL
END
) AS CODUNICOCLI_F,
COALESCE (
CASE
WHEN A.CODUNICOCLI IS NOT NULL THEN CURRENT_DATE()
ELSE NULL
END,
CASE
WHEN LENGTH(TRIM(C.CUC_NUM)) = 10 THEN C.APERTURA_CUC_FC
ELSE NULL
END,
CASE
WHEN LENGTH(TRIM(D.CUC_NUM)) = 10 THEN D.APERTURA_CUC_FC
ELSE NULL
END
) AS APERTURA_CUC_FC
FROM (
SELECT A.CODSBS,
NVL(A.TIPPERSONA, 0) AS TIPPERSONA,
CAST(
NULLIF(
CASE
WHEN A.TIPDOCTRIB = 3
AND LENGTH(A.NUMDOCTRIB) = 11
AND (A.NUMDOCTRIB LIKE ANY ('10%', '15%', '17%', '20%')) THEN 2
ELSE A.TIPDOCTRIB
END,
-1
) AS VARCHAR(1)
) AS TIP_DOC_TRIB,
NULLIF(A.NUMDOCTRIB, '.') AS NUM_DOC_TRIB,
CASE
WHEN A.TIPPERSONA IN (1, 3, 4) THEN CASE
WHEN A.TIPDOC = '2'
AND A.CODDOC = A.CODDOC_TRDT
AND A.TIPDOC_TRDT IS NOT NULL THEN A.TIPDOC_TRDT
ELSE A.TIPDOC
END
ELSE CASE
WHEN A.TIPDOCTRIB = 3
AND LENGTH(A.NUMDOCTRIB) = 11
AND (A.NUMDOCTRIB LIKE ANY ('10%', '15%', '17%', '20%')) THEN '2'
ELSE CAST(A.TIPDOCTRIB AS VARCHAR(2))
END
END AS TIPDOC,
CASE
WHEN A.TIPPERSONA IN (1, 3, 4) THEN A.CODDOC
ELSE A.NUMDOCTRIB
END AS CODDOC,
CASE
WHEN LENGTH(TRIM(A.CODUNICOCLI)) = 10 THEN TRIM(A.CODUNICOCLI)
ELSE NULL
END AS CODUNICOCLI,
CAST(
UPPER(TRIM(NULLIF(OREPLACE(A.APPATERNO, '"', ''), '.'))) AS VARCHAR(120)
) AS APELLIDO_PATERNO,
CAST(
UPPER(TRIM(NULLIF(OREPLACE(A.APMATERNO, '"', ''), '.'))) AS VARCHAR(40)
) AS APELLIDO_MATERNO,
CAST(
UPPER(TRIM(NULLIF(OREPLACE(A.PRIMERNBR, '"', ''), '.'))) AS VARCHAR(40)
) AS PRIMER_NOMBRE,
CAST(
UPPER(TRIM(NULLIF(OREPLACE(A.SEGUNDONBR, '"', ''), '.'))) AS VARCHAR(40)
) AS SEGUNDO_NOMBRE,
A.FECACTUALIZACIONTABLA
FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC A
) A
LEFT JOIN E_DW_VIEWS.V_DATOS_PRINCIPALES_SUNAT B ON A.TIPDOC = '1'
AND SUBSTRING(B.NUMRUC, 1, 2) = '10'
AND A.CODDOC = SUBSTRING(B.NUMRUC, 3, 8)
LEFT JOIN DLAB_PROGRAMADATOSVPC.DE_CLIENTE_RUC15 R ON A.TIPDOC = R.TIPDOC
AND A.CODDOC = R.CODDOC
LEFT JOIN E_DW_VIEWS.V_SCO_LCL_MAEPERSONA C ON A.CODSBS = C.CLIENTE_SBS_CD
LEFT JOIN E_DW_VIEWS.V_SCO_LCL_MAEPERSONA D ON A.TIPDOC = D.COD_TIPO_DOCUMENTO
AND A.CODDOC = D.COD_DOCUMENTO
) A QUALIFY ROW_NUMBER() OVER(
PARTITION BY A.CODSBS
ORDER BY A.APERTURA_CUC_FC DESC
) = 1
)
WITH DATA
PRIMARY INDEX(CODSBS);
---
drop table DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_AGG;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_AGG as (
SELECT Periodo_val,Cod_SBS_Val,
MAX(CASE WHEN TIPO_PRODUCTO_RCC_DESC IN ('COL. DIRECTAS','REACTIVA') AND SITUACION_RCC_DESC IN ('VIGENTE','VENCIDO') AND PRODUCTO_RCC_DESC='FACTORING' then 1
WHEN TIPO_PRODUCTO_RCC_DESC IN ('COL. DIRECTAS','REACTIVA') AND SITUACION_RCC_DESC IN ('VIGENTE','VENCIDO') AND PRODUCTO_RCC_DESC= 'DESCUENTOS' THEN 1 ELSE 0 END) AS TARGET_SF
--max(CASE WHEN TIPO_PRODUCTO_RCC_DESC IN ('COL. DIRECTAS','REACTIVA') AND SITUACION_RCC_DESC='VIGENTE' AND NOMBREEMPRESAFINANC_DESC ='IBK' AND PRODUCTO_RCC_DESC='FACTORING' THEN 1 ELSE 0 END) AS flg_FACTORING_IBK_VIGENTE
FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF A
group by Periodo_val,Cod_SBS_Val
)WITH DATA
PRIMARY INDEX(Cod_SBS_Val);
--
--- SACANDO EL RUC DE MIS CLIENTES sf
drop table DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC;
CREATE MULTISET TABLE DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC as (
SELECT * FROM (SELECT A.* ,
B.NUM_RUC,
C.KEY_VALUE
FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_AGG A
LEFT JOIN DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA B
LEFT JOIN E_DW_VIEWS.V_MST_ENCRIP_NRO_DOC_MDL_HISTDIA C ON B.NUM_RUC = C.NRO_DOC
ON A.Cod_SBS_Val = B.CODSBS) A WHERE KEY_VALUE IS NOT NULL
)
WITH DATA
PRIMARY INDEX(Cod_SBS_Val);
SELECT Periodo_val, SUM(TARGET_SF), COUNT(1) FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC GROUP BY Periodo_val;
SELECT * FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC
SELECT * FROM DLAB_PROGRAMADATOSVPC.HM_TARGET_SF_RUC ;
DEL DLAB_PROGRAMADATOSVPC.TMP_VPC_RCC_PERSONA;
Revise this Paste