//***************************************************************************
// (c) Copyright IBM Corp. 2007 All rights reserved.
//
// The following sample of source code ("Sample") is owned by International
// Business Machines Corporation or one of its subsidiaries ("IBM") and is
// copyrighted and licensed, not sold. You may use, copy, modify, and
// distribute the Sample in any form without payment to IBM, for the purpose of
// assisting you in the development of your applications.
//
// The Sample code is provided to you on an "AS IS" basis, without warranty of
// any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
// IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
// MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
// not allow for the exclusion or limitation of implied warranties, so the above
// limitations or exclusions may not apply to you. IBM shall not be liable for
// any damages you suffer as a result of using, copying, modifying or
// distributing the Sample, even if IBM has been advised of the possibility of
// such damages.
//***************************************************************************
//
// SOURCE FILE NAME: TbRead.sqlj
//
// SAMPLE: How to read table data
//
// SQL Statements USED:
// SELECT
// FETCH
//
// Classes used from Util.sqlj are:
// Db
// Data
// SqljException
//
//
// Output will vary depending on the JDBC driver connectivity used.
//***************************************************************************
//
// For more information on the sample programs, see the README file.
//
// For information on developing Java applications see the Developing Java Applications book.
//
// For information on using SQL statements, see the SQL Reference.
//
// For the latest information on programming, compiling, and running DB2
// applications, visit the DB2 Information Center at
// http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
//**************************************************************************/
import java.lang.*;
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator Named_Iterator(String deptnumb, String deptname);
#sql iterator Positioned_Iterator(String, String);
#sql iterator TbRead_Cursor0(int, String, int, String, String);
#sql iterator TbRead_Cursor1(String, String, String, String, String);
#sql iterator TbRead_Cursor2(int, String);
#sql iterator TbRead_Cursor3(String, int);
#sql iterator TbRead_Cursor4(int, String, String, String);
#sql iterator TbRead_Cursor5(int, int, String, String);
#sql iterator TbRead_Cursor6(String, int, double);
#sql iterator TbRead_Cursor7(String, Integer, Double);
#sql iterator TbRead_Cursor8(float);
class TbRead
{
public static void main(String argv[])
{
try
{
Db db = new Db(argv);
System.out.println();
System.out.println("THIS SAMPLE SHOWS HOW TO READ TABLE DATA.");
// connect to the 'sample' database
db.getDefaultContext();
// different ways to read table data
selectUsingNamedBindingToColumns();
selectUsingPositionalBindingToColumns();
mostSimpleSubselect();
basicSubselect();
groupBySubselect();
subselect();
rowSubselect();
fullselect();
selectStatement();
basicSubselectFromMultipleTables();
basicSubselectFromJoinedTable();
basicSubselectUsingSubquery();
basicSubselectUsingCorrelatedSubquery();
subselectUsingGroupingSets();
subselectUsingRollup();
subselectUsingCube();
selectUsingQuerySampling();
// disconnect from the 'sample' database
db.disconnect();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // main
// helping function
static void orgTbContentDisplay()
{
try
{
int deptnumb = 0;
String deptname = "";
int manager = 0;
String division = "";
String location = "";
TbRead_Cursor0 cur0;
System.out.println();
System.out.println(" SELECT * FROM org");
System.out.println(
" DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION\n" +
" -------- -------------- ------- ---------- --------------");
// perform a SELECT against the "org" table in the sample database.
#sql cur0 = {SELECT * FROM org};
while (true)
{
// retrieve and display the result from the SELECT statement
#sql {FETCH :cur0
INTO :deptnumb, :deptname, :manager, :division, :location};
if (cur0.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 14) +
" " + Data.format(manager, 7) +
" " + Data.format(division, 10) +
" " + Data.format(location, 14));
}
// close the cursor
cur0.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // orgTbContentDisplay
// helping function
static void departmentTbContentDisplay()
{
try
{
int mgrnoInd = 0;
int departmentLocationInd = 0;
String deptno = "";
String departmentDeptname = "";
String mgrno = "";
String admrdept = "";
String departmentLocation = "";
TbRead_Cursor1 cur1;
System.out.println();
System.out.println(" SELECT * FROM department");
System.out.println(
" DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION\n" +
" ------ ---------------------------- ------ -------- --------");
#sql cur1 = {SELECT * FROM department};
while (true)
{
#sql {FETCH :cur1 INTO :deptno, :departmentDeptname, :mgrno,
:admrdept, :departmentLocation};
if (cur1.endFetch())
{
break;
}
System.out.print(" " + Data.format(deptno, 6) +
" " + Data.format(departmentDeptname, 28));
if (mgrno != null)
{
System.out.print(" " + Data.format(mgrno, 6));
}
else
{
System.out.print(" - ");
}
System.out.print(" " + Data.format(admrdept, 8));
if (departmentLocation != null)
{
System.out.print(" " + Data.format(departmentLocation, 16));
}
else
{
System.out.print(" - ");
}
System.out.println();
}
// close the cursor
cur1.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // departmentTbContentDisplay
// helping function
static void employeeTbPartialContentDisplay()
{
try
{
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT job, edlevel, comm\n" +
" FROM employee\n" +
" WHERE job IN('DESIGNER', 'FIELDREP')\n" +
"\n" +
" Results:\n" +
" JOB EDLEVEL COMM\n" +
" -------- ------- -----------");
int edlevel = 0;
double comm = 0.0;
String job = "";
TbRead_Cursor6 cur6;
// declare a cursor
#sql cur6 = {SELECT job, edlevel, comm
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP')};
while (true)
{
// fetch the cursor
#sql {FETCH :cur6 INTO :job, :edlevel, :comm};
if (cur6.endFetch())
{
break;
}
System.out.println(" " + Data.format(job, 8) +
" " + Data.format(edlevel, 7) +
" " + Data.format(comm, 10, 2));
}
// close the cursor
cur6.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // employeeTbPartialContentDisplay
static void selectUsingNamedBindingToColumns()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT\n" +
" SELECT\n" +
"AND THE 'NAMED' ITERATOR\n" +
"TO RETRIEVAL MULTI-ROW QUERY RESULTS.");
// display the content of the 'org' table
departmentTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptno as deptnumb, deptname\n" +
" FROM department\n" +
" WHERE admrdept = 'A00'\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
Named_Iterator namedIter = null;
// declare a cursor
#sql namedIter = {SELECT deptno as deptnumb, deptname
FROM department
WHERE admrdept = 'A00'};
while (namedIter.next())
{
System.out.println(" " + Data.format(namedIter.deptnumb(), 8) +
" " + Data.format(namedIter.deptname(), 14));
}
// close the cursor
namedIter.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // selectUsingNamedBindingToColumns
static void selectUsingPositionalBindingToColumns()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENT\n" +
" SELECT\n" +
"AND THE 'POSITIONED' ITERATOR\n" +
"TO RETRIEVAL MULTI-ROW QUERY RESULTS.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptno as deptnumb, deptname\n" +
" FROM department\n" +
" WHERE admrdept = 'A00'\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
Positioned_Iterator posIter;
String deptnumb = "";
String deptname = "";
// delcare cursor
#sql posIter = {SELECT deptno as deptnumb, deptname
FROM department
WHERE admrdept = 'A00'};
while (true)
{
// fetch the cursor
#sql {FETCH :posIter INTO :deptnumb, :deptname};
if (posIter.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 14));
}
// close the cursor
posIter.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // selectUsingPositionalBindingToColumns
static void mostSimpleSubselect()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, deptname FROM org\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
int deptnumb = 0;
String deptname = "";
TbRead_Cursor2 cur2;
// declare a cursor
#sql cur2 = {SELECT deptnumb, deptname FROM org};
while (true)
{
// fetch the cursor
#sql {FETCH :cur2 INTO :deptnumb, :deptname};
if (cur2.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 14));
}
// close the cursor
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // mostSimpleSubselect
static void basicSubselect()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT USING A WHERE CLAUSE.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, deptname FROM org WHERE deptnumb < 30\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
int deptnumb = 0;
String deptname = "";
TbRead_Cursor2 cur2;
// declare a cursor
#sql cur2 = {SELECT deptnumb, deptname FROM org WHERE deptnumb < 30};
while (true)
{
// fetch the cursor
#sql {FETCH :cur2 INTO :deptnumb, :deptname};
if (cur2.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 14));
}
// close the cursor
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // basicSubselect
static void groupBySubselect()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A 'GROUP BY' SUBSELECT.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT division, MAX(deptnumb) FROM org GROUP BY division\n" +
"\n" +
" Results:\n" +
" DIVISION MAX(DEPTNUMB)\n" +
" ---------- --------------");
int maxDeptnumb = 0;
String division = "";
TbRead_Cursor3 cur3;
// declare a cursor
#sql cur3 = {
SELECT division, MAX(deptnumb) FROM org GROUP BY division};
while (true)
{
// fetch the cursor
#sql {FETCH :cur3 INTO :division, :maxDeptnumb};
if (cur3.endFetch())
{
break;
}
System.out.println(" " + Data.format(division, 10) +
" " + Data.format(maxDeptnumb, 14));
}
// close the cursor
cur3.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // groupBySubselect
static void subselect()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT division, MAX(deptnumb)\n" +
" FROM org\n" +
" WHERE location NOT IN 'New York'\n" +
" GROUP BY division\n" +
" HAVING division LIKE '%%ern'\n" +
"\n" +
" Results:\n" +
" DIVISION MAX(DEPTNUMB)\n" +
" ---------- --------------");
int maxDeptnumb = 0;
String division = "";
TbRead_Cursor3 cur3;
// declare a cursor
#sql cur3 = {SELECT division, MAX(deptnumb)
FROM org
WHERE location NOT IN 'New York'
GROUP BY division
HAVING division LIKE '%ern'};
while (true)
{
// fetch the cursor
#sql {FETCH :cur3 INTO :division, :maxDeptnumb};
if (cur3.endFetch())
{
break;
}
System.out.println(" " + Data.format(division, 10) +
" " + Data.format(maxDeptnumb, 14));
}
// close the cursor
cur3.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // subselect
static void rowSubselect()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A 'ROW' SUBSELECT.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, deptname\n" +
" INTO :deptnumb, :deptname\n" +
" FROM org\n" +
" WHERE location = 'New York'\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
int deptnumb = 0;
String deptname = "";
TbRead_Cursor2 cur2;
// declare a cursor
#sql cur2 = {SELECT deptnumb, deptname
FROM org
WHERE location = 'New York'};
// fetch the cursor
#sql {FETCH :cur2 INTO :deptnumb, :deptname};
System.out.println(" " + Data.format(deptnumb,8) +
" " + Data.format(deptname,14));
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // rowSubselect
static void fullselect()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A FULLSELECT.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, deptname\n" +
" FROM org\n" +
" WHERE deptnumb < 20\n" +
" UNION\n" +
" VALUES(7, 'New Deptname')\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
int deptnumb = 0;
String deptname = "";
TbRead_Cursor2 cur2;
// declare a cursor
#sql cur2 = {SELECT deptnumb, deptname
FROM org
WHERE deptnumb < 20
UNION
VALUES(7, 'New Deptname')};
while (true)
{
// fetch the cursor
#sql {FETCH :cur2 INTO :deptnumb, :deptname};
if (cur2.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 14));
}
// close the cursor
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // fullselect
static void selectStatement()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SELECT USING ORDER BY.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, deptname\n" +
" FROM org\n" +
" WHERE deptnumb > 30\n" +
" ORDER BY deptname\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- ------------");
int deptnumb = 0;
String deptname = "";
TbRead_Cursor2 cur2;
// declare a cursor
#sql cur2 = {SELECT deptnumb, deptname
FROM org
WHERE deptnumb > 30
ORDER BY deptname};
while (true)
{
// fetch the cursor
#sql {FETCH :cur2 INTO :deptnumb, :deptname};
if (cur2.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 18));
}
// close the cursor
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // selectStatement
static void basicSubselectFromMultipleTables()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT FROM MULTIPLE TABLES.");
// display the content of the 'org' table
orgTbContentDisplay();
// display the content of the 'department' table
departmentTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, o.deptname, deptno, d.deptname\n" +
" FROM org o, department d\n" +
" WHERE deptnumb <= 15 AND deptno LIKE '%%11'\n" +
"\n" +
" Results:\n" +
" DEPTNUMB ORG.DEPTNAME DEPTNO DEPARTMENT.DEPTNAME\n"+
" -------- -------------- ------ -------------------");
int deptnumb = 0;
String deptno = "";
String orgDeptname = "";
String departmentDeptname = "";
TbRead_Cursor4 cur4;
// declare a cursor
#sql cur4 = {SELECT deptnumb, o.deptname, deptno, d.deptname
FROM org o, department d
WHERE deptnumb <= 15 AND deptno LIKE '%11'};
while (true)
{
// fetch the cursor
#sql {FETCH :cur4 INTO :deptnumb, :orgDeptname, :deptno,
:departmentDeptname};
if (cur4.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(orgDeptname, 14) +
" " + Data.format(deptno, 6) +
" " + Data.format(departmentDeptname, 14));
}
// close the cursor
cur4.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // basicSelectFromMultipleTables
static void basicSubselectFromJoinedTable()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT FROM JOINED TABLES.");
// display the content of the 'org' table
orgTbContentDisplay();
// display the content of the 'department' table
departmentTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, manager, deptno, mgrno\n" +
" FROM org\n" +
" INNER JOIN department\n" +
" ON manager = INTEGER(mgrno)\n" +
" WHERE deptnumb BETWEEN 20 AND 100\n" +
"\n" +
" Results:\n" +
" DEPTNUMB MANAGER DEPTNO MGRNO\n" +
" -------- ------- ------ ------");
int deptnumb = 0;
int manager = 0;
String deptno = "";
String mgrno = "";
TbRead_Cursor5 cur5;
// declare a cursor
#sql cur5 = {SELECT deptnumb, manager, deptno, mgrno
FROM org
INNER JOIN department
ON manager = INTEGER(mgrno)
WHERE deptnumb BETWEEN 20 AND 100};
while (true)
{
// fetch the cursor
#sql {FETCH :cur5 INTO :deptnumb, :manager, :deptno, :mgrno};
if (cur5.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(manager, 7) +
" " + Data.format(deptno, 5) +
" " + Data.format(mgrno, 6));
}
// close the cursor
cur5.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // basicSubselectFromJoinedTable
static void basicSubselectUsingSubquery()
{
try
{
int deptnumb = 0;
String deptname = "";
TbRead_Cursor2 cur2;
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT USING A SUBQUERY.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, deptname\n" +
" FROM org\n" +
" WHERE deptnumb < (SELECT AVG(deptnumb) FROM org)\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
// declare a cursor
#sql cur2 = {SELECT deptnumb, deptname
FROM org
WHERE deptnumb < (SELECT AVG(deptnumb) FROM org)};
while (true)
{
// fetch the cursor
#sql {FETCH :cur2 INTO :deptnumb, :deptname};
if (cur2.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 14));
}
// close the cursor
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // basicSubselectUsingSubquery
static void basicSubselectUsingCorrelatedSubquery()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT USING A CORRELATED SUBQUERY.");
// display the content of the 'org' table
orgTbContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT deptnumb, deptname\n" +
" FROM org o1\n" +
" WHERE deptnumb > (SELECT AVG(deptnumb)\n" +
" FROM org o2\n" +
" WHERE o2.division = o1.division)\n" +
"\n" +
" Results:\n" +
" DEPTNUMB DEPTNAME\n" +
" -------- --------------");
int deptnumb = 0;
String deptname = "";
TbRead_Cursor2 cur2;
// declare a cursor
#sql cur2 = {SELECT deptnumb, deptname
FROM org o1
WHERE deptnumb > (SELECT AVG(deptnumb)
FROM org o2
WHERE o2.division = o1.division)};
while (true)
{
// fetch the cursor
#sql {FETCH :cur2 INTO :deptnumb, :deptname};
if (cur2.endFetch())
{
break;
}
System.out.println(" " + Data.format(deptnumb, 8) +
" " + Data.format(deptname, 14));
}
// close the cursor
cur2.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // basicSubselectUsingCorrelatedSubquery
static void subselectUsingGroupingSets()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT USING GROUPING SETS.");
employeeTbPartialContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT job, edlevel, SUM(comm)\n" +
" FROM employee\n" +
" WHERE job IN('DESIGNER', 'FIELDREP')\n" +
" GROUP BY GROUPING SETS((job, edlevel), (job))\n" +
"\n" +
" Results:\n" +
" JOB EDLEVEL SUM(COMM)\n" +
" -------- ------- -----------");
Integer edlevel = new Integer(0);
Double commSum = new Double(0.0);
String job = null;
TbRead_Cursor7 cur7;
// declare a cursor
#sql cur7 = {SELECT job, edlevel, SUM(comm)
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP')
GROUP BY GROUPING SETS((job, edlevel), (job))};
while (true)
{
// fetch the cursor
#sql {FETCH :cur7 INTO :job, :edlevel, :commSum};
if (cur7.endFetch())
{
break;
}
if (job != null)
{
System.out.print(" " + Data.format(job, 8));
}
else
{
System.out.print(" -");
}
if (edlevel != null)
{
System.out.print(" " + Data.format(edlevel, 7));
}
else
{
System.out.print(" -");
}
if (commSum != null)
{
System.out.print(" " + Data.format(commSum, 10, 2));
}
else
{
System.out.print(" -");
}
System.out.println();
}
// close the cursor
cur7.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // subselectUsingGroupingSets
static void subselectUsingRollup()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT USING ROLLUP.");
employeeTbPartialContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT job, edlevel, SUM(comm)\n" +
" FROM employee\n" +
" WHERE job IN('DESIGNER', 'FIELDREP')\n" +
" GROUP BY ROLLUP(job, edlevel)\n" +
"\n" +
" Results:\n" +
" JOB EDLEVEL SUM(COMM)\n" +
" -------- ------- -----------");
Integer edlevel = new Integer(0);
Double commSum = new Double(0.0);
String job = null;
TbRead_Cursor7 cur7;
// declare a cursor
#sql cur7 = {SELECT job, edlevel, SUM(comm)
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP')
GROUP BY ROLLUP(job, edlevel)};
while (true)
{
// fetch the cursor
#sql {FETCH :cur7 INTO :job, :edlevel, :commSum};
if (cur7.endFetch())
{
break;
}
if (job != null)
{
System.out.print(" " + Data.format(job, 8));
}
else
{
System.out.print(" -");
}
if (edlevel != null)
{
System.out.print(" " + Data.format(edlevel, 7));
}
else
{
System.out.print(" -");
}
if (commSum != null)
{
System.out.print(" " + Data.format(commSum, 10, 2));
}
else
{
System.out.print(" -");
}
System.out.println();
}
// close the cursor
cur7.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // subselectUsingRollup
static void subselectUsingCube()
{
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" DECLARE CURSOR\n" +
" FETCH\n" +
" CLOSE\n" +
"TO PERFORM A SUBSELECT USING CUBE.");
employeeTbPartialContentDisplay();
System.out.println();
System.out.println(
" Perform:\n" +
" SELECT job, edlevel, SUM(comm)\n" +
" FROM employee\n" +
" WHERE job IN('DESIGNER', 'FIELDREP')\n" +
" GROUP BY CUBE(job, edlevel)\n" +
"\n" +
" Results:\n" +
" JOB EDLEVEL SUM(COMM)\n" +
" -------- ------- -----------");
Integer edlevel = new Integer(0);
Double commSum = new Double(0.0);
String job = null;
TbRead_Cursor7 cur7;
// declare a cursor
#sql cur7 = {SELECT job, edlevel, SUM(comm)
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP')
GROUP BY CUBE(job, edlevel)};
while (true)
{
// fetch the cursor
#sql {FETCH :cur7 INTO :job, :edlevel, :commSum};
if (cur7.endFetch())
{
break;
}
if (job != null)
{
System.out.print(" " + Data.format(job, 8));
}
else
{
System.out.print(" -");
}
if (edlevel != null)
{
System.out.print(" " + Data.format(edlevel, 7));
}
else
{
System.out.print(" -");
}
if (commSum != null)
{
System.out.print(" " + Data.format(commSum, 10, 2));
}
else
{
System.out.print(" -");
}
System.out.println();
}
// close the cursor
cur7.close();
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // subselectUsingCube
static void selectUsingQuerySampling()
{
float avg = 0.0f;
try
{
System.out.println();
System.out.println(
"----------------------------------------------------------\n" +
"USE THE SQL STATEMENTS:\n" +
" SELECT\n" +
"TO PERFORM A SELECT USING QUERY SAMPLING ");
System.out.println(
"\nCOMPUTING AVG(SALARY) WITHOUT SAMPLING \n" +
"\n Perform:\n" +
" SELECT AVG(salary) FROM employee \n" +
"\n Results:\n" +
" AVG SALARY\n" +
" ----------");
TbRead_Cursor8 cur8;
// declare a cursor
#sql cur8 = {SELECT AVG(salary) FROM employee};
// retrieve and display the result from the SELECT statement
#sql {FETCH :cur8 INTO :avg};
System.out.println(" " + avg);
cur8.close();
System.out.println(
"\nCOMPUTING AVG(SALARY) WITH QUERY SAMPLING" +
"\n - ROW LEVEL SAMPLING " +
"\n - BLOCK LEVEL SAMPLING \n" +
"\n ROW LEVEL SAMPLING : USE THE KEYWORD 'BERNOULLI'\n" +
"\nFOR A SAMPLING PERCENTAGE OF P, EACH ROW OF THE TABLE IS\n" +
"SELECTED FOR THE INCLUSION IN THE RESULT WITH A PROBABILITY\n" +
"OF P/100, INDEPENDENTLY OF THE OTHER ROWS IN T\n" +
"\n Perform:\n" +
" SELECT AVG(salary) FROM employee TABLESAMPLE BERNOULLI(25)" +
" REPEATABLE(5)\n" +
"\n Results:\n" +
" AVG SALARY\n" +
" ----------");
#sql cur8 = {SELECT AVG(salary)
FROM employee
TABLESAMPLE BERNOULLI(25) REPEATABLE(5)};
// retrieve and display the result from the SELECT statement
#sql {FETCH :cur8 INTO :avg};
System.out.println(" " +avg);
cur8.close();
System.out.println(
"\n\n BLOCK LEVEL SAMPLING : USE THE KEYWORD 'SYSTEM'\n" +
"\nFOR A SAMPLING PERCENTAGE OF P, EACH ROW OF THE TABLE IS\n" +
"SELECTED FOR INCLUSION IN THE RESULT WITH A PROBABILITY\n" +
"OF P/100, NOT NECESSARILY INDEPENDENTLY OF THE OTHER ROWS\n" +
"IN T, BASED UPON AN IMPLEMENTATION-DEPENDENT ALGORITHM\n" +
"\n Perform:\n" +
" SELECT AVG(salary) FROM employee TABLESAMPLE SYSTEM(50)" +
" REPEATABLE(1234)\n" +
"\n Results:\n" +
" AVG SALARY\n" +
" ----------" );
#sql cur8 = {SELECT AVG(salary)
FROM employee
TABLESAMPLE SYSTEM(50) REPEATABLE(1234)};
// retrieve and display the result from the SELECT statement
#sql {FETCH :cur8 INTO :avg};
System.out.println(" " + avg);
// close the cursor
cur8.close();
System.out.println(
"\nREPEATABLE CLAUSE ENSURES THAT REPEATED EXECUTIONS OF THAT\n" +
"TABLE REFERENCE WILL RETURN IDENTICAL RESULTS FOR THE SAME \n" +
"VALUE OF THE REPEAT ARGUMENT (IN PARENTHESIS).");
}
catch (Exception e)
{
SqljException sqljExc = new SqljException(e);
sqljExc.handle();
}
} // selectUsingQuerySampling
} // TbRead