background image
<< USING clause | General expressions >>

WHERE CURRENT OF clause

<< USING clause | General expressions >>
Derby Reference Manual
87
Boolean expressions are allowed in the WHERE clause. Most of the general expressions
listed in
General expressions
, can result in a boolean value.
In addition, there are the more common boolean expressions. Specific boolean operators
listed in Table 10, take one or more operands; the expressions return a boolean value.
Example
-- find the flights where no business-class seats have
-- been booked
SELECT *
FROM FlightAvailability
WHERE business_seats_taken IS NULL
OR business_seats_taken = 0
-- Join the EMP_ACT and EMPLOYEE tables
-- select all the columns from the EMP_ACT table and
-- add the employee's surname (LASTNAME) from the EMPLOYEE table
-- to each row of the result.
SELECT SAMP.EMP_ACT.*, LASTNAME
FROM SAMP.EMP_ACT, SAMP.EMPLOYEE
WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO
-- Determine the employee number and salary of sales representatives
-- along with the average salary and head count of their departments.
-- This query must first create a new-column-name specified in the AS
clause
-- which is outside the fullselect (DINFO)
-- in order to get the AVGSALARY and EMPCOUNT columns,
-- as well as the DEPTNO column that is used in the WHERE clause
SELECT THIS_EMP.EMPNO, THIS_EMP.SALARY, DINFO.AVGSALARY, DINFO.EMPCOUNT
FROM EMPLOYEE THIS_EMP,
(SELECT OTHERS.WORKDEPT AS DEPTNO,
AVG(OTHERS.SALARY) AS AVGSALARY,
COUNT(*) AS EMPCOUNT
FROM EMPLOYEE OTHERS
GROUP BY OTHERS.WORKDEPT
)AS DINFO
WHERE THIS_EMP.JOB = 'SALESREP'
AND THIS_EMP.WORKDEPT = DINFO.DEPTNO
WHERE CURRENT OF clause
The WHERE CURRENT OF clause is a clause in some UPDATE and DELETE
statements. It allows you to perform positioned updates and deletes on updatable
cursors. For more information about updatable cursors, see
SELECT statement
.
Syntax
WHERE CURRENT OF
cursor-Name
Example
Statement s = conn.createStatement();
s.setCursorName("AirlinesResults");
ResultSet rs = conn.executeQuery(
"SELECT Airline, basic_rate " +
"FROM Airlines FOR UPDATE OF basic_rate");
Statement s2 = conn.createStatement();
s2.executeUpdate("UPDATE Airlines SET basic_rate = basic_rate " +
"+ .25 WHERE CURRENT OF AirlinesResults");
SQL expressions
Syntax for many statements and expressions includes the term Expression, or a term for
a specific kind of expression such as TableSubquery. Expressions are allowed in these
specified places within statements.