Teradata FORMAT: comportamiento caprichoso (cuanto menos)

15 julio \15\UTC 2015

La cláusula FORMAT sirve para fijar el formato con el que se mostrará una columna, pero también influye en el formato que se aceptará en los INSERTs con CASTs implícitos.

Vamos a ver un ejemplo con formatos para DATE y TIME:

SHOW TABLE CARLOS.PRUEBA12;

 *** Text of DDL statement returned.
 *** Total elapsed time was 1 second.

CREATE MULTISET TABLE CARLOS.PRUEBA12 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      ID_N INTEGER NOT NULL,
      D_FECHA DATE FORMAT 'yyyymmdd',
      H_HORA TIME(0) FORMAT 'hhmiss')
PRIMARY INDEX ( ID_N );


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA12 VALUES (1, '20150715',NULL);


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA12 VALUES (2, NULL, '182716');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

El formato definido para la columnna hace incluso que se rechacen cadenas en formato ANSI / ISO-8601:

 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA12 VALUES (3, NULL, '18:28:16');

 *** Failure 6758 Invalid time.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 
 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA12 VALUES (6, '2015-07-15', NULL);

 *** Failure 3535 A character string failed conversion to a numeric value.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.
 
 
 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N, D_FECHA, H_HORA
FROM CARLOS.PRUEBA12;


 *** Query completed. 2 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N   D_FECHA  H_HORA
-----------  --------  ------
          1  20150715  (null)
          2    (null)  182716

Así vemos que hemos fallado miserablemente al insertar fechas y horas en formato diferente al establecido con el FORMAT en la creación de la tabla.

Pero ahora es cuando las cosas se lían un poco:

Creamos el fichero C:\Basura\PRUEBAFORMAT.txt como:

3;20150715;183125
4;20150715;183145

E intentamos una carga con un .IMPORT FILE (nótese que las filas en el fichero respetan los formatos definidos):

 BTEQ -- Enter your SQL request or BTEQ command:
.IMPORT VARTEXT ';' FILE C:\Basura\PRUEBAFORMAT.txt;
 BTEQ -- Enter your SQL request or BTEQ command:
.REPEAT *
 BTEQ -- Enter your SQL request or BTEQ command:
USING
  id_n    (VARCHAR(1)),
  d_fecha (VARCHAR(8)),
  h_hora  (VARCHAR(6))
INSERT INTO CARLOS.PRUEBA12 (ID_N, D_FECHA, H_HORA) 
VALUES (:id_n, :d_fecha, :h_hora);
 *** Starting Row 0 at Wed Jul 15 18:37:37 2015

 *** Failure 6758 Invalid time.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.


 *** Failure 6758 Invalid time.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.


 *** Warning: Out of data.
 *** Finished at input row 2 at Wed Jul 15 18:37:37 2015
 *** Total number of statements: 2,  Accepted : 0,  Rejected : 2

 *** Total elapsed time was 1 second.

 *** Total requests sent to the DBC = 2
 *** Successful requests per second =  2.000

¡Fallo!

Probamos con la carga del mismo fichero, pero como de longitud fija, por si acaso:

 BTEQ -- Enter your SQL request or BTEQ command:
.IMPORT FILE C:\Basura\PRUEBAFORMAT.txt;
 *** Warning: No IMPORT mode was given, assuming field mode.
 BTEQ -- Enter your SQL request or BTEQ command:
.REPEAT *
 BTEQ -- Enter your SQL request or BTEQ command:
USING
  id_n    (CHAR(1)),
  filler1 (CHAR(1)),
  d_fecha (CHAR(8)),
  filler2 (CHAR(1)),
  h_hora  (CHAR(6))
INSERT INTO CARLOS.PRUEBA12 (ID_N, D_FECHA, H_HORA) 
VALUES (:id_n, :d_fecha, :h_hora);
 *** Starting Row 0 at Wed Jul 15 18:45:12 2015

 *** Failure 6758 Invalid time.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.


 *** Failure 6758 Invalid time.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

 *** Warning: Out of data.
 *** Finished at input row 2 at Wed Jul 15 18:45:12 2015
 *** Total number of statements: 2,  Accepted : 0,  Rejected : 2

 *** Total elapsed time was 1 second.

 *** Total requests sent to the DBC = 2
 *** Successful requests per second =  2.000

