Paste code

DROP TABLE IF EXISTS .................T_MERCADO_BPE_ADQ_SF_P01


                            CREATE TABLE .................T_MERCADO_BPE_ADQ_SF_P01
                            WITH ( format = &#039;Parquet&#039;, 
                                      parquet_compression = &#039;SNAPPY&#039;, 
                                      partitioned_by = ARRAY[&#039;PERIODO&#039;], 
                                      external_location= &#039;s3://..................................../T_MERCADO_BPE_ADQ_SF_P01/&#039;
                                    )
                            AS (
                            SELECT 
                                     cod_sbs_val AS CODSBS,
                                     producto_rcc_desc AS PRODUCTO,
                                     situacion_rcc_desc AS SITUACION,
                                     empresa_cd AS COD_EMPRESA,
                                     CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) = &#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN &#039;BANCOS&#039; ELSE &#039;CAJAS & FINANC.&#039; END AS TIPO_ENTIDAD,
                                     CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) = &#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN nomcortoempresafinanc_desc ELSE &#039;CAJAS & FINANC.&#039; END AS BANCO,
                                     tipo_producto_rcc_desc TIPO_PRODUCTO,
                                     SUM(saldo_amt) MTO_SALDO,
                                     MAX(flg_lin_mercado) flg_lin_mercado, 
                                     periodo_val AS PERIODO
                               FROM .....................T_VPC_FACT_SALDOS_RCC
                               WHERE 1 = 1
                               AND date_parse(cast(periodo_val as varchar),&#039;%Y%m&#039;) >= (SELECT date_add(&#039;month&#039;, -18, date_parse(cast(MAX(periodo_val) as varchar),&#039;%Y%m&#039;)) FROM .....................T_VPC_FACT_SALDOS_RCC)
                               AND cod_sbs_val in (select cod_sbs_val from .....................t_vpc_segmentacion_merc where banca_fin_val = &#039;BPE&#039; or banca_mes_val = &#039;BPE&#039;)
                               AND tipo_producto_rcc_desc IN (
                                    &#039;COL. DIRECTAS&#039;,&#039;REACTIVA&#039;,&#039;COL. INDIRECTAS&#039;,&#039;FAE&#039;,&#039;INMOBILIARIO&#039;
                               )
                               AND 
                               (
                                     (
                                            flg_lin_mercado = &#039;S&#039; AND
                                            --(
                                                 producto_rcc_desc NOT IN (&#039;OTROS&#039;) 
                                                 --OR 
                                                 --(
                                                 --  B.PRODUCTO =&#039;OTROS&#039; AND 
                                                 --  B.DETALLE IN (&#039;FINANCIACION DE PROYECTOS&#039;)
                                                 --)
                                            --)
                                     )                                
                                     OR tipo_producto_rcc_desc IN (&#039;REACTIVA&#039;,&#039;FAE&#039;,&#039;INMOBILIARIO&#039;)
                              )
                              GROUP BY 
                                     periodo_val,
                                     cod_sbs_val,
                                     producto_rcc_desc,
                                     situacion_rcc_desc,
                                     empresa_cd,
                                     CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN &#039;BANCOS&#039; ELSE &#039;CAJAS & FINANC.&#039; END,
                                     CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN nomcortoempresafinanc_desc ELSE &#039;CAJAS & FINANC.&#039; END,
                                     tipo_producto_rcc_desc
                              
                            )