Pagination SQL error when ordering on subquery result

[jeff-chastain]

Suppose I have a Quick result set where one of the properties is derived from a sub-query. If I attempt to paginate and order that result set by the sub-query derived property and error is thrown because the ROW_NUMBER() OVER (...) clause is at the same level in the query as the sub-query property.

This is the current SQL generated by QB in this scenario ... (note the balanceDue derived column right next to the ROW_NUMBER())

SELECT	* 
FROM	(
		SELECT	[tblBK_Travel].*, 
			(
				(
					SELECT COALESCE( SUM( tblBK_Travel_Cost.Amount ), 0 )
					FROM tblBK_Travel_Cost 
					WHERE tblBK_Travel.Travel_ID = tblBK_Travel_Cost.Travel_ID
				) - (
					SELECT COALESCE( SUM(tblBK_Travel_Payment.Amount), 0 ) 
					FROM tblBK_Travel_Payment 
					WHERE tblBK_Travel.Travel_ID = tblBK_Travel_Payment.Travel_ID
				)
			) AS balanceDue, 
			ROW_NUMBER() OVER (
				ORDER BY CASE 
					WHEN balanceDue IS NULL OR balanceDue = '' THEN 1 ELSE 0 END, 
					[balanceDue] DESC, 
					[tblBK_Travel].[Travel_ID] DESC
				) AS [QB_RN] 

		FROM	[tblBK_Travel] 

		WHERE	[tblBK_Travel].[Trip_ID] = '1664100' 
	) [QB_RESULTS] 

WHERE	[QB_RN] > 0 
		AND [QB_RN] <= 25 
ORDER BY 
		[QB_RN] ASC

If instead, the base query is wrapped in a SELECT * with the ROW_NUMBER() OVER (...) applied at that level, then derived columns can be used without issue in the order by.

SELECT	* 
FROM	(
			SELECT	*,
					ROW_NUMBER() OVER (
						ORDER BY CASE 
							WHEN balanceDue IS NULL OR balanceDue = '' THEN 1 ELSE 0 END, 
							[balanceDue] DESC, 
							[Travel_ID] DESC
						) AS [QB_RN]

			FROM	(
						SELECT	[tblBK_Travel].*, 
							(
								(
									SELECT COALESCE( SUM( tblBK_Travel_Cost.Amount ), 0 )
									FROM tblBK_Travel_Cost 
									WHERE tblBK_Travel.Travel_ID = tblBK_Travel_Cost.Travel_ID
								) - (
									SELECT COALESCE( SUM(tblBK_Travel_Payment.Amount), 0 ) 
									FROM tblBK_Travel_Payment 
									WHERE tblBK_Travel.Travel_ID = tblBK_Travel_Payment.Travel_ID
								)
							) AS balanceDue

						FROM	[tblBK_Travel] 

						WHERE	[tblBK_Travel].[Trip_ID] = '1664100' 

					) [QB_RESULTS] 

		) [QB_PAGINATED_RESULTS] 

WHERE	[QB_RN] > 0 
		AND [QB_RN] <= 25 
ORDER BY 
		[QB_RN] ASC