CREATE MULTISET VOLATILE TABLE JBS_QMIGMOVIL, NO LOG AS ( SELECT A.Periodo, CAST(A.Region AS VARCHAR(50)) AS Region, CAST(A.Des_Departamento AS VARCHAR(50)) AS Des_Departamento, A.CiudadID, A.Num_Ident, CASE WHEN TRIM(A.Num_Ident) IS NULL THEN 'NA' WHEN TRIM(A.Num_Ident) IN (' ','') THEN 'NA' WHEN A.Num_Ident LIKE ('%-%') THEN CASE WHEN POSITION ('-' IN A.Num_Ident) = 1 THEN TRIM(SUBSTR(A.Num_Ident,POSITION ('-' IN A.Num_Ident)+1,50)) ELSE TRIM(SUBSTR(A.Num_Ident,1,POSITION ('-' IN A.Num_Ident)-1)) END WHEN A.Num_Ident LIKE ('%.%') THEN CASE WHEN POSITION ('.' IN A.Num_Ident) = 1 THEN TRIM(SUBSTR(A.Num_Ident,POSITION ('.' IN A.Num_Ident)+1,50)) ELSE TRIM(SUBSTR(A.Num_Ident,1,POSITION ('.' IN A.Num_Ident)-1)) END ELSE TRIM(A.Num_Ident) END AS C_IDENTIFICACION, A.Cod_Cliente, A.ClienteID, A.AbonadoID, A.AbonadoCD, A.Num_Celular, A.Fec_Alta, A.Fec_alta_HistPOS, (CAST(CURRENT_DATE AS DATE) - A.Fec_Alta) / CAST(30 AS FLOAT) AS Q_ANTIGUEDAD, (CAST(CURRENT_DATE AS DATE) - A.Fec_alta_HistPOS) / CAST(30 AS FLOAT) AS Q_ANTIGUEDAD_POS, A.CicloFacturacionID, CAST(A.CICLO AS INTEGER) AS CICLO, TRIM(A.Cod_Segmento) AS Cod_Segmento, CAST(TRIM(A.Desc_Segmento) AS VARCHAR(50)) AS Desc_Segmento, TRIM(A.Des_tippersona) AS Des_tippersona, CAST(TRIM(A.producto) AS VARCHAR(50)) AS producto, TRIM(A.tip_producto) AS tip_producto, TRIM(A.Cod_situacion) AS Cod_situacion, TRIM(A.Cod_Plantarif) AS Cod_Plantarif, A.CARGO_BASICO_SIN_IVA, A.MINSALMOVISTAR, A.MINSALRF, (A.MINSAL - A.MINSALMOVISTAR - A.MINSALRF) AS MINSALoff, CAST(B.Nombre AS VARCHAR(50)) AS D_CIUDAD, CAST(C.Nombre AS VARCHAR(50)) AS D_CLIENTE, CASE WHEN SUBSTR(imsi_gsm,7, 1) = 6 THEN 'USIM' ELSE 'SIM' END Marca_USIM, CASE WHEN A.tip_producto LIKE ANY ('%M2M%','%DATOS M2M%') THEN 'M2M' WHEN UPPER(D.ModeloTerminalDesc) LIKE ANY ('%MODEM%','%USB%','%WIFI%','%M2M%') OR UPPER(D.LineaProductoDesc) LIKE ANY ('%M2M%') THEN 'M2M' end AS D_LINEAPRODUCTO FROM vwVectorContrato A LEFT JOIN vwCiudad B ON (A.CiudadID = B.CiudadID) LEFT JOIN vwCliente C ON (A.ClienteID = C.ClienteID) LEFT JOIN vwVentaInicial D ON (A.AbonadoCD = D.AbonadoCD) /*LEFT JOIN vwcarteracuentafacturacion E ON (A.AbonadoCD = E.AbonadoCD) LEFT JOIN MAD.Factuacion_Movil_modelos F ON (A.AbonadoCD = F.AbonadoCD) */ QUALIFY(ROW_NUMBER() OVER(PARTITION BY A.Num_Celular ORDER BY A.Periodo DESC, A.CARGO_BASICO_SIN_IVA DESC))=1 WHERE A.Periodo >= (CURRENT_DATE -30) (FORMAT 'YYYYMM')(CHAR(7)) /*CURRENT_DATE (FORMAT 'YYYYMM') (CHAR(7))*/ /*E.Ind_mora NOT IN (0,1) */ /*A.Periodo = &PERIODO_MODELO/**/ )WITH DATA PRIMARY INDEX(Num_Celular) ON COMMIT PRESERVE ROWS;