Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 28 Aug 2002 @ 14:10:19 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: How to Declare Cursor
 
From:   Anomy Anom

<-- Anonymously Posted: Wednesday, August 28, 2002 10:03 -- >

Please see the sample code below

       IDENTIFICATION DIVISION.
       PROGRAM-ID. TERCOB04.
       AUTHOR.

       ENVIRONMENT DIVISION.

       DATA DIVISION.

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

       77  DISPLAY-SWITCH    PIC X          VALUE 'D'.

       01  ACTIVITY-COUNT    PIC -(15)9.
       01  LOGON-STRING.
           49 LOG-LENGTH     PIC S9(4) COMP VALUE 35.
           49 LOG-DESC       PIC X(35)      VALUE
                            '1/ VHRP,VHRP100'.

       01  ERROR-MSG.
           49 FILLER         PIC S9(4) COMP.
           49 ERROR-TXT      PIC X(80).

       01  ERROR-CODE        PIC S9(9) COMP.
       01  MAX-LENGTH        PIC S9(4) COMP VALUE +80.
       01  OUT-CODE          PIC -(15)9.

       01  REQUEST-TYPE      PIC X(8).

       01  AD-RECORD.
           05 AD-WEEK-ID     PIC S9(9) COMP.
           05 AD-TYPE-CD     PIC X(02).

       01  AD-WEEK-ID-OUT    PIC -(15)9.
       01  AD-TYPE-CD-OUT    PIC -(15)9.

       01  AD-TYPE-CD-IND    PIC S9(4) COMP.
           EXEC SQL INCLUDE SQLCA END-EXEC.

      ***************************************************************
      ***** DECLARE CURSOR
      ***************************************************************

           EXEC SQL
                    DECLARE ADCUR  CURSOR FOR
                    SELECT AD_WEEK_ID,
                           AD_TYPE_CD
                      FROM DEVDB02.AD
           END-EXEC.

      ***************************************************************
       PROCEDURE DIVISION.
      ***************************************************************

           DISPLAY 'EXECUTING TERLOGON'
           DISPLAY ' '

      ***************************************************************
      ***** LOGON
      ***************************************************************

           MOVE 'LOGON' TO REQUEST-TYPE

           MOVE LENGTH OF LOG-DESC  TO LOG-LENGTH
           DISPLAY 'LOGON-STRING = ' LOGON-STRING

           EXEC SQL
                    LOGON :LOGON-STRING
           END-EXEC

           PERFORM ERROR-CHECK

           IF (SQLCODE NOT = 0)
               GOBACK
           ELSE
               DISPLAY 'LOGON SUCCESSFUL'
           END-IF

      ***************************************************************
      ***** OPEN CURSOR
      ***************************************************************

           MOVE 'OPEN' TO REQUEST-TYPE

           EXEC SQL
                    OPEN ADCUR
           END-EXEC

           PERFORM ERROR-CHECK

           IF  SQLCODE EQUAL ZEROS
               PERFORM FETCH-ADCUR UNTIL SQLCODE = 100
           END-IF

      ***************************************************************
      ***** CLOSE CURSOR
      ***************************************************************

           MOVE 'CLOSE' TO REQUEST-TYPE

           EXEC SQL
                    CLOSE ADCUR
           END-EXEC

           PERFORM ERROR-CHECK

      ***************************************************************
      ***** LOGOFF
      ***************************************************************

           MOVE 'LOGOFF' TO REQUEST-TYPE

           EXEC SQL
                    LOGOFF
           END-EXEC

           PERFORM ERROR-CHECK

           GOBACK.

       FETCH-ADCUR.
           MOVE 'FETCH' TO REQUEST-TYPE

           EXEC SQL
                    FETCH ADCUR
                      INTO :AD-WEEK-ID,
                           :AD-TYPE-CD INDICATOR :AD-TYPE-CD-IND
           END-EXEC

           PERFORM ERROR-CHECK.

           IF (SQLCODE = 0) AND (DISPLAY-SWITCH = 'D')
               MOVE ' ' TO DISPLAY-SWITCH
               MOVE SQLERRD(3) TO ACTIVITY-COUNT
               DISPLAY ' '
               DISPLAY ACTIVITY-COUNT, ' ROWS SELECTED'
           END-IF

           IF (SQLCODE = 0)
               DISPLAY ' '
               DISPLAY 'AD-WEEK-ID        : ', AD-WEEK-ID
               DISPLAY 'AD-TYPE-CD        : ', AD-TYPE-CD
           END-IF.

      ***************************************************************
      ***** ERROR CHECK
      ***************************************************************

       ERROR-CHECK.
           IF (SQLCODE NOT = 0)
               DISPLAY ' SQLCODE = ' SQLCODE
               MOVE SPACES TO ERROR-TXT
               CALL 'PPRTEXT' USING SQL-RDTRTCON,
                                    ERROR-CODE,
                                    ERROR-MSG,
                                    MAX-LENGTH
               MOVE ERROR-CODE TO OUT-CODE
               DISPLAY ' '
               DISPLAY 'ERROR/WARNING DETECTED IN ', REQUEST-TYPE
               DISPLAY '   CODE: ', OUT-CODE
               DISPLAY '   MSG : ', ERROR-TXT
           END-IF.


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023