Untitled - MARKUP 2.76 KB
                                
                                    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;
                                
                            

Paste Hosted With By Paste.ly