Oh rats.
I've created a new QAQQINI file with IGNORE_DERIVED_INDEX set to *NO.
But so ROWNMUMBER() OVER() still fails because of "An OLAP function is not supported for this query".
My files must have triggers which appears to stop SQL using SQE, which is necessary for the function to work.
Works fine if I remove the trigger but they are essential.
Peter
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Peter Connell
Sent: Friday, 4 December 2009 6:00 p.m.
To: 'Web Enabling the AS400 / iSeries'
Subject: Re: [WEB400] Pagination with PHP and DB2 (simple working example)
Kelly,
That is just so clever I could cry.
Peter
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Kelly Cookson
Sent: Friday, 4 December 2009 5:04 p.m.
To: Web Enabling the AS400 / iSeries
Subject: [WEB400] Pagination with PHP and DB2 (simple working example)
This is certainly not the only way to paginate with DB2. I just thought
it might be useful to have a working example in the WEB400 archive.
The script assumes a DB2 file called MYLIB/MYEOP. The MYEOP file
contains four fields: EOPYEAR, EOPPRD, EOPBEGDT, EOPCLSDT.
EOPYEAR = year
EOPPRD = period
EOPBEGDT = begin date
EOPCLSDT = close date
The file holds the begin and close dates for each business period of
every year. I want to scroll back and forth through the file, seeing the
information for one year at a time. (We happen to have 13 business
periods in each year. So I want to see 13 records at a time.).
The script uses a nested SELECT to accomplish the scrolling:
"SELECT * FROM
(SELECT A.EOPYEAR, A.EOPPRD, A.EOPBEGDT, A.EOPCLSDT, ROWNUMBER()
OVER() AS RN
FROM MYLIB.MYEOP A)
X WHERE X.RN BETWEEN $rn AND $limit";
The inner SELECT retrieves all the records from the MYEOP file and adds
a new "row number field" using the ROWNUMBER() OVER() option. The outer
SELECT retrieves only the records with the desired range of row numbers.
The variable $rn contains the first row number. The variable $limit
contains the last row number.
The script also contains two forms. The form buttons let the user post
requests to scroll forward to the "next" records or backward to the
"previous" records. The session variable $_SESSION["rn"] stores the row
number across scrolling requests.
The script is listed in full below. Hope it helps.
Kelly
<?php
session_start();
switch ($_POST["scroll"])
{
case "next":
$_SESSION["rn"] = $_SESSION["rn"] + 13;
unset($_POST["scroll"]);
break;
case "previous":
$_SESSION["rn"] = $_SESSION["rn"] - 13;
if ($_SESSION["rn"] < 1)
{
$_SESSION["rn"] = 1;
}
unset($_POST["scroll"]);
break;
default:
$_SESSION["rn"] = 1;
break;
}
$rn = $_SESSION["rn"];
$limit = ($rn + 12);
?>
<html>
<head>
<title>Pagination Example</title>
<style type="text/css">
body {
text-align: center;
}
table {
margin: 10px;
border: 1px solid #000000;
}
th, td {
border: 1px solid #dedede;
padding: 2px 4px 2px 4px;
}
form {
display: inline;
text-align: center;
}
input {
width: 100px;
}
</style>
</head>
<body>
<h2>Years and Periods</h2>
<table>
<tr>
<th>Row #</th>
<th>Year</th>
<th>Period</th>
<th>Begin</th>
<th>End</th>
</tr>
<?php
$conn = db2_connect("", "", "");
$query =
"SELECT * FROM
(SELECT A.EOPYEAR, A.EOPPRD, A.EOPBEGDT, A.EOPCLSDT, ROWNUMBER()
OVER()
AS RN FROM MYLIB.MYEOP A)
X WHERE X.RN BETWEEN $rn AND $limit";
$statement = db2_prepare($conn, $query);
db2_execute($statement);
while ($row = db2_fetch_assoc($statement))
{
echo "<tr><td>".$row["RN"]."</td>";
echo "<td>".$row["EOPYEAR"]."</td>";
echo "<td>".$row["EOPPRD"]."</td>";
echo "<td>".$row["EOPBEGDT"]."</td>";
echo "<td>".$row["EOPCLSDT"]."</td></tr>";
}
db2_close($conn);
?>
</table>
<form method="post" action="pagination.php">
<input type="hidden" name="scroll" value="previous" />
<input type="submit" value="Previous" />
</form>
<form method="post" action="pagination.php">
<input type="hidden" name="scroll" value="next" />
<input type="submit" value="Next" />
</form>
</body>
</html>
midrange.com
