/* Copyright © Oracle Corporation 1995.  All Rights Reserved. */
/* ABSTRACT:
  This sample precompiled C program lists the employees assigned to 
  each department defined in the multischema sample corporate_data 
  database. 
*/

#include <stdio.h>
#include <sql_rdb_headers.h>

main()
{
/* Declare return status variable for error handling. */

    int   SQLCODE;

/* Declare module to specify SQL standard and quoting rules. */

    EXEC SQL DECLARE MODULE SQL_MODULE
        DIALECT SQL92
        QUOTING RULES SQL92;
    
/* Variables for program use. */
    char  emp_id[6], 
          emp_last_name[21],
          emp_first_name[21],
          dept_name[21],
          dept_code[5],
          dept_man_id[6];
    
/* Declare the corporate_data database. */

EXEC SQL DECLARE ALIAS FILENAME corporate_data;

/* Declare the cursor for the DEPARTMENTS table. */

EXEC SQL DECLARE DEPT_CURSOR TABLE CURSOR        
         FOR SELECT
             DEPARTMENT_CODE, DEPARTMENT_NAME, MANAGER_ID FROM               
             "RDB$DBHANDLE.ADMINISTRATION".ACCOUNTING.DEPARTMENTS
         ORDER BY DEPARTMENT_CODE;

/* Declare the cursor for the EMPLOYEES table. */

EXEC SQL DECLARE EMP_CURSOR TABLE CURSOR
         FOR SELECT
             E.EMPLOYEE_ID, E.LAST_NAME,E.FIRST_NAME FROM
             "RDB$DBHANDLE.ADMINISTRATION".PERSONNEL.EMPLOYEES E,
             "RDB$DBHANDLE.ADMINISTRATION".PERSONNEL.JOB_HISTORY JH
         WHERE E.EMPLOYEE_ID = JH.EMPLOYEE_ID
             AND JH.DEPARTMENT_CODE = :dept_code
            AND JH.JOB_END IS NULL
         ORDER BY E.LAST_NAME;

/* Print the report title. */

   printf("%20s DEPARTMENT EMPLOYEE LISTING\n\n"," ");

   /* Open the DEPARTMENTS cursor. */

      EXEC SQL OPEN DEPT_CURSOR;

   while (1)
   {

    /* Fetch the department data. */

      EXEC SQL FETCH DEPT_CURSOR INTO
                :dept_code,
                :dept_name,
                :dept_man_id;

      if (SQLCODE == 100) 
          break;
      else
          if (SQLCODE != 0)
              sql_signal();

      /* Print the department data and employee headings. */

      printf("\n");
      printf("Department Code: %s\n",dept_code);
      printf("Department Name: %s\n",dept_name);
      printf("Dept Manager Id: %s\n\n",dept_man_id);
      printf("Employee Id %s Last Name %15s First Name \n"," "," ");

      /* Open the EMPLOYEES cursor. */

         EXEC SQL OPEN EMP_CURSOR;

      while (1)

      {
          /* Fetch the employee data. */

             EXEC SQL FETCH EMP_CURSOR INTO 
		:emp_id,
                :emp_last_name,
                :emp_first_name;

          if (SQLCODE == 100)
              break;
          else
              if (SQLCODE != 0)
                  sql_signal();

          /* Print employees data. */

          printf("%6s %28s %25s \n", emp_id,emp_last_name,emp_first_name);

      } /* End employees while loop. */

        /* Close the EMPLOYEES cursor. */ 

           EXEC SQL CLOSE EMP_CURSOR;

   }/* End departments while loop. */
        
   /* Close the DEPARTMENTS cursor. */    

      EXEC SQL CLOSE DEPT_CURSOR;

} /* End main program. */