/******************************************************************************
**
** Source File Name = joinsql.sqc
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000 
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
**    PURPOSE : an example of using advanced SQL statements.
**              The advanced SQL statement to be executed and displayed is:
**         WITH
**         DEPT_MGR AS
**            ( SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO
**               FROM DEPARTMENT D, EMPLOYEE E
**               WHERE D.MGRNO=E.EMPNO AND E.JOB='MANAGER'
**            ),
**         DEPT_NO_MGR AS
**            ( SELECT DEPTNO, DEPTNAME, MGRNO AS EMPNO
**               FROM DEPARTMENT
**               EXCEPT ALL
**               SELECT DEPTNO, DEPTNAME, EMPNO
**               FROM DEPT_MGR
**            ),
**         MGR_NO_DEPT (DEPTNO, EMPNO, LASTNAME, FIRSTNME, PHONENO) AS
**            ( SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, PHONENO
**               FROM EMPLOYEE
**               WHERE JOB='MANAGER'
**               EXCEPT ALL
**               SELECT DEPTNO,EMPNO, LASTNAME, FIRSTNME, PHONENO
**               FROM DEPT_MGR
**            )
**         SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO
**            FROM DEPT_MGR
**         UNION ALL
**         SELECT DEPTNO, DEPTNAME, EMPNO,
**            CAST(NULL AS VARCHAR(15)) AS LASTNAME,
**            CAST(NULL AS VARCHAR(12)) AS FIRSTNME,
**            CAST(NULL AS CHAR(4)) AS PHONENO
**            FROM DEPT_NO_MGR
**         UNION ALL
**         SELECT DEPTNO,
**            CAST(NULL AS VARCHAR(29)) AS DEPTNAME,
**            EMPNO, LASTNAME, FIRSTNME, PHONENO
**            FROM MGR_NO_DEPT;
**
**    STRUCTURES USED :
**       sqlca
**       sqlda
**
**    APIs USED :
**
**    FUNCTIONS DECLARED :
**       'C' COMPILER LIBRARY :
**          stdio.h  -  printf
**          string.h -  strncpy
**
**       OTHER :
**          external : [in the file UTIL.C]
**             check_error : Checks for SQLCODE error, and prints out any
**                           related information available.
**
**    EXTERNAL DEPENDANCIES :
**       - Ensure existence of database for precompile purposes.
**       - Precompile with the SQL precompiler (PREP in DB2)
**       - Bind to a database (BIND in DB2)
**       - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
**         or the Microsoft Visual C++ compiler (Windows) 
**         or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
**   -  "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
**   -  "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <sqlenv.h>
#include <sqlda.h>
#include "utilemb.h"


EXEC SQL INCLUDE SQLCA ;


int main(int argc, char *argv[]) {

   int counter = 0 ;

   EXEC SQL BEGIN DECLARE SECTION ;
      char  userid[9] ;
      char  passwd[19] ;
      char  deptno[4] ;
      short deptno_ind ;
      char  deptname[31] ;
      short deptname_ind ;
      char  empno[7] ;
      short empno_ind ;
      char  lastname[16] ;
      short lastname_ind ;
      char  firstname[13] ;
      short firstname_ind ;
      char  phoneno[5] ;
      short phoneno_ind ;
   EXEC SQL END DECLARE SECTION ;


   printf( "\nSample C program:  JOINSQL\n\n" ) ;

   if (argc == 1) {
      EXEC SQL CONNECT TO sample;
      EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else if (argc == 3) { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;
      EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else {
      printf ("\nUSAGE: joinsql [userid passwd]\n\n");
      return 1;
   } /* endif */

   /* declare the cursor for the advanced SQL statement */
   EXEC SQL DECLARE pcurs CURSOR FOR
            WITH
              DEPT_MGR AS (
                SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO
                  FROM DEPARTMENT D, EMPLOYEE E
                  WHERE D.MGRNO=E.EMPNO AND E.JOB='MANAGER'
              ),
              DEPT_NO_MGR AS (
                SELECT DEPTNO, DEPTNAME, MGRNO AS EMPNO
                  FROM DEPARTMENT
                  EXCEPT ALL
                    SELECT DEPTNO, DEPTNAME, EMPNO
                      FROM DEPT_MGR
              ),
              MGR_NO_DEPT (DEPTNO, EMPNO, LASTNAME, FIRSTNME, PHONENO) AS (
                SELECT WORKDEPT, EMPNO, LASTNAME, FIRSTNME, PHONENO
                  FROM EMPLOYEE
                  WHERE JOB='MANAGER'
                  EXCEPT ALL
                    SELECT DEPTNO,EMPNO, LASTNAME, FIRSTNME, PHONENO
                      FROM DEPT_MGR
              )
              SELECT DEPTNO, DEPTNAME, EMPNO, LASTNAME, FIRSTNME, PHONENO
                FROM DEPT_MGR
                UNION ALL
                  SELECT DEPTNO,
                         DEPTNAME,
                         EMPNO,
                         CAST(NULL AS VARCHAR(15)) AS LASTNAME,
                         CAST(NULL AS VARCHAR(12)) AS FIRSTNME,
                         CAST(NULL AS CHAR(4)) AS PHONENO
                    FROM DEPT_NO_MGR ;

   EXEC SQL OPEN pcurs ;
   EMB_SQL_CHECK( "OPEN CURSOR" ) ;

   printf( "DEPTNO DEPTNAME                    EMPNO  LASTNAME        FIRSTNAME PHONENO\n" ) ;
   printf( "===========================================================================\n" ) ;

   /* FETCH the rows from the table corresponding to the SQL statement */

   while ( 1 ) {
      EXEC SQL FETCH pcurs INTO :deptno:deptno_ind,
                                :deptname:deptname_ind,
                                :empno:empno_ind,
                                :lastname:lastname_ind,
                                :firstname:firstname_ind,
                                :phoneno:phoneno_ind ;

      if ( deptno_ind )    *deptno    = '\0' ;
      if ( deptname_ind )  *deptname  = '\0' ;
      if ( empno_ind )     *empno     = '\0' ;
      if ( lastname_ind )  *lastname  = '\0' ;
      if ( firstname_ind ) *firstname = '\0' ;
      if ( phoneno_ind )   *phoneno   = '\0' ;

      if ( SQLCODE != 0 ) break ;
      printf( "%-3.3s %-30.30s %-6.6s %-15.15s %-12.12s %-4.4s\n",
              deptno,
              deptname,
              empno,
              lastname,
              firstname,
              phoneno
            ) ;

      counter++ ;

   } ;

   EXEC SQL CLOSE pcurs ;
   EMB_SQL_CHECK( "CLOSE CURSOR" ) ;
   printf( "\n  %d record(s) selected\n\n", counter ) ;

   EXEC SQL CONNECT RESET ;
   EMB_SQL_CHECK( "CONNECT RESET" ) ;

   return 0 ;

}                    /* end of program : joinsql.sqc */