Example Using SQL Statements

In the following example, the program prompts for the name of a department, obtains the names and phone numbers of all members of that department from the EMPLOYEE table, and presents that information on the screen.
/******************************************************/
/* Exec to list names and phone numbers by department */
/******************************************************/

/*--------------------------------------------------------------*/
/* Get the department number to be used in the select statement */
/*--------------------------------------------------------------*/
   Say 'Enter a department number'
   Pull dept

/*--------------------------------------------------------------*/
/* Retrieve all rows from the EMPLOYEE table for the department */
/*--------------------------------------------------------------*/
   "EXECSQL SELECT LASTNAME, PHONENO FROM EMPLOYEE ",
                   "WHERE WORKDEPT = '"dept"'"
   If rc <> 0 then
     do
       Say ' '
       Say 'Error accessing EMPLOYEE table'
       Say 'RC      =' rc
       Say 'SQLCODE =' SQLCODE
       Exit rc
     end

/*---------------------------------------*/
/* Display the members of the department */
/*---------------------------------------*/
   Say 'Here are the members of Department' dept
   Do n = 1 to lastname.0
     Say lastname.n phoneno.n
   End

   Exit


rvse633.html | Timestamp icon Last updated: Monday, 5 June 2017