SQL AVANZADO (III). USO DE LA EXPRESION CASE EN SENTENCIAS UPDATE INFORMIX - Advanced Informix Sql (III). Using the CASE expression in a UPDATE sentence

En entradas anteriores hemos visto el uso de la función CASE en una SELECT, pero su uso puede ser muy útil también en una sentencia UPDATE.
Por ejemplo, imaginemos que queremos aumentar el precio de nuestros artículos en un porcentaje distinto según la sección a la que pertenezca el artículo.

Habitualmente haríamos un UPDATE por cada seccion asignando el precio correcto a sus artículos.
UPDATE articulos
SET precio = precio * 1.05
WHERE seccion = 1;

UPDATE articulos
SET precio = precio * 1.08
WHERE seccion = 2;

UPDATE articulos
SET precio = precio * 1.10
WHERE seccion = 3;

 Dependiendo del número de filas que tenga la tabla de artículos, podemos conseguir un importante ahorro de tiempo realizando todas las operaciones en una única sentencia:
UPDATE articulos
SET precio =
CASE
WHEN seccion = 1 THEN precio*1.05
WHEN seccion = 2 THEN precio*1.08
WHEN seccion = 3 THEN precio*1.10
END

 Tenemos que tener en cuenta que al menos debe haber una condicion WHEN y que ésta puede ser múltiple, es decir, podemos condicionar la actualización a varias condiciones.
Por ejemplo:
UPDATE articulos
SET precio =
CASE
WHEN seccion = 1 AND precio >= 100 THEN precio*1.05
WHEN seccion = 1 AND precio <100 THEN precio*1.08
END

 Vemos que actualizamos los artículos de la seccion 1 pero con distinto factor según su precio actual.
Espero que os sirva de utilidad.

Hasta la próxima.

SQL AVANZADO (II). USO DE LA FUNCION WEEKDAY EN SENTENCIAS SQL INFORMIX - Advanced Informix Sql (II). Using the weekday function in Sql

Si periodicamente os toca realizar un informe y para obtenerlo os basais en alguna query de esas maravillosas que guardais como si fueran de oro, es conveniente que incluyais la fecha del dia en que lo lanzais para poder hacer un seguimiento......

La fecha del dia puede incluirse con un simple TODAY, pero si queremos darle un formato mas 'amigable' podemos utilizar la funcion WEEKDAY.

Esta función nos devuelve el dia de la semana en que nos encontramos con un número cuyo significado va desde el 0 (Domingo) hasta el 6 (Sábado)

Si lo combinamos con la expresión CASE que vimos en otra entrada de este mismo blog, podremos obtener resultados interesantes:
SELECT CASE WEEKDAY(today)
    WHEN 0 THEN 'Domingo'
    WHEN 1 THEN 'Lunes'
    WHEN 2 THEN 'Martes'
    WHEN 3 THEN 'Miercoles'
    WHEN 4 THEN 'Jueves'
    WHEN 5 THEN 'Viernes'
    WHEN 6 THEN 'Sabado'
END || ', '|| day(today)|| ' de '||
CASE MONTH(today)
    WHEN 1 THEN 'Enero'
    WHEN 2 THEN 'Febrero'
    WHEN 3 THEN 'Marzo'
    WHEN 4 THEN 'Abril'
    WHEN 5 THEN 'Mayo'
    WHEN 6 THEN 'Junio'
    WHEN 7 THEN 'Julio'
    WHEN 8 THEN 'Agosto'
    WHEN 9 THEN 'Septiembre'
    WHEN 10 THEN 'Octubre'
    WHEN 11 THEN 'Noviembre'
    WHEN 12 THEN 'Diciembre'
END || ' de '|| YEAR(today) FECHA,
COUNT(*) PEDIDOS,
SUM(importe) IMPORTE
FROM pedidos

Obtendremos una salida como esta:

Estoy de acuerdo en que el ejemplo es un poco simple pero quiero que os quedeis con la idea de que con esta función combinada con la expresión CASE podeis obtener una salida interesante para una fecha.
Ni que decir tiene que igual que la he utilizado con TODAY podeis utilizarla con cualquier fecha incluyendo columnas de tipo DATE de vuestra base de datos.

Espero que os sirva de utilidad.
Hasta la próxima.

SQL AVANZADO (I). USO DE LA EXPRESION CASE EN SENTENCIAS SQL INFORMIX - Advanced Informix Sql (I). Using the CASE expression in Sql

