etl_intercambio/ofertas_mer_ut/querys.sql

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP 1000 [LINE]
      ,[ROWNUM]
  FROM [LCL_STAGGING].[dbo].[FLAT_FILE_OF_MER_UT]


 SELECT ROWNUM FROM FLAT_FILE_OF_MER_UT WHERE LINE LIKE '%IDENTIFICACION%'

SELECT LINE,
CASE FLAT_FILE_OF_MER_UT.ROWNUM
	WHEN 4 THEN 'AGENTE'
	WHEN 5 THEN 'DIA'
	WHEN 6 THEN 'MES'
	WHEN 7 THEN 'AÑO'
	ELSE 'OTRO'
END DATA, 'IDENTIFICACION' AS ID 
FROM FLAT_FILE_OF_MER_UT 
WHERE 
ROWNUM > (2+1) AND
ROWNUM < (SELECT MIN(ROWNUM) FROM FLAT_FILE_OF_MER_UT WHERE LINE = '' AND ROWNUM > (2))

SELECT ROWNUM FROM FLAT_FILE_OF_MER_UT WHERE LINE LIKE '%DECLARACION CONTRATOS RETIROS MER%'

SELECT LINE FROM FLAT_FILE_OF_MER_UT
WHERE ROWNUM > (9+1) AND
ROWNUM < (SELECT MIN(ROWNUM) FROM FLAT_FILE_OF_MER_UT WHERE LINE = '' AND ROWNUM > (9))

DECLARE @LARGO = 12
GO

SELECT
SUBSTRING(LINE,0,12) MEDICION_RET,
UPPER(SUBSTRING(LINE,12,12)) AGENTE_INYECTA,
SUBSTRING(LINE,24,12) MEDICION_INY,
SUBSTRING(LINE,36,12) HORA_INICIO,
SUBSTRING(LINE,48,12) HORA_FIN,
SUBSTRING(LINE,60,12) TIPO_CONTRATO,
SUBSTRING(LINE,72,13) COD_CF,
SUBSTRING(LINE,85,12) TITULAR_DT,
SUBSTRING(LINE,96,12) RESPONSABLE_TRANSMISION,
SUBSTRING(LINE,109,12) ENERGIA_DECLARADA,
SUBSTRING(LINE,120,12) ENERGIA_REQUERIDA,
SUBSTRING(LINE,132,12) OFERTA_PMAX,
SUBSTRING(LINE,144,12) ENERGIA_B1,
SUBSTRING(LINE,156,12) PRECIO_B1,
SUBSTRING(LINE,168,12) ENERGIA_B2,
SUBSTRING(LINE,180,12) PRECIO_B2,
SUBSTRING(LINE,192,12) ENERGIA_B3,
SUBSTRING(LINE,204,12) PRECIO_B3,
SUBSTRING(LINE,216,12) ENERGIA_B4,
SUBSTRING(LINE,228,12) PRECIO_B4,
SUBSTRING(LINE,240,12) ENERGIA_B5,
SUBSTRING(LINE,252,12) PRECIO_B5,
SUBSTRING(LINE,264,12) OFERTA_FLEX,
SUBSTRING(LINE,276,12) TIPO_OFERTA_FLEX,
SUBSTRING(LINE,288,12) ENERGIAF_B1

FROM FLAT_FILE_OF_MER_UT
WHERE ROWNUM > (9+1) AND
ROWNUM < (SELECT MIN(ROWNUM) FROM FLAT_FILE_OF_MER_UT WHERE LINE = '' AND ROWNUM > (9))

SELECT LEN(LINE) FROM FLAT_FILE_OF_MER_UT WHERE ROWNUM > (9+1) AND
ROWNUM < (SELECT MIN(ROWNUM) FROM FLAT_FILE_OF_MER_UT WHERE LINE = '' AND ROWNUM > (9))