¡El mismo fallo!

Pero el formato sigue funcionando para cadenas insertadas ‘a mano’ en sentencias:

 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA12 VALUES (3, '20150715','184415');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N, D_FECHA, H_HORA
FROM CARLOS.PRUEBA12;


 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N   D_FECHA  H_HORA
-----------  --------  ------
          1  20150715  (null)
          2    (null)  182716
          3  20150715  184415

Y para terminarlo de liar, modificamos el fichero C:\Basura\PRUEBAFORMAT.txt:

4;20150715;
5;20150715;

Es decir, dejamos las fechas según el FORMAT definido, pero hacemos NULL las horas.

Y hete aquí que:

 BTEQ -- Enter your SQL request or BTEQ command:
.IMPORT VARTEXT ';' FILE C:\Basura\PRUEBAFORMAT.txt;
 BTEQ -- Enter your SQL request or BTEQ command:
.REPEAT *
 BTEQ -- Enter your SQL request or BTEQ command:
USING
  id_n    (VARCHAR(1)),
  d_fecha (VARCHAR(8)),
  h_hora  (VARCHAR(6))
INSERT INTO CARLOS.PRUEBA12 (ID_N, D_FECHA, H_HORA) VALUES (:id_n, :d_fecha, :h_hora);
 *** Starting Row 0 at Wed Jul 15 18:48:15 2015


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 *** Warning: Out of data.
 *** Finished at input row 2 at Wed Jul 15 18:48:15 2015
 *** Total number of statements: 2,  Accepted : 2,  Rejected : 0

 *** Total elapsed time was 1 second.

 *** Total requests sent to the DBC = 2
 *** Successful requests per second =  2.000

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N, D_FECHA, H_HORA
FROM CARLOS.PRUEBA12;


 *** Query completed. 5 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

       ID_N   D_FECHA  H_HORA
-----------  --------  ------
          1  20150715  (null)
          2    (null)  182716
          3  20150715  184415
          4  20150715  (null)
          5  20150715  (null)

¡Y se insertan!

Resumiendo: Los formatos para fechas y horas definen la validez en las inserciones con CASTs implícitos hechas en sentencias. Pero si utilizamos un fichero con IMPORT FILE sólo funcionan en el caso de las fechas (DATE), y fallan en el caso de las horas (TIME).

Ya lo sé, ya lo sé: a mí tampoco me gusta.

Saludos.

Carlos.


New running mate…

12 julio \12\UTC 2015

image

Saludos.

Carlos.


MobaXterm mola

18 junio \18\UTC 2015

Hoy MobaXterm me sacó de apuros cuando el servidor de “desktops” de VMware, VMView, falló -cosa por otra parte nada infrecuente- y estuvo todo el día caído.

Mi pequeña estación de trabajo con un intel atom y un Windows XP “embedded” fue suficiente para poder trabajar muy decentemente mediante ssh en los servidores Linux con esta herramienta. Y desde un USB, sin instalación.

MobaXterm mola.

Saludos.

Carlos.


How does it feel?

16 junio \16\UTC 2015

Once upon a time you dressed so fine

Hoy hace 50 años de la grabación de Like a Rolling Stone.

Saludos.

Carlos.


Convertir filas a cadenas en Teradata (II)

25 mayo \25\UTC 2015

El viejo problema de convertir filas a cadenas de caracteres es un tema recurrente y ha sido tratado aquí. Normalmente hay que recurrir a algún tipo de recursividad (incluso en Oracle) y suele resultar un tanto engorroso.

Pero resulta que hay otro método. Aunque es un tanto extraño, ya que necesita utilizar Teradata XML Services, y más específicamente la función XMLAGG.

Esta función agrega múltiples filas para construir un valor XML (devuelve un XMLTYPE). Pero lo interesante es que podemos utilizarla para algo que no tiene que ver con XML: convertir filas en cadenas.

