Categoría:Informix

Selección que usa otra Instancia/BD diferente a la actual[editar]

La sintaxis para referenciar tablas de BBDD externas es de la forma: bd@instancia:tabla Como en el ejemplo siguiente:

   SELECT COUNT(*)
   FROM
       registro r,
       bdext@instanc:registro r2
   WHERE
       r.id=r2.id
       AND (r.dni <> r2.dni OR r.fechact <> r2.fechact)

Diferencia de registros de la misma tabla en dos entornos diferentes:

   SELECT FIRST 1
       (SELECT COUNT(*) FROM latabla)-(SELECT COUNT(*) FROM mibd@instanc:latabla)
   FROM systables

Sub-consultas utilizando sub-conjuntos[editar]

   SELECT
       dni
   FROM
       TABLE( MULTISET (
                           SELECT
                               r.dni, c.nropasapor
                           FROM
                               registro r,
                           OUTER
                               personal c
                           WHERE
                               r.dni IS NOT NULL
                               AND r.dni[10,11] <> '00'
                               AND r.dni=c.dni
       ))
   WHERE
       nropasapor IS NULL

OUTER JOIN[editar]

Funciona como el LEFT JOIN pero posee mucho mejor rendimiento que éste sobre todo para cantidades grandes de datos, por lo que se aconseja encarecidamente el uso de OUTERs en lugar de LEFT JOINs.

   SELECT c.customer_num, c.lname, o.order_num,
        order_date, call_dtime
      FROM customer c, OUTER orders o, OUTER cust_calls x
      WHERE c.customer_num = o.customer_num
         AND c.customer_num = x.customer_num
      ORDER BY lname
      INTO TEMP service

Más sobre OUTERs en la web de IBM.

Formateando / Adaptando el resultado de una consulta[editar]

Breve resumen de funciones utilísimas para ajustar o convertir los datos según se necesite:

NVL: evitando los valores nulos[editar]

La consulta siguiente me devolverá la cadena '//sin especificar//' para los casos en que el valor del campo calle valga NULL. Para el resto de casos devolverá el valor del campo tal cual.

   SELECT
       NVL(calle, 'sin especificar') AS calle
   FROM
       personal

DECODE: devolver valor determinado segun el caso[editar]

La siguiente //query// devuelve el nombre del mes a partir de un tipo DATE.

   SELECT
       DECODE( MONTH(p.unafecha),1,"Enero",2,"Febrero",3,"Marzo",4,"Abril",5,"Mayo",6,"Junio",7,"Julio",
                                8,"Agosto",9,"Septiembre",10,"Octubre",11,"Noviembre","Diciembre") as nomes
   FROM
       latabla

Más sobre DECODE en la web de IBM.

CASE: decisiones con lógica más compleja[editar]

Existen dos maneras de utilizar CASE. La primera forma:

   SELECT
       cust_name,
       CASE
       WHEN number_of_problems = 0
         THEN 100
       WHEN number_of_problems > 0 AND number_of_problems < 4
         THEN number_of_problems * 500
       WHEN number_of_problems >= 4 and number_of_problems <= 9
         THEN number_of_problems * 400
       ELSE
         (number_of_problems * 300) + 250
       END as cust_problems,
      cust_address
   FROM
       custtab

La segunda forma de usar CASE es como sigue:

   SELECT
       title,
       CASE movie_type
           WHEN 1 THEN 'HORROR'
           WHEN 2 THEN 'COMEDY'
           WHEN 3 THEN 'ROMANCE'
           WHEN 4 THEN 'WESTERN'
           ELSE 'UNCLASSIFIED'
       END,
       our_cost
   FROM
       movie_titles

Más sobre CASE en la web de IBM.

Escribiendo en las tablas[editar]

Inserción simple[editar]

   INSERT INTO proceso (tipo, descripcion)
   VALUES ('02', 'ESTANDAR TIPO B') ;

Inserción desde otra tabla[editar]

   INSERT INTO envios
           (proceso, dni, nropasapor, sexo, lote,   correo, calle, codpostal, fecha, envio, estado)
   SELECT   proceso, dni, nropasapor, sexo, '0001', ,     calle, codpostal, TODAY, 1,     '001'
   FROM
       personal
   WHERE NOT EXISTS(
           SELECT * FROM envios WHERE proceso=personal.proceso AND dni=personal.dni
       )
       AND proceso='P123' AND municipio='2500'

