DECLARE
	@PageSize INT,
	@PageNumber INT,
	@FirstRow INT,
	@LastRow INT
   
	SELECT	@PageSize = $pageLength,
	@PageNumber = $pageNumber;
   
	SELECT	@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
	@LastRow = (@PageNumber - 1) * @PageSize + @PageSize ;
					
					
	WITH results AS
					
					
 (  SELECT
   
   
    t1.[Converted_Mortgage_Number],
    t1.[Converted_Mortgage_Advance_No],
    t1.[Original_Account_Number],
    CASE
      WHEN t3.description IS NOT NULL
        THEN t3.description
    	ELSE t1.CACS_Location_Code
    END AS CACS_Location_Code,
    t1.[Original_Account_Number_1],
    t1.[Payment_Amount],
    t1.[Letter_ID],
    t1.[Batch_Activity_Date],
    t1.[Activity_Date],
    t1.[Activity_Time],
    CASE
		WHEN t6.description IS NOT NULL
		  THEN t1.[Collection_Activity] + ' - '+ t6.description
		  ELSE t1.[Collection_Activity]
	END AS [Collection_Activity],
    CASE
		WHEN t5.description IS NOT NULL
		  THEN t1.[Party_Contact_Code] + ' - ' + t5.description
		  ELSE t1.[Party_Contact_Code]
	END AS [Party_Contact_Code],
    CASE
	  WHEN t7.description IS NOT NULL
	  THEN t1.[Place_Called] + ' - ' + t7.description
	  ELSE t1.[Place_Called]
	END AS [Place_Called],  
    t1.[Promise_Amount_1],
    t1.[Promise_D_ate_1],
    t1.[Promise_Amount_2],
    t1.[Promise_D_ate_2],
    CASE
		WHEN t8.description IS NOT NULL
		THEN t1.[Non_Pay_Excuse_Code] + ' - ' + t8.description
		ELSE t1.[Non_Pay_Excuse_Code]
	END AS [Non_Pay_Excuse_Code],	
    CASE
      WHEN t2.description IS NOT NULL
        THEN t1.CACS_State + ' - ' + t2.description
        ELSE t1.CACS_State
    END AS CACS_State,
    t1.[User_ID/Transaction_Type],
    t1.[Arrears_Amount],
    t4.[Sequence_Number],
    t4.[History_Text],
    ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber
   
    FROM format_cacs_cch AS t1
   
    LEFT OUTER JOIN format_cacs_history_1 AS t4
    ON   t1.Converted_Mortgage_Number = t4.Converted_Mortgage_Number
    AND t1.Activity_Date = t4.Activity_Date
    AND t1.Activity_Time = t4.Activity_Time
    	  
    LEFT JOIN sd_cacs_states AS t2
    ON   t1.CACS_Location_Code = t2.location
    AND t1.CACS_State = t2.state
    	  
    LEFT JOIN sd_cacs_Location_details AS t3
					        ON t1.CACS_Location_Code = t3.code 
		
	LEFT JOIN sd_party_contacted_code AS t5	
	ON t1.[Party_Contact_Code] = t5.code
	
	LEFT JOIN sd_collection_activity_code AS t6
	ON t1.[Collection_Activity] = t6.code
	
	LEFT JOIN sd_place_called AS t7
	ON t1.[Place_Called] = t7.code
	
	LEFT JOIN sd_non_pay_excuse_code AS t8
	ON t1.[Non_Pay_Excuse_Code] = t8.code
	
    WHERE (t1.Converted_Mortgage_Number = '$mortgage'  $additional_date_params)
	 
   
	
	)
	
   SELECT  
		  
   [Converted_Mortgage_Number],
   [Converted_Mortgage_Advance_No],
   [Original_Account_Number],
    CACS_Location_Code,
   [Original_Account_Number_1],
   [Payment_Amount],
   [Letter_ID],
   [Batch_Activity_Date],
   [Activity_Date],
   [Activity_Time],
   [Collection_Activity],
   [Party_Contact_Code],
   [Place_Called],
   [Promise_Amount_1],
   [Promise_D_ate_1],
   [Promise_Amount_2],
   [Promise_D_ate_2],
   [Non_Pay_Excuse_Code],
   CACS_State,
   [User_ID/Transaction_Type],
   [Arrears_Amount],
   [Sequence_Number],
   [History_Text]	
		  
   FROM results
   
   WHERE	RowNumber BETWEEN @FirstRow AND @LastRow
   ORDER BY [$criteria] $direction ;

quote:

---

Originally posted by harsh_athalye

I believe this is MySQL. In that case, you need to post to mysql forum site, this site is purely for MS SQL Server.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

---

ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber

quote:

---

Originally posted by Lamprey

just a guess but does the [$criteria] from this line qualify the column with the correct table prefix?

ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber

---