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 ( 4 years ago )
CREATE TABLE d_mdl_vpc_disc.MM_BASE_RCC_AGENDA
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_periodo'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/MM_BASE_RCC_AGENDA/'
)
AS (
SELECT * FROM
(
SELECT S.periodo_campania
,S.periodo_ejecucion
,R.periodo_val periodo_rcc
,R.fecha_sbs_dt
,S.num_documento
,R.cod_sbs_val,
S.periodo_ejecucion p_periodo
FROM UNIVERSO_PREVIO_3 S
INNER JOIN e_perm_aws.t_fact_vpc_detalle_cliente_rcc R
ON S.cod_sbs_empresa_1 = R.cod_sbs_val
AND S.periodo_ejecucion = substr(replace(substr(cast(date_add('month', +1, date_parse(R.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
WHERE 1 = 1
and S.num_documento is not null
and S.cod_sbs_empresa_1 is not null
and S.cod_sbs_empresa_1 not like ''
and S.cod_sbs_empresa_1 != '.'
)
UNION
(
SELECT S.periodo_campania
,S.periodo_ejecucion
,R.periodo_val periodo_rcc
,R.fecha_sbs_dt
,S.num_documento
,R.cod_sbs_val,
S.periodo_ejecucion p_periodo
FROM UNIVERSO_PREVIO_3 S
INNER JOIN e_perm_aws.t_fact_vpc_detalle_cliente_rcc R
ON S.cod_sbs_empresa_2 = R.cod_sbs_val
AND S.periodo_ejecucion = substr(replace(substr(cast(date_add('month', +1, date_parse(R.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
WHERE 1 = 1
and S.num_documento is not null
and S.cod_sbs_empresa_2 is not null
and S.cod_sbs_empresa_2 not like ''
and S.cod_sbs_empresa_2 != '.'
)
UNION
(
SELECT S.periodo_campania
,S.periodo_ejecucion
,R.periodo_val periodo_rcc
,R.fecha_sbs_dt
,S.num_documento
,R.cod_sbs_val,
S.periodo_ejecucion p_periodo
FROM UNIVERSO_PREVIO_3 S
INNER JOIN e_perm_aws.t_fact_vpc_detalle_cliente_rcc R
ON S.cod_sbs_empresa_3 = R.cod_sbs_val
AND S.periodo_ejecucion = substr(replace(substr(cast(date_add('month', +1, date_parse(R.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
WHERE 1 = 1
and S.num_documento is not null
and S.cod_sbs_empresa_3 is not null
and S.cod_sbs_empresa_3 not like ''
and S.cod_sbs_empresa_3 != '.'
)
UNION
(
SELECT S.periodo_campania
,S.periodo_ejecucion
,R.periodo_val periodo_rcc
,R.fecha_sbs_dt
,S.num_documento
,R.cod_sbs_val,
S.periodo_ejecucion p_periodo
FROM UNIVERSO_PREVIO_3 S
INNER JOIN e_perm_aws.t_fact_vpc_detalle_cliente_rcc R
ON S.cod_sbs_empresa_4 = R.cod_sbs_val
AND S.periodo_ejecucion = substr(replace(substr(cast(date_add('month', +1, date_parse(R.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
WHERE 1 = 1
and S.num_documento is not null
and S.cod_sbs_empresa_4 is not null
and S.cod_sbs_empresa_4 not like ''
and S.cod_sbs_empresa_4 != '.'
)
UNION
(
SELECT S.periodo_campania
,S.periodo_ejecucion
,R.periodo_val periodo_rcc
,R.fecha_sbs_dt
,S.num_documento
,R.cod_sbs_val,
S.periodo_ejecucion p_periodo
FROM UNIVERSO_PREVIO_3 S
INNER JOIN e_perm_aws.t_fact_vpc_detalle_cliente_rcc R
ON S.cod_sbs_rrll_1 = R.cod_sbs_val
AND S.periodo_ejecucion = substr(replace(substr(cast(date_add('month', +1, date_parse(R.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
WHERE 1 = 1
and S.flg_pn = 1
and S.num_documento is not null
and S.cod_sbs_rrll_1 is not null
and S.cod_sbs_rrll_1 not like ''
and S.cod_sbs_rrll_1 != '.'
AND (cod_sbs_empresa_1 IS NULL OR cod_sbs_empresa_1 like '' OR cod_sbs_empresa_1 = '.')
)
UNION
(
SELECT S.periodo_campania
,S.periodo_ejecucion
,R.periodo_val periodo_rcc
,R.fecha_sbs_dt
,S.num_documento
,R.cod_sbs_val,
S.periodo_ejecucion p_periodo
FROM UNIVERSO_PREVIO_3 S
INNER JOIN e_perm_aws.t_fact_vpc_detalle_cliente_rcc R
ON S.cod_sbs_rrll_2 = R.cod_sbs_val
AND S.periodo_ejecucion = substr(replace(substr(cast(date_add('month', +1, date_parse(R.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
WHERE 1 = 1
and S.flg_pn = 1
and S.num_documento is not null
and S.cod_sbs_rrll_2 is not null
and S.cod_sbs_rrll_2 not like ''
and S.cod_sbs_rrll_2 != '.'
AND (cod_sbs_empresa_1 IS NULL OR cod_sbs_empresa_1 like '' OR cod_sbs_empresa_1 = '.')
)
UNION
(
SELECT S.periodo_campania
,S.periodo_ejecucion
,R.periodo_val periodo_rcc
,R.fecha_sbs_dt
,S.num_documento
,R.cod_sbs_val,
S.periodo_ejecucion p_periodo
FROM UNIVERSO_PREVIO_3 S
INNER JOIN e_perm_aws.t_fact_vpc_detalle_cliente_rcc R
ON S.cod_sbs_rrll_3 = R.cod_sbs_val
AND S.periodo_ejecucion = substr(replace(substr(cast(date_add('month', +1, date_parse(R.periodo_val ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6)
WHERE 1 = 1
and S.flg_pn = 1
and S.num_documento is not null
and S.cod_sbs_rrll_3 is not null
and S.cod_sbs_rrll_3 not like ''
and S.cod_sbs_rrll_3 != '.'
AND (cod_sbs_empresa_1 IS NULL OR cod_sbs_empresa_1 like '' OR cod_sbs_empresa_1 = '.')
)
)
Revise this Paste