Ir al contenido principal

Firebird 2.1 List Function

The List function in Firebird 2.1.

This is an aggregate function that returns a string with the field values concatenated using a delimiter. the default delimiter is the comma. So it's similar to the SUM function, but instead of adding numbers, this function concatenate strings. if some of the values is null then the function returns null.

So suppose I have 2 tables, projects and resources, and I know that a project will have a few resources assigned. Now I need to write a report of projects showing their resources assigned.

CREATE TABLE projects(
project_id INTEGER,
name string
);

CREATE TABLE resources(
project_id integer,
user_name string
);



Without the LIST function I have to write this query

SELECT
p.project_id as id,
p.name,
r.user_name
FROM
projects p INNER JOIN
resources r ON p.project_id = r.project_id

my result will be like this

id name user_name
-------------------------
1 Project_1 Steve
1 Project_1 John
2 Project_2 Jim
2 Project_2 Ed

Then in the report I will need to group the records with the same project_id so I don't show duplicate projects for each resource assigned.

Now with Firebird 2.1 I can write the query using the LIST function

SELECT
p.project_id AS id,
p.name,
LIST(r.user_name, ', ') AS resources
FROM
projects p INNER JOIN
resources r ON p.project_id = r.project_id
GROUP BY
p.project_id,
p.name


my result will be like this

id name resources
-----------------------------
1 Project_1 Steve, John
2 Project_2 Jim, Ed


then in my report I only need to show the results of my query, I don't need to create groups.

The list function returns a BLOB data type, so if I need to display the result in a grid I often CAST the result to varchar. The delimiter parameter is not required (I added because I like to add a space after the comma). You can see more details of how to use the LIST function in the Firebird 2.1 Release Notes

This function is also useful in reports when you need details values in the same row as the master data like in reports headers or footers.

Comentarios

  1. Hola, viendo tus ejemplos y en especial el último query. ¿es posible que se pueda ordenar los datos de la tercer columna?
    Es decir que ordene la columna user_name algo asi:

    id name resources
    -----------------------------
    1 Project_1 John, Steve
    2 Project_2 Ed, Jim

    Gracias!

    Recien encontré tu blog y me parecen interesantes los temas que tratas. Saludos!

    ResponderBorrar
  2. Hola Iván,
    No lo he intentando, pero basado en esta respuesta de Dmitry Yemanov, me parece que no es posible.

    Saludos

    ResponderBorrar
  3. Gracias por tu respuesta, creo que efectivamente no se puede. Saludos!

    ResponderBorrar
  4. Busque la forma de realizar el orden del list y pude realizarlo de esta manera

    with VT_list as (
    select p.project_id,r.user_name
    from Projects p
    INNER JOIN resources r ON p.project_id = r.project_id
    order by r.user_name
    )
    SELECT p.project_id AS id, p.name,LIST(r.user_name, ', ') AS Resources
    FROM Projects p
    INNER JOIN VT_lista r ON p.project_id = r.project_id
    GROUP BY p.project_id,p.name

    Posiblemente le pueda ayudar a alguien mas que este buscando lo mismo

    ResponderBorrar
  5. Sabes cómo se podría hacer lo mismo pero con la versión 2.0 de Firebird? Ya que aquella versión no cuenta con esta función agregada LIST.

    De antemano muchas gracias!

    ResponderBorrar
  6. Jessrom, disculpa no te haya contestado antes. Tenía un poco abandonado el blog. Volviendo a la pregunta: no se me ocurre una forma concreta de conseguir el mismo resultado que la función List.
    Quizás usaría un Stored Procedure y un For Select e ir concatenado en una variable los valores.

    ResponderBorrar

Publicar un comentario

Entradas más populares de este blog

Bloqueos

Una de las preguntas típicas de las juntas matutinas en los equipos de desarrollo de software es ¿Hay algún bloqueo? Si lo hay, se trata de ver qué es lo que está esperando esa persona y encontrar la forma de que se desbloquee; pero ¿Qué son los bloqueos? Los bloqueos son obstáculos que te impiden realizar o avanzar en tu trabajo. Evitan que puedas seguir progresando en el proyecto.

He notado que es común en las personas con menos experiencia decir que tienen un bloqueo cuando están batallando, debido a su poca experiencia, en la forma de resolver un problema. Han intentado varias formas y se empiezan a quedar sin ideas de como puede ser resuelto el problema o como pueden cumplir con el requerimiento especificado. Al quedarse sin opciones de qué intentar dicen que tienen un bloqueo con la tarea y que a menos que alguien les diga como resolverlo, no se puede avanzar en la tarea.

En personas con más experiencia, ese tipo de bloqueos no ocurren, una persona con experiencia ha visto pro…

Firebird 2.1 UPDATE OR INSERT

Another great feature that I like in Firebird 2.1 is the UPDATE OR INSERT statement. It's a really time saver and it makes the SQL cleaner.

For example suppose I have a products table like the one I use in my last post and an inventory table to store the product stock. Before Firebird 2.1 if I want to set the stock for a product I needed to check if a record for that product_id already exists; if the product_id already exists then I write an update. If not then I write an insert statement. So I ended up with something like this:


IF EXISTS(SELECT * FROM inventory WHERE product_id = :product_id ) THEN
UPDATE
inventory
SET
stock = :stock
WHERE
product_id = :product_id;
ELSE
INSERT INTO inventory
(product_id, stock)
VALUES
(:product_id, :stock);

In this example I only update one field but when I have to update a big table I ended up with a big chunk of code and thinking: "there should be another (better) way to do this".

Fortunately now with Firebird 2.1 there…

Database Mail en MS SQL Server 2005

Configuración de Database Mail en MS SQL Server 2005

Primero se debe de habilitar, ya que por omisión el componente esta deshabilitado, Utilizando el SSMS (SQL Server Management Studio)


Si no esta habilitado aparecerá un mensaje preguntado si lo habilita, después aparece esta ventana donde se pregunta al usuario que es lo desea hacer.


Seleccionamos la primera opción para crear un perfil.


Configuramos el perfil y le agregamos por lo menos una cuenta.


Seleccionamos el perfil como public y default.


Para mandar correo se utiliza el procedimiento msdb.sp_send_dbmail por lo tanto el usuario que intente mandar correo debe de tener permiso para la base de datos msdb.

Referencias:
http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp