IDENTIFICATION DIVISION.
       PROGRAM-ID. CBLSQL01.
       AUTHOR. RAFAGOMEZ.
      *
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SOURCE-COMPUTER. IBM-AS400.
       OBJECT-COMPUTER. IBM-AS400.
       SPECIAL-NAMES. DECIMAL-POINT IS COMMA.


       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
      *
       DATA DIVISION.
       FILE SECTION.

      *------------------------
       WORKING-STORAGE SECTION.
      *------------------------



      *
      *
           exec sql
               include sqlca
           end-exec.

       01 W-VARIABLES.
           05 W-PAIS   PIC X(3) VALUE "ARG".
           05 W-CANTAN PIC X(20).
           05 W-RESULT PIC X(150).
      *
       01 W-TABLA.
           05 W-VECT PIC A(50) OCCURS 10 TIMES.
      *
      *------------------
       PROCEDURE DIVISION.
      *------------------

           exec sql
               set option commit = *NONE
           end-exec

      *-----------------
      * CREAMOS LA TABLA
      *-----------------
           exec sql
            CREATE OR REPLACE TABLE GARAS181.CANTANTES(
            CODIGO NUMERIC(7, 0) GENERATED ALWAYS AS IDENTITY
            PRIMARY KEY, PAIS CHARACTER(03) NOT NULL WITH DEFAULT,
            NOMBRE CHARACTER(50) NOT NULL WITH DEFAULT)
            RCDFMT REGI
           end-exec

      *----------------------
      * ADICIONAMOS REGISTROS
      *----------------------
      *    exec sql
      *        INSERT INTO GARAS181.CANTANTES
      *        (PAIS, NOMBRE) VALUES("ENG", "Rod Stewart")
      *    end-exec.
      *    exec sql
      *        INSERT INTO GARAS181.CANTANTES
      *        (PAIS, NOMBRE) VALUES("ARG", "Soda Stereo")
      *    end-exec.
      *    exec sql
      *        INSERT INTO GARAS181.CANTANTES
      *        (PAIS, NOMBRE) VALUES("BRA", "ParalamasDS")
      *    end-exec.
      *    exec sql
      *    UPDATE GARAS181.CANTANTES SET NOMBRE = "Cuchi Leguizamon",
      *       PAIS = "ARG"  WHERE CODIGO = 0000008
      *    end-exec.
      *    exec sql
      *    UPDATE GARAS181.CANTANTES SET NOMBRE = "Madonna",
      *       PAIS = "USA"  WHERE CODIGO = 0000007
      *    end-exec.
      *
      *
      *
           exec sql
               WHENEVER SQLERROR GO TO MOSTRAR-ERROR
           end-exec.

      *
      *PARA INSERTAR VARIABLES EN EL SELECT DEBEN IR CON : DELANTE
      *
           exec sql
               SELECT COUNT(*) INTO :W-RESULT
                         FROM GARAS181.CANTANTES
                         WHERE PAIS = :W-PAIS
           end-exec.
           DISPLAY "RESULTADO DE LA BUSQUEDA: " W-RESULT.
           DISPLAY "PARA EL PAIS: " W-PAIS.
      *
      *
      *
      *
      *EL FETCH FIRST 1 SIGNIFICA QUE DEL RESULTADO MUESTRE
      *SOLO LA PRIMERA FILA
      *FETCH NEXT 1 O 2 O 3 ROW'S' ONLY
      *    exec sql
      *        SELECT NOMBRE INTO :W-RESULT
      *                  FROM GARAS181.CANTANTES
      *          WHERE PAIS = :W-PAIS FETCH FIRST 1 ROW ONLY
      *    end-exec.
      *    DISPLAY "RESULTADO DE LA BUSQUEDA: " W-RESULT.
      *
      *********************************************************
      *CUANDO UN SELECT TIRA VARIOS REGISTROS COMO RESULTADO
      *PUEDO IR PASANDOLOS 1 A 1 DE LA SIGUIENTE MANERA
      *
            exec sql
                select NOMBRE INTO :W-RESULT FROM (
                    SELECT codigo, pais, nombre, ROW_NUMBER() OVER
                    (ORDER BY NOMBRE) as FILA
                    FROM GARAS181.CANTANTES WHERE PAIS = "ARG")
                WHERE FILA = 2
            end-exec.
                DISPLAY "EL NUMERO 2 ES " W-RESULT
      *
      *
      *
           STOP RUN.
      *
      *
      *
       MOSTRAR-ERROR.
           DISPLAY "Error de SQL.".
           EXIT.
       F-MOSTRAR-ERROR.