Supongo que algún jefe impaciente os habrá pedido alguna vez un informe de forma urgente (a mi me pasó hace tiempo...).
Por SQL es posible obtener información muy valiosa pero muchas veces el formato en el que se almacena la información no es del todo inteligible para la mayoría de los mortales y os habrá tocado 'maquillarlo' antes de entregarlo con el trabajo que eso conlleva.

Bueno, pues el uso de la expresión CASE en una sentencia SQL me ha servido para librarme de algunas de esas labores tan tediosas.

Imaginemos la típica tabla de pedidos en la que tenemos una columna que indica la situación del mismo con estos valores:

• 10-Recibido
• 20-En fabricación
• 30-Terminado
• 40-Enviado
• 50-Entregado

En un informe normal obtenido por SQL unicamente podriamos obtener el código de la situación y tendríamos que explicar su significado.

Sin embargo si utilizamos la expresión CASE podemos obtener algo como esto:

SELECT 
id_pedido PEDIDO,
CASE situacion
  WHEN 10 THEN "RECIBIDO"
  WHEN 20 THEN "EN FABRICACION"
  WHEN 30 THEN "TERMINADO"
  WHEN 40 THEN "ENVIADO"
  WHEN 50 THEN "ENTREGADO"
  ELSE "DESCONOCIDO"
END SITUACION,
fecha_pedido FECHA,
cliente CLIENTE
FROM PEDIDOS



Ya sé que el ejemplo es algo sencillo pero estoy seguro de que le vereis la forma de sacarle partido.
Lo que no estoy seguro es de si estas expresiones son estandares de SQL y se pueden utilizar con otros gestores de Bases de Datos.

Eso ya me lo comentais vosotros...

CONOCER LAS CONSTRAINTS FOREIGN KEY DE UNA TABLA - Knowing the foreign keys of a Informix table by Sql

Si queremos conocer las constraints de tipo Foreign Key que apuntan a una determinada tabla por SQL, podemos utilizar esta query en la que sólo debeis sustituir "my_table_name" por el nombre de la tabla que querais inspeccionar.


SELECT d.tabname primaria, i.colname columna,
       a.tabname secundaria, f.colname columna,
       b.constrname constraint, b.owner propietario, b.constrtype tipo,
       CASE c.delrule
          WHEN 'C' then 'SI'
          WHEN 'R' then 'NO'
       END cascade_delete
  FROM systables a,
       sysconstraints b,
       sysreferences c,
       systables d,
       sysindexes e,
       syscolumns f,
       sysconstraints g,
       sysindexes h,
       syscolumns i
 WHERE d.tabname = "my_table_name"
   AND c.ptabid = d.tabid
   AND b.constrid = c.constrid
   AND g.constrid = c.primary
   AND b.constrtype = 'R'
   AND a.tabid = b.tabid
   AND e.idxname = b.idxname -- Indice tabla secundaria
   AND h.idxname = g.idxname -- Indice tabla primaria
   AND i.tabid = g.tabid -- Columnas tabla primaria
   AND f.tabid = b.tabid -- Columnas tabla secundaria
   AND ((f.colno = e.part1 AND i.colno = h.part1) or
        (f.colno = e.part2 AND i.colno = h.part2) or
        (f.colno = e.part3 AND i.colno = h.part3) or
        (f.colno = e.part4 AND i.colno = h.part4) or
        (f.colno = e.part5 AND i.colno = h.part5) or
        (f.colno = e.part6 AND i.colno = h.part6) or
        (f.colno = e.part7 AND i.colno = h.part7) or
        (f.colno = e.part8 AND i.colno = h.part8) or
        (f.colno = e.part9 AND i.colno = h.part9) or
        (f.colno = e.part10 AND i.colno = h.part10) or
        (f.colno = e.part11 AND i.colno = h.part11) or
        (f.colno = e.part12 AND i.colno = h.part12) or
        (f.colno = e.part13 AND i.colno = h.part13) or
        (f.colno = e.part14 AND i.colno = h.part14) or
        (f.colno = e.part15 AND i.colno = h.part15) or
        (f.colno = e.part16 AND i.colno = h.part16))

Los tipos de constrains reflejados en la columna constrtype de la tabla sysconstraints pueden ser:

• C Constraint de tipo Check
• P Constraint Primary Key
• R Constraint Reference (Foreign Key)
• U Constraint Unique
• N Constraint NOT NULL 

