× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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>




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.