Veamos cómo:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM PRUEBA08;


 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  C_TXT
-----------  -------------------------
          3  TRES
          1  UNO
          4  CUATRO
          2  DOS


 BTEQ -- Enter your SQL request or BTEQ command:
SELECT XMLAGG(C_TXT ORDER BY ID_N) FROM CARLOS.PRUEBA08;


 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

XMLAGG(C_TXT ORDER BY ID_N ASC RETURNING SEQUENCE)
--------------------------------------------------
UNO DOS TRES CUATRO

¿Así de fácil? Casi. Hay que tener en cuenta que XMLAGG se comporta como una función de agregación (sí, como SUM(), COUNT(), MAX()…):

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N,XMLAGG(C_TXT ORDER BY ID_N) FROM CARLOS.PRUEBA08;

 *** Failure 3504 Selected non-aggregate values must be part of the associated group.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

Exacto:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N,XMLAGG(C_TXT ORDER BY ID_N) 
FROM CARLOS.PRUEBA08 GROUP BY 1;


 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

       ID_N XMLAGG(C_TXT ORDER BY ID_N ASC RETURNING SEQUENCE)
----------- --------------------------------------------------------------------------------
          3 TRES
          1 UNO
          4 CUATRO
          2 DOS

Teniendo eso claro, lo tenemos casi:

 BTEQ -- Enter your SQL request or BTEQ command:
INSERT INTO CARLOS.PRUEBA08 VALUES (1,'DOS');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:


INSERT INTO CARLOS.PRUEBA08 VALUES (1,'TRES');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.


 BTEQ -- Enter your SQL request or BTEQ command:


INSERT INTO CARLOS.PRUEBA08 VALUES (2,'TRES');


 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.
 
 BTEQ -- Enter your SQL request or BTEQ command:
SELECT * FROM CARLOS.PRUEBA08 ORDER BY 1;


 *** Query completed. 7 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  C_TXT
-----------  -------------------------
          1  UNO
          1  DOS
          1  TRES
          2  DOS
          2  TRES
          3  TRES
          4  CUATRO

Entonces:

  BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N, XMLAGG(C_TXT) 
FROM CARLOS.PRUEBA08 GROUP BY 1 ORDER BY 1;


 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

       ID_N XMLAGG(C_TXT RETURNING SEQUENCE)
----------- ----------------------------------------------------------
          1 UNO DOS TRES
          2 DOS TRES
          3 TRES
          4 CUATRO

Sólo queda un asunto menor: XMLAGG devuelve XML:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N,
       TYPE(XMLAGG(C_TXT)) 
  FROM CARLOS.PRUEBA08 GROUP BY 1 ORDER BY 1;


 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  Type(XMLAGG(C_TXT ORDER BY ID_N ASC RETURNING SEQUENCE))
-----------  --------------------------------------------------------
          1  SYSUDTLIB.XML
          2  SYSUDTLIB.XML
          3  SYSUDTLIB.XML
          4  SYSUDTLIB.XML

Por lo que un ‘cast‘ le pone la guinda al pastel:

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT ID_N, 
       XMLAGG(C_TXT) (VARCHAR(64)) 
  FROM CARLOS.PRUEBA08 GROUP BY 1 ORDER BY 1;


 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

       ID_N  XMLAGG(C_TXT RETURNING SEQUENCE)
-----------  ----------------------------------------------------------------
          1  UNO DOS TRES
          2  DOS TRES
          3  TRES
          4  CUATRO

Y si en vez de un espacio queremos un separador específico (‘|’, ‘;’, ‘,’…), es un juego de niños implementarlo.

Saludos.

Carlos.


Dos de tres…

18 mayo \18\UTC 2015

Este año,  dos de tres.  Aunque esa tercera es la que más escuece.

image

Saludos.

Carlos.


Se ha muerto BB King

15 mayo \15\UTC 2015

Se ha muerto BB King, Lucille se queda viuda…

Saludos.

Carlos.


Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

Únete a otros 62 seguidores