Espero que os sea de utilidad.

CONOCER LAS TABLAS QUE MAS ESPACIO OCUPAN EN UN DBSPACE DESDE SQL - Knowing the size of a Informix table in a Dbspace by Sql

TABLAS DE UN DBSPACE

Si queremos ir equilibrando la ocupacion de los dbspaces que componen nuestra base de datos, es interesante tener una herramienta que nos permita conocer qué tablas son las que mas espacio ocupan en un determinado dbspace para ver si nos conviene pasarlas a otro.

Desde sql ejecutamos la instrucción:

DATABASE sysmaster;
SELECT 
   t2.name dbspace,
   t1.tabname table,
   t1.owner owner,
   TRUNC(t3.nrows,0) rows,
   TRUNC(t3.rowsize,0) row_size,
   TRUNC(t3.nrows*t3.rowsize/1024/1024,2) MB
FROM systabnames t1,sysdbspaces t2, segeua:systables t3
WHERE t2.dbsnum=trunc(t1.partnum/1048576)
  AND t2.name='dbspace_01'
  AND t1.tabname = t3.tabname
ORDER BY 6 DESC


Obtendremos una lista como esta:

DBSPACE DE UNA TABLA

Con una pequeña modificación de la query anterior podemos conocer el dbspace en el que se encuentra una determinada tabla:

DATABASE sysmaster;
SELECT
    t2.name dbspace,
    t1.tabname table,
    t1.owner owner,
    TRUNC(t3.nrows,0) rows,
    TRUNC(t3.rowsize,0) rowsize,
    TRUNC(t3.nrows*t3.rowsize/1024/1024,2) MB
FROM systabnames t1,sysdbspaces t2, segeua:systables t3
WHERE t2.dbsnum=trunc(t1.partnum/1048576)
  AND t1.tabname='tabla_01'
  AND t1.tabname = t3.tabname


A mi me han sido de utilidad y continuan siendolo.
Hasta la próxima.

CONOCER LA OCUPACION DE DBSPACES EN INFORMIX POR SQL - Knowing the free space of a Informix Dbspace by SQL

Aunque actualmente existen herramientas como OAT, que nos permiten visualizar graficamente la ocupación de los Dbspaces definidos en nuestra base de datos Informix, es conveniente poder obtener esta información por SQL por si queremos incluir está información en un registro para hacer un seguimiento detallado.

Para obtener información de nuestros Dbspaces, asi como de los Chunks que los forman debemos recurrir a dos tablas de la Base de datos sysmaster:

• sysdbspaces
• syschunks

La forma de calcular el espacio que ocupan, asi como el espacio libre varía en función de si es un dbspace  normal, o es un blobspace o es un sbspace.

Lo he resuelto haciendo tres querys unidas por la clausula UNION:

DATABASE sysmaster;
--
-- Dbspace 'normal'
--
SELECT
d.name[1,10] Name,
d.nchunks Chunks,
SUM(k.chksize*2/1024) Total,
SUM(k.nfree*2/1024) Free,
100-(SUM(k.nfree*2/1024)*100)/(SUM(k.chksize*2/1024)) Perc
FROM sysdbspaces d, syschunks k
WHERE d.dbsnum=k.dbsnum
AND d.is_blobspace=0
AND d.is_sbspace=0
GROUP BY 1,2

UNION
--
-- Blobspaces
--
SELECT
d.NAME[1,10] Name,
d.nchunks Chunks,
SUM(k.chksize*2/1024) Total,
SUM(k.nfree*8/1024)Free,
100-(SUM(k.nfree*8/1024)*100)/(SUM(k.chksize*2/1024)) Perc
FROM sysdbspaces d, syschunks k
WHERE d.dbsnum=k.dbsnum
AND d.is_blobspace=1
GROUP BY 1,2

UNION
--
-- Sbspaces
--
SELECT
d.NAME[1,10] Name,
d.nchunks Chunks,
SUM(k.chksize*2/1024) Total,
SUM(k.udfree*2/1024) Free,
100-(SUM(k.udfree*2/1024)*100)/(SUM(k.chksize*2/1024)) Perc
FROM sysdbspaces d, syschunks k
WHERE d.dbsnum=k.dbsnum
AND d.is_sbspace=1
GROUP BY 1,2
ORDER BY 1


El resultado es una tabla como esta:

Hasta la próxima.