Ya hemos visto en este artículo:

Agregando filas adicionales

como podemos hacer para agregar filas al resultado de salida de nuestra consulta. Para ello escribimos un stored procedure seleccionable.

Ahora, veremos otra técnica para obtener lo que deseamos, usando una construcción del Firebird llamada CTE (Common Table Expresion).

En nuestro ejemplo, la salida tiene 18 filas. ¿Por qué? porque hay 2 filas para Países, 4 filas para Estados y 12 filas para Ciudades. En total, 18 filas.

Y esas son exactamente las filas que tiene cada una de las tablas.

Eso nos trae a la mente que podemos usar el comando UNION para unir el conjunto resultado de la tabla PAÍSES con el conjunto resultado de la tabla ESTADOS y con el conjunto resultado de la tabla CIUDADES.

Listado 1.

WITH MiUnion AS (

   SELECT
      PAI_IDENTI * 1000000 AS CIU_ORDENX,
      PAI_NOMBRE           AS CIU_NOMPAI,
      ''                   AS CIU_NOMEST,
      ''                   AS CIU_NOMBRE
   FROM
      PAISES

   UNION

   SELECT
      EST_IDEPAI * 1000000 + EST_IDENTI * 1000 AS CIU_ORDENX,
      ''                                       AS CIU_NOMPAI,
      EST_NOMBRE                               AS CIU_NOMEST,
      ''                                       AS CIU_NOMBRE
   FROM
      ESTADOS

   UNION

   SELECT
      CIU_IDEPAI * 1000000 + CIU_IDEEST * 1000 + CIU_IDENTI AS CIU_ORDENX,
      ''                                                    AS CIU_NOMPAI,
      ''                                                    AS CIU_NOMEST,
      CIU_NOMBRE
   FROM
      CIUDADES

)

SELECT
   *
FROM
   MiUnion
ORDER BY
   CIU_ORDENX

Y este es el resultado que obtenemos al ejecutar el Listado 1.

CIUDADES10

 

Captura 1. Si haces clic en la imagen la verás más grande

Desde luego que la columna CIU_ORDENX no es necesario mostrarla a los usuarios, se muestra en la Captura 1. para que sea más fácil entender la técnica utilizada.

Explicación:

El comando UNION nos permite agregar al resultado de una consulta el resultado de otra consulta, obteniendo así un conjunto resultado que es la suma de los anteriores.

Como nuestras tablas tienen 2 filas, 4 filas y 12 filas, al hacer las UNIONes entre ellas el resultado final tendrá sí o sí, 18 filas.

La tabla MiUnion es una tabla virtual, una tabla que solamente existe en la memoria de la computadora, o sea, es una tabla CTE.

Como MiUnion es una tabla virtual puede tener todas las columnas que se nos ocurra, inclusive podemos inventarle columnas, tal como hicimos con la columna CIU_ORDENX.

La columna CIU_ORDENX utilizamos para guardar en ella el orden en el cual deseamos que las filas sean mostradas. Aquí obtenemos sus valores en potencias de 1.000 porque suponemos que las cantidades de Países, de Estados y de Ciudades serán menos que 1.000 y que sus identificadores respectivos también siempre serán menores que 1.000. Si cualquiera de ellos pueden ser más que 1.000 entonces tendríamos que usar potencias de 10.000, de 100.000, etc.

CIU_ORDENX para un País es: Identificador_del_País * 1.000 * 1.000

CIU_ORDENX para un Estado es: Identificador_del_País * 1.000 * 1.000 + Identificador_del_Estado * 1.000

CIU_ORDENX para una Ciudad es: Identificador_del_País * 1.000 * 1.000 + Identificador_del_Estado * 1.000 + Identificador_de_la_Ciudad

Esto implica que CIU_ORDENX para un País siempre terminará con 6 ceros, para un Estado siempre terminará con 3 ceros y para una Ciudad siempre terminará entre 001 y 999.

También implica que la cantidad de Países no puede ser mayor que 999, que la cantidad de Estados no puede ser mayor que 999 y que la cantidad de Ciudades no puede ser mayor que 999. Si cualquiera de ellos pudiera ser mayor que 999 entonces en lugar de usar potencias de 1.000 habría que usar potencias de 10.000, de 100.000, etc.

Luego de haber creado a la tabla virtual MiUnion lo único que nos resta es mostrarla, ordenada según la columna virtual CIU_ORDENX, y así obtenemos el resultado deseado.

Artículos relacionados:

Usando CTE (Common Table Expresion)

Agregando filas adicionales

El índice del blog Firebird21

El foro del blog Firebird21