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 ROWNUMBER())

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