1
Vote

TopRange does not generate correct query for SQL Client

description

Hi,

I having an issue when using TopRange method for sql client.

Following lines generates non-valid sql query.
DBSelectQuery sel = DBQuery.Select()
                    .Field("ProductKey")
                    .Field("OrderDateKey")
                    .Field("CustomerKey")
                    .GroupBy("ProductKey")
                    .GroupBy("OrderDateKey")
                    .GroupBy("CustomerKey")
                    .From("FactInternetSales");

DBSelectQuery main = DBQuery.Select();
main = main.TopRange(10, 100)
    .Field("ProductKey")
    .Field("OrderDateKey")
    .Field("CustomerKey")
    .From(sel).As("sub");
Generated SQL ;
SELECT [ProductKey], [OrderDateKey], [CustomerKey] 
FROM (
    SELECT  ROW_NUMBER() OVER( [ProductKey],[OrderDateKey],[CustomerKey]) AS _rowNum
            , [ProductKey], [OrderDateKey], [CustomerKey]
        FROM (
            SELECT [ProductKey], [OrderDateKey], [CustomerKey]
                FROM [FactInternetSales]
                GROUP BY [ProductKey], [OrderDateKey], [CustomerKey]
            ) AS _rowtable 
    WHERE _rowNum BETWEEN 10 AND 109 [ProductKey],[OrderDateKey],[CustomerKey] AS [sub];
and the SQL is not even valid.

Am I using TopRange inappropriately ?
  • ROW_NUMBER should at least have ORDER BY or PARTITION BY clause in OVER clause.
  • The _rowNum generated column should be filtered on top level select statement.
  • paranthesis are not managed properly.

comments