Actualización simple[editar]

   UPDATE
       proceso
   SET
       descripcion='CRUCES COMPAÑIA SEGURSO'
   WHERE
       tipo='01' ;

Actualización de valores desde otras tablas[editar]

Desde una tabla solo:

   UPDATE
       registro
   SET
       (registro.fechact, registro.fechacrea) = (
           (   SELECT fechact, fechacrea
               FROM bdext@instanc:registro
               WHERE id=registro.id
           )
       )

Desde varias tablas:

   UPDATE
       registro
   SET
       registro.dni = (SELECT dni FROM tabla1 WHERE id=registro.id),
       registro.fechact = (SELECT fechact FROM otra WHERE id=registro.id)
   WHERE
       registro.dni IS NULL

Modificar Estructura / Campos de una Tabla[editar]

Agregar una nueva columna a una tabla[editar]

   ALTER TABLE envios
       ADD fecha DATE DEFAULT TODAY NOT NULL ;

Para insertar la nueva columna en una posición u orden determinado antes de un campo ya existente usar BEFORE :

   ALTER TABLE items
      ADD (item_weight DECIMAL(6,2) NOT NULL BEFORE total_price)

Modificar un campo existente[editar]

   ALTER TABLE apartados
       MODIFY ( nombre VARCHAR(100) NOT NULL ) ;

Eliminar un campo de una tabla[editar]

   ALTER TABLE latabla DROP fecha ;

Renombrar una tabla[editar]

   RENAME TABLE nombreant TO nuevonom ;

Renombrar un campo[editar]

   RENAME COLUMN latabla.campoant TO camponuevo ;

Copias de seguridad (solo desde DB–Access)[editar]

Descargar el contenido de una tabla a un fichero de texto:

   UNLOAD TO personal.txt SELECT * FROM personal ;

Restaurar el contenido de una tabla desde un fichero de texto con LOAD :

   LOAD FROM personal.txt INSERT INTO personal ;

BLOBs[editar]

Volcar el contenido de un campo BLOB a un fichero físico en disco:

   SELECT LOTOFILE (documento, 'c:\tmp\00137100', 'client')
   FROM nw_repositorio
   WHERE cddni= '00137100'

Para copiar valores/ficheros LOB de un campo a otro se usa LOCOPY.

Para insertar ficheros en campos LOB de una tabla se utiliza la función FILETOBLOB.

Triggers[editar]

Ejemplo de disparador que registra la fecha, hora y usuario de ultima modificación de cualquier campo de una tabla concreta. Suponiendo que dicha tabla contendrá los siguientes campos para contener la susodicha información de //Data Warehousing//:

   fechact    DATE
   usuario    VARCHAR(30)

Y definiendo un procedimiento auxiliar que se limite simplemente a devolvernos dichos datos tal y como sigue:

   CREATE PROCEDURE pr_current_user() RETURNING DATETIME YEAR TO SECOND, VARCHAR(30)
      RETURN CURRENT, USER;
   END PROCEDURE;

Entonces la definición del trigger de registro de dichos campos quedaría:

   CREATE TRIGGER tu_tabla
   UPDATE ON tabla
   FOR EACH ROW (
       EXECUTE PROCEDURE pr_current_user() INTO tabla.fechact, tabla.usuario
   )

Para ejecutar más de una instrucción por cada fila implicada en el trigger se separan éstas por comas:

   CREATE TRIGGER trig_tabusers INSERT ON tabusers
   REFERENCING NEW AS new_ins
      FOR EACH ROW (
          EXECUTE PROCEDURE pr_crear_cuenta(new_ins.login, new_ins.nombre, new_ins.apellidos),
   
          INSERT INTO auditoria( tabla, operacion, clave, detalles )
          VALUES ( 'tabusers', 'I', new_ins.login, new_ins.nombre||' - '||new_ins.apellidos )
      )

Esta categoría no contiene ninguna página o archivo.