/******************************************************************************
**
** 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 */