PHP PDO MSSQL ROW_NUMBER() error

0
votes

I'm using php pdo with SQL Server Native Client 11.0 as the driver. Obiously I do cannot use LIMIT clause as it is proprietary and in trying to workarounf selecting ranges of rows I keep hitting issues.

My setup as follows :

$statement = "SELECT ROW_NUMBER() OVER (ORDER BY USERS.name DESC) AS RowNum,  CASE.no, CASE.type, CASE.date,    CASE.ju, USERS.name, TBRLMF.rd_rl_description FROM dbo.CASE INNER JOIN dbo.USERS ON dbo.CASE.no = dbo.USERS.case_no INNER JOIN dbo.CMSTBRLMF ON dbo.USERS.relationship = dbo.TBRLMF.code ";

if($exact == 'checked'){
$exact = '=';
}else{
$exact = 'LIKE';
}
if($searchtype == 'users'){
$statement .= " WHERE USERS.name $exact '%$searchstring%'";
}else{
$statement .= " WHERE USERS.no $exac '%$searchstring%'";
}
$statement .= " AND RowNum BETWEEN :offset AND :max";

$statement  = $dbh->prepare($statement);
$statement->bindParam(':offset', $offset, PDO::PARAM_INT);
$statement->bindParam(':max', $max, PDO::PARAM_INT);

and as long as I do not include the line of code :

$statement .= " AND RowNum BETWEEN :offset AND :max";

It works fine but gives me all the data.

When that line is included I recieve the following error :-

caught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 207 [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'RowNum'. (SQLExecute[207] at ext\pdo_odbc\odbc_stmt.c:254)' in C:\pub\test\Classes\Core.php:44

I am at a loss and MSSQL is new to me.

Help much appreciated.

2
phpsql-serverpdo

2 Answers

1
votes

The predicate on that column can't be in a WHERE clause. (The predicates in the WHERE clause are evaluated when the rows are accessed; the value of that expression (analytic function) isn't available until after the rows are accessed.

You may be able to reference the column alias in a HAVING clause:

" HAVING RowNum BETWEEN ... ";
1
votes

You can't use COLUMN ALIAS (RowNum in your case) in WHERE clause. Rather change your query using derived table like below

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY USERS.name DESC) AS RowNum,  
CASE.no, 
CASE.type, 
CASE.date,    
CASE.ju, 
USERS.name, 
TBRLMF.rd_rl_description 
FROM dbo.CASE 
INNER JOIN dbo.USERS 
ON dbo.CASE.no = dbo.USERS.case_no 
INNER JOIN dbo.CMSTBRLMF 
ON dbo.USERS.relationship = dbo.TBRLMF.code 
WHERE USERS.name LIKE '%string%'
AND USERS.no LIKE '%string%'
) X 
WHERE RowNum BETWEEN 1 AND 10;

EDIT:

You can also use CTE (Common Table Expression) to get around this like below

Create the CTE

WITH NEWCTE AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY USERS.name DESC) AS RowNum,  
    CASE.no, 
    CASE.type, 
    CASE.date,    
    CASE.ju, 
    USERS.name, 
    TBRLMF.rd_rl_description 
    FROM dbo.CASE 
    INNER JOIN dbo.USERS 
    ON dbo.CASE.no = dbo.USERS.case_no 
    INNER JOIN dbo.CMSTBRLMF 
    ON dbo.USERS.relationship = dbo.TBRLMF.code 
    WHERE USERS.name LIKE '%searchstring%'
    AND USERS.no LIKE '%searchstring%'
)

Query the CTE

SELECT * 
FROM NEWCTE
WHERE RowNum BETWEEN 1 AND 10