Expresión de Tabla Común o CTE en SQL

Una expresión de tabla común o CTE (common table expression) se puede considerar como un conjunto de resultados temporales, que se definen en el ámbito de la ejecución de una instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW.

Una CTE es como una tabla temporal que sólo dura el tiempo que dura la ejecución. Una CTE puede hacer referencia a sí misma, y se puede hacer referencia a ella varias veces en la misma consulta.

La estructura básica de una CTE es:

WITH nombre_consulta [(columna [,…n])] AS

(

   definición_consulta_CTE

)

La lista de las columnas resultantes ES opcional sólo si se proporcionan nombres distintivos en la definición de la consulta.

Vamos a ver toda esta teoría en la práctica. Vamos a partir de la BBDD queya hemos definido para el ejemplo de la agenda.

Suponed que queremos seleccionar un número determinado de registros, como por ejemplo los 2 primeros. Desgraciadamente en SQL/400 no tenemos la sentencia LIMIT para limitar el número de registros a mostrar, como ocurre en MySQL.

Una solución podría ser generar una tabla temporal que contuviera el número de registro (ojo, número de registro no número relativo). Una vez tuviéramos esa tabla, realizaríamos una selección para aquellos registros cuyo número de registro sea inferior o igual a 2.

Pues bien, la tabla temporal la crearemos con una CTE y el resultado lo obtendremos realizando una consulta sobre esa CTE.

Definiremos la CTE de la siguiente forma:

WITH R1 AS (                                      

     SELECT NOMBRE, APELLIDOS, DIRECCION, TELEFONO,

            ROWNUMBER() OVER() AS NUMREG          

     FROM   BJOSEM/FAGENDAP                       

)                                                 

SELECT * FROM R1 WHERE NUMREG <= 2                

En la CTE seleccionamos los campos del fichero FAGENDAP, además de su número de registro. Este número lo obtenemos con la OLAP ROWNUMBER, que nos devuelve el número de registro según el orden establecido en OVER(). Como no hemos establecido orden alguno, contará según vayan saliendo los resultados.

Una vez tenemos la CTE, realizamos una consulta para seleccionar los registros de la CTE cuyo número sea igual o inferior a 2.

Si pudiéramos “depurar” la consulta, veríamos que la ejecución inicial de la CTE sería la siguiente:

Una vez tenemos la consulta generada, con la condición de que NUMREG <= 2 seleccionados sólo 2 registros.

Esto es un ejemplo simple de una CTE, pero las CTE pueden ayudarnos a simplificar mucho nuestras consultas.

Espero les haya servido y no olviden visitar cobol-400.com

Un saludo.

Creación de un Subfile - Cobol

Anteriormente hemos creado las DDS tanto de las tablas que almacenarán los datos como de la pantalla que mostrará el subfichero.

Ahora nos adentraremos en la parte del programa Cobol que controlará dicho subfichero.

A grandes rasgos, el programa realizará dos pasos principales:

1. Cargar el subfichero.
2. Mostrar el subfichero y gestionar las teclas de función.

Para la carga del subfichero nos leeremos de principio a fin el fichero lógico FAGENDA1 para que salgan ordenados por nombre y apellidos. Por cada registro leído, escribiremos uno en el subfichero.

Una vez hayamos terminado la lectura del fichero de datos, el subfichero quedará cargado, por lo que lo único que tendremos que hacer es mostrar por pantalla dicho subfichero y gestionar las teclas que se pulsan.

Así que manos a la obra. El fuente quedaría de la siguiente forma (después del fuente comentamos los diferentes accesos):

0001.00        PROCESS APOST.                                                 

0002.00       *************************                                       

0003.00        IDENTIFICATION DIVISION.                                        

0004.00       *************************                                       

0005.00        PROGRAM-ID. AGENDA.                                            

0006.00        AUTHOR.   TODO-AS400.BLOGSPOT.COM.ES                                     

0007.00        SECURITY. FECHA DE CREACION     : MAYO/2012                    

0008.00                  ULTIMA MODIFICACION   : 00/00/0000                   

0009.00                                                                       

0010.00       *****************************************************************

0011.00       *|     D E S C R I P C I Ó N    D E L    P R O G R A M A        |

0012.00       *****************************************************************

0013.00       *|    SUBFICHERO DE CARGA TOTAL SIMPLE V 1.0                    |

0014.00       *|    @@@@@   TODO-AS400.BLOGSPOT.COM.ES @@@@@                  |

0015.00       *****************************************************************

0016.00                                                                        

0017.00       **********************                                          

0018.00        ENVIRONMENT DIVISION.                                          

0019.00       **********************                                           

0020.00           CONFIGURATION SECTION.                                      

0021.00           SOURCE-COMPUTER. AS-400.                                

0022.00           OBJECT-COMPUTER. AS-400.                                 

0023.00           SPECIAL-NAMES.   DECIMAL-POINT IS COMMA                 

0024.00                            LOCAL-DATA    IS LDA                   

0025.00                            REQUESTOR     IS CONSOLA.              

0026.00       *                                                            

0027.00        INPUT-OUTPUT SECTION.                                      

0028.00       *------------------------------------------------------------

0029.00           FILE-CONTROL.                                            

0030.00       *------------------------------------------------------------

0031.00            SELECT TAGENDA   ASSIGN        WORKSTATION-TAGENDA-SI  

0032.00                             ORGANIZATION  TRANSACTION             

0033.00                             ACCESS        DYNAMIC                 

0034.00                             CONTROL-AREA  AREA-TECLAS             

0035.00                             RELATIVE KEY  NRR                     

0036.00                             FILE STATUS   FS-FILE.                

0037.00                                                                   

0038.00            SELECT FAGENDA1  ASSIGN        DATABASE-FAGENDA1       

0039.00                             ORGANIZATION  INDEXED                 

0040.00                             ACCESS        DYNAMIC                 

0041.00                             RECORD KEY    EXTERNALLY-DESCRIBED-KEY

0042.00                             FILE STATUS   FS-FILE.

0043.00                                                    

0044.00       ***************                             

0045.00        DATA DIVISION.                             

0046.00       ***************                             

0047.00       *--------------------------------------------

0048.00        FILE SECTION.                              

0049.00       *--------------------------------------------

0050.00       *--------------------------------------------

0051.00        FD  TAGENDA.                                

0052.00        01  R-TAGENDA PIC X(1024).                 

0053.00       *--------------------------------------------

0054.00        FD  FAGENDA1.                              

0055.00        01  R-FAGENDA1.                            

0056.00            COPY DDS-ALL-FORMAT OF FAGENDA1.       

0057.00       *--------------------------------------------

0058.00        WORKING-STORAGE SECTION.                   

0059.00       *--------------------------------------------

0060.00       *--------------------------------------------

0061.00        01  VARIABLES-GENERALES.                   

0062.00            05  FS-FILE             PIC X(02).     

0063.00            05  NUERR               PIC 9(03).

0064.00            05  NRR                 PIC 9(04).

0065.00            05  W-CICLO             PIC 9(02).

0066.00                                             

0067.00       *---- SWITCHES ------------------------

0068.00        01  SW-FAGENDA1   PIC X.             

0069.00            88 FIN-FAGENDA1    VALUE 'S'.    

0070.00            88 NO-FIN-FAGENDA1 VALUE 'N'.    

0071.00                                             

0072.00       *######################################

0073.00       *-----------  REGISTROS DE PANTALLA Y L

0074.00       *######################################

0075.00        01  W-SFL01-O.                       

0076.00            COPY DDS-SFL01-O     OF  TAGENDA.

0077.00                                             

0078.00        01  AREA-TECLAS.                     

0079.00            05  FILLER PIC XX.               

0080.00                88  INTRO    VALUE '00'.     

0081.00                88  F03      VALUE '03'.     

0082.00                                             

0083.00        01  INDICADORES.                      

0084.00            05 TAB-IN OCCURS 99                  PIC  1 INDICATOR 1.   

0085.00               88 INDON                          VALUE B'1'.           

0086.00               88 INDOFF                         VALUE B'0'.           

0087.00                                                                        

0088.00       *################################################################

0089.00       *------------------ ACCESO A OTROS PROGRAMAS --------------------

0090.00       *################################################################

0091.00                                                                       

0092.00       *################################################################

0093.00       *------------------ TABLAS AUXILIARES ---------------------------

0094.00       *################################################################

0095.00                                                                       

0096.00       *================================================================

0097.00        LINKAGE SECTION.                                               

0098.00       *================================================================

0099.00                                                                        

0100.00       ***********************************************************     

0101.00        PROCEDURE DIVISION.                                            

0102.00       ***********************************************************     

0103.00                                                                        

0104.00       *****************************************************************

0105.00       *              E S T R U C T U R A    - A G E N D A -    

0106.00       **********************************************************

0107.00       *-----------------------                                 

0108.00        0000-ESTRUCTURA-AGENDA.                                 

0109.00       *-----------------------                                  

0110.00            PERFORM 1000-INICIO-AGENDA                          

0111.00            PERFORM 2000-PROCESO-AGENDA UNTIL W-CICLO = 99      

0112.00            PERFORM 3000-FIN-AGENDA.                            

0113.00                                                                 

0114.00       **********************************************************

0115.00       ********************* PROCESOS PRINCIPALES ***************

0116.00       **********************************************************

0117.00       *------------------                                      

0118.00        1000-INICIO-AGENDA.                                     

0119.00       *------------------                                      

0120.00            INITIALIZE VARIABLES-GENERALES                      

0121.00            MOVE          001     TO   NUERR                    

0122.00                                                                

0123.00            OPEN I-O    TAGENDA                                 

0124.00                 INPUT  FAGENDA1                                

0125.00                                                                

0126.00            MOVE 1 TO W-CICLO              

0127.00            .                              

0128.00                                           

0129.00       *-------------------                

0130.00        2000-PROCESO-AGENDA.               

0131.00       *-------------------                

0132.00            EVALUATE W-CICLO               

0133.00               WHEN 1                      

0134.00                  PERFORM 2000-CARGAR-SFL01

0135.00               WHEN 2                      

0136.00                  PERFORM 2000-MOSTRAR-SFL01

0137.00               WHEN OTHER                   

0138.00                  MOVE 99 TO W-CICLO       

0139.00            END-EVALUATE                   

0140.00            .                              

0141.00                                           

0142.00       *---------------                     

0143.00        3000-FIN-AGENDA.                   

0144.00       *---------------                    

0145.00            MOVE    002      TO   NUERR    

0146.00            CLOSE  TAGENDA FAGENDA1        

0147.00            GOBACK.                                                     

0148.00                                                                       

0149.00       *****************************************************************

0150.00       ********************* PROCESOS DE CICLO 1 ***********************

0151.00       *****************************************************************

0152.00       *------------------                                             

0153.00        2000-CARGAR-SFL01.                                              

0154.00       *------------------                                             

0155.00            MOVE ZEROES TO NRR                                         

0156.00            PERFORM 2000-LIMPIAR-CTL01                                 

0157.00            PERFORM 9000-LEERN-FAGENDA1                                

0158.00            PERFORM UNTIL FIN-FAGENDA1                                 

0159.00               ADD 1 TO NRR                                            

0160.00                                                                        

0161.00               INITIALIZE W-SFL01-O                                    

0162.00               MOVE NOMBRE    OF FAGENDA1 TO SFL01NOM OF W-SFL01-O     

0163.00               MOVE APELLIDOS OF FAGENDA1 TO SFL01APE OF W-SFL01-O     

0164.00               MOVE TELEFONO  OF FAGENDA1 TO SFL01TEL OF W-SFL01-O     

0165.00                                                                       

0166.00               PERFORM 9000-ESCRIBIR-SFL01                              

0167.00               PERFORM 9000-LEERN-FAGENDA1                             

0168.00            END-PERFORM                                                

0169.00                                                                        

0170.00            IF NRR > 0                                                 

0171.00               SET INDON(90) TO TRUE                                   

0172.00            ELSE                                                       

0173.00               SET INDOFF(90) TO TRUE                                  

0174.00            END-IF                                                     

0175.00                                                                       

0176.00            MOVE 2 TO W-CICLO                                          

0177.00            .                                                          

0178.00                                                                       

0179.00       *-------------------                                             

0180.00        2000-LIMPIAR-CTL01.                                            

0181.00       *-------------------                                            

0182.00            SET INDOFF(90) TO TRUE                                      

0183.00            PERFORM 9000-ESCRIBIR-CTL01                                

0184.00            SET INDON(90)  TO TRUE                                     

0185.00            .                                                           

0186.00                                                                       

0187.00       *****************************************************************

0188.00       ********************* PROCESOS DE CICLO 2 ***********************

0189.00       ***********************************************************

0190.00       *------------------                                       

0191.00        2000-MOSTRAR-SFL01.                                      

0192.00       *------------------                                        

0193.00            PERFORM 9000-ESCRIBIR-PIE01                          

0194.00            PERFORM 9000-ESCRIBIR-CTL01                          

0195.00            PERFORM 9000-LEER-CTL01                              

0196.00            PERFORM 2000-EVALUAR-TECLAS-CTL01                    

0197.00            .                                                    

0198.00                                                                 

0199.00       *--------------------------                               

0200.00        2000-EVALUAR-TECLAS-CTL01.                               

0201.00       *--------------------------                               

0202.00            IF F03                                                

0203.00               MOVE 99 TO W-CICLO                                

0204.00            END-IF                                               

0205.00            .                                                    

0206.00                                                                  

0207.00       ***********************************************************

0208.00       *  Rutinas 9000 de Acceso a Ficheros                      

0209.00       ***********************************************************

0210.00       *--------------------                            

0211.00        9000-ESCRIBIR-PIE01.                            

0212.00       *--------------------                            

0213.00            MOVE  901 TO NUERR                           

0214.00            WRITE R-TAGENDA                             

0215.00                           FORMAT     'PIE01'           

0216.00                           INDICATORS INDICADORES       

0217.00            END-WRITE                                    

0218.00            .                                           

0219.00                                                        

0220.00       *-------------------                             

0221.00        9000-LEERN-FAGENDA1.                             

0222.00       *-------------------                             

0223.00            MOVE 902 TO NUERR                           

0224.00            READ FAGENDA1  NEXT                         

0225.00                 AT END      SET FIN-FAGENDA1     TO TRUE

0226.00                 NOT AT END  SET NO-FIN-FAGENDA1  TO TRUE

0227.00            END-READ                                    

0228.00            .                                           

0229.00                                                         

0230.00       *--------------------                            

0231.00        9000-ESCRIBIR-SFL01.                             

0232.00       *--------------------                             

0233.00            MOVE  903 TO NUERR                           

0234.00            WRITE SUBFILE R-TAGENDA  FROM W-SFL01-O      

0235.00                                    FORMAT 'SFL01'       

0236.00                                    INDICATORS INDICADORES

0237.00            END-WRITE                                    

0238.00            .                                            

0239.00                                                         

0240.00       *--------------------                             

0241.00        9000-ESCRIBIR-CTL01.                             

0242.00       *--------------------                             

0243.00            MOVE  904 TO NUERR                           

0244.00            WRITE R-TAGENDA                              

0245.00                            FORMAT     'CTL01'           

0246.00                            INDICATORS INDICADORES       

0247.00            END-WRITE                                    

0248.00            .                                            

0249.00                                                         

0250.00       *----------------                                 

0251.00        9000-LEER-CTL01.                                 

0252.00       *----------------                            

0253.00            MOVE  905 TO NUERR                     

0254.00            READ  TAGENDA                          

0255.00                           FORMAT     'CTL01'      

0256.00                           INDICATORS INDICADORES  

0257.00            END-READ                               

0258.00            .                                      

De las líneas 27 a 56 declaramos los ficheros a usar. La pantalla TAGENDA y el fichero FAGENDA1, así como sus FD (File Description).

Dentro de la Working declaramos las variables y switches que vamos a utilizar. Dentro de la definición de los campos de pantalla, declaramos los campos que tendrá el subfichero (realizamos un COPY DDS para no tener que meter los campos a mano). A su vez declaramos las teclas de función que se utilizarán y la tabla con los indicadores de pantalla.

En la línea 108 iniciamos el programa en sí. Declaramos la estructura del programa, que será un inicio, un cuerpo y un fin.

En el inicio abriremos los ficheros. El cuerpo estará embuclado hasta que la variable W-CICLO valga 99. Luego estará el fin, que será donde cerremos ficheros y finalicemos el programa.

Dentro de 2000-PROCESO-AGENDA lo que haremos sera evaluar el valor de la variable W-CICLO. Como podéis ver, aquí definimos los dos procesos de los que hablamos anteriormente. Por un lado la carga del subfichero y por otro mostrar dicho subfichero. Cada parte será un ciclo de ejecución.

En la carga del subfichero (párrafo 2000-CARGAR-SFL01) inicialmente borraremos el subfichero. Para ello desactivaremos el indicador 90, que es el que controla el SLFCLR. Una vez limpiado, nos recorremos el fichero FAGENDA1 de principio a fin, y por cada registro escribimos uno en el subfichero.

Tras finalizar la lectura, verificaremos el valor de NRR. Si vale 0 (no hay registros en el subfichero), desactivaremos el indicador 90 para que no se muestre el subfichero (es el indicador que controla el DSPSFL). En cambio, si NRR > 0, activaremos el indicador 90 para que posteriormente se muestre el subfichero.

Una vez realizado este primer ciclo, nos vamos al siguiente ciclo, que será el que muestre el subfichero. Escribiremos el formato del pie, el formato de control y leeremos el control para ver los datos. Tras leer la pantalla, controlaremos qué teclas se han pulsado (si se pulsa F3 finalizará el programa moviendo el valor 99 a W-CICLO).

Como podéis ver, un subfichero que sólo muestra información es algo muy simple. Teniendo clara la filosofía de su programación (carga del subfile y mostrado del mismo) la programación se hace tremendamente simple.

Para ver cómo funciona el subfichero, cargaremos con datos el fichero:

INSERT INTO FAGENDAP (NOMBRE, APELLIDOS, DIRECCION,      

TELEFONO, FILLER) VALUES                                        

('PEPITO', 'GOMEZ LOPEZ', 'CALLE SU CASITA 3', '666888777', ' '),

('DIEGO', 'SERRANO JAEN', 'AVENIDA GRANDE  8', '111222333', ' '),

('JUAN', 'PEREZ JIMENEZ', 'CALLE LORCA S/N', '236854795', ' ')  

Y ejecutaremos el programa: 

CALL AGENDA

La ejecución del mismo:

En post posteriores iremos complicando aún más este ejemplo, añadiendo una ventana para dar de alta registros, añadiendo más columnas, opción de búsqueda dentro del subfichero, etc…

Y recordad, no olvidéis visitar nuestra web cobol-400.com.

Un saludo.