Hi,

I've created some pages with navigational links using the following COUNT and nested statements. They work perfectly fine when I'm getting fields from a single table.

-->COUNT rows in the TABLE:

$display_rows = 20;
$sql_count = "SELECT COUNT(*) FROM TABLE1 where FIELD1 like upper('$query')||'%'";
$row_count=OCIParse($conn, $sql_count);
OCIExecute($row_count);
if (OCIFetch($row_count)) {
$num_rows = OCIResult($row_count,1);
} else {


-->Select the right FIELDS:

$sql = "select FIELD1, FIELD2, FIELD3, COUNTER from (select ";
$sql .= " FIELD1, FIELD2, FIELD3, ROWNUM as \"COUNTER\" from (select ";
$sql .= " FIELD1, FIELD2, FIELD3 from TABLE1 where FIELD1 like upper('$ query')||'%')) where COUNTER between ";
$sql .= $start_range." AND ".$end_range;

-->Churn out the data:

if ($num_rows != 0) {
// Parse the SQL string & execute it
$rs=OCIParse($conn,$sql);
OCIDefineByName($rs,"FIELD1",&$field1);

etc etc

-->Display the page numbering system:

if ($num_rows >= $display_rows && $num_rows > 0) {
$num_rows++;

etc etc

I start to run into problems when I want to take FIELDS from *two* TABLES. This is what I've tried ... which I know isn't correct:

$sql = "select TABLE1.FIELD1, TABLE1.FIELD2, TABLE2.FIELDX, TABLE2.FIELDY, COUNTER from (select ";
$sql .= " TABLE1.FIELD1, TABLE1.FIELD2, TABLE2.FIELDX, TABLE2.FIELDY, ROWNUM as \"COUNTER\" from (select ";
$sql .= "TABLE1.FIELD1, TABLE1.FIELD2, TABLE2.FIELDX, TABLE2.FIELDY from TABLE1, TABLE 2 where TABLE1.FIELD1 like upper('$query')||'%')) where COUNTER between ";
$sql .= $start_range." AND ".$end_range;

The error message I get is 'undefined column' due obviously to COUNTER or ROWNUM. Anyone has any ideas how I would restructure the second nested SQL statement to include an intersect or join? Also, how would I alter the first SQL statement that does the row counting?

All help is much appreciated.

Gio