Aggregate query errors when using .Paged()

[blankers]

See the error explained here:

• http://forums.subsonicproject.com/t/3708.aspx

There was supposedly a workitem here at one time but the URL is dead:

• http://www.codeplex.com/subsonic/WorkItem/View.aspx?WorkItemId=18219

The fix is in Sql2005Generator.cs, starting at line 24:

change:

    private const string PAGING_SQL =
        @"
                SELECT {7}
                FROM     (SELECT ROW_NUMBER() OVER ({1}) AS Row, 
                {0} 
                {2}
                {3}
                {4}
                )
                                       AS PagedResults
                WHERE  Row >= {5} AND Row <= {6}";

to:

    private const string PAGING_SQL =
        @"
                SELECT {8}
                FROM     (SELECT ROW_NUMBER() OVER ({1}) AS Row, 
                {0} 
                {2}
                {3}
                {4}
                {5}
                )
                                       AS PagedResults
                WHERE  Row >= {6} AND Row <= {7}";

And add this variable declaration:

        string groupby = string.Empty;

Change:

        if (qry.Aggregates.Count > 0)
            joins = String.Concat(joins, GenerateGroupBy());

to:

        if (qry.Aggregates.Count > 0)
            groupby = String.Concat(joins, GenerateGroupBy());

And change:

        string sql = string.Format(PAGING_SQL, columnList, orderby, fromLine, joins, wheres, pageStart, pageEnd, top);

to:

        string sql = string.Format(PAGING_SQL, columnList, orderby, fromLine, joins, wheres, groupby, pageStart, pageEnd, top);

[6pac]

Thanks for that. I'm probably one of the only ones with commit privs who's still active right now, but I've only just been given them and still have to work out the admin end of GIT. It's crunch time for a major project for me, but in another month I'm going to start reviewing and committing a few bugs a week until they're all accounted for - on SS2 and SS3. Thanks again,

Ben McIntyre

[6pac]

Implemented. Pull the repo.