Neoview Guide to Stored Procedures in Java (R2.2)

TOPSALESREPS Procedure
The TOPSALESREPS procedure accepts a number representing the fiscal quarter (1, 2, 3, and 4,
with each number representing a range of months) and returns the employee number, first name,
last name, and sale figures of the top five sales representatives who had the highest sales
(unit_price * qty_ordered) that quarter.
Java Method: topSalesReps()
Example A-12 topSalesReps() Method
public static void topSalesReps(int whichQuarter,
ResultSet[] topReps)
throws SQLException
{
if (whichQuarter < 1 || whichQuarter > 4)
{
throw new SQLException ("Invalid value for quarter. " +
"Retry the CALL statement " +
"using a number from 1 to 4 " +
"to represent the quarter.", "38001" );
}
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement getTopReps = conn.prepareStatement(
" SELECT [first 5] E.empnum, E.first_name, " +
" E.last_name, TOTALS.total " +
" FROM persnl.employee E, " +
" ( SELECT O.salesrep, " +
" sum(OD.unit_price * OD.qty_ordered) as total " +
" FROM sales.orders O, sales.odetail OD " +
" WHERE O.ordernum = OD.ordernum " +
" AND quarter(O.order_date) = ? " +
" GROUP BY O.salesrep " +
" ) TOTALS " +
" WHERE E.empnum = TOTALS.salesrep " +
" ORDER BY TOTALS.total descending ");
getTopReps.setInt(1, whichQuarter);
topReps[0] = getTopReps.executeQuery();
}
Creating the Procedure: TOPSALESREPS
To create this procedure in the PERSNL schema, upload the Payroll.jar file to the Neoview
platform, navigate to the PERSNL schema in DB Admin, and then enter or select these values in
the Create Procedure Wizard of DB Admin. For instructions, see “Uploading SPJ JAR Files to
the Neoview Platform” (page 35) and “Creating SPJs” (page 43).
topsalesrepsName:
Payroll.jar > Payroll > topSalesRepsCode:
• First SQL parameter:
— Name: whichquarter
— Direction: IN
— SQL Data Type: INTEGER
— Java Data Type: int
Parameters:
• Dynamic result sets: 1
• Accesses Database: selected
Attributes:
96 Sample SPJs