Hello, my problem is outlined below.

I have a need to sort on a field in a dynamic list. I've had some problems and gone over the suggestions in the forum but I've hit a snag which I can't seem to resolve.

I am using: LLBLGen v2.0.0.0 Final (October 23, 2006) Runtime Library Version: 2.0.0.61023 .net 2.0 Oracle 10g

My code is this:

            ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(PostFields.PostId, 0, PostFields.PostId.Alias);
            fields.DefineField(PostReportFields.Report, 1, "Count", string.Empty, AggregateFunction.Count);
            fields.DefineField(PostReportFields.DateReported, 2, AggregateFunction.Min);

            IRelationPredicateBucket filter = new RelationPredicateBucket(PostReportFields.IsClosed == false);
            if (ChannelId.HasValue) filter.PredicateExpression.Add(ChannelFields.ChannelId == ChannelId.Value);
            filter.Relations.Add(ChannelEntity.Relations.ForumGroupEntityUsingChannelId);
            filter.Relations.Add(ForumGroupEntity.Relations.ForumEntityUsingForumGroupId);
            filter.Relations.Add(ForumEntity.Relations.ThreadEntityUsingForumId);
            filter.Relations.Add(ThreadEntity.Relations.PostEntityUsingThreadId);
            filter.Relations.Add(PostEntity.Relations.PostReportEntityUsingPostId);

            IGroupByCollection groupBy = new GroupByCollection();
            groupBy.Add(fields[0]);

            SortExpression sorter = new SortExpression(new SortClause(fields[2], null, SortOperator.Ascending));

            DataTable dynamicList = new DataTable();
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                adapter.FetchTypedList(fields, dynamicList, filter, pageSize, sorter, false, groupBy, pageNumber, pageSize);
            }

which puts out the following SQL:

SELECT "TEST_JBYRD"."POST"."POSTID" AS "PostId", COUNT("TEST_JBYRD"."POSTREPORT"."REPORT") AS "Count", MIN("TEST_JBYRD"."POSTREPORT"."DATEREPORTED") AS "DateReported" FROM (((((((( "TEST_JBYRD"."CHANNEL"  INNER JOIN "TEST_JBYRD"."FORUMGROUP"  ON  "TEST_JBYRD"."CHANNEL"."CHANNELID"="TEST_JBYRD"."FORUMGROUP"."CHANNELID") INNER JOIN "TEST_JBYRD"."FORUM"  ON  "TEST_JBYRD"."FORUMGROUP"."FORUMGROUPID"="TEST_JBYRD"."FORUM"."FORUMGROUPID") INNER JOIN "TEST_JBYRD"."FORUMBASE"  ON  "TEST_JBYRD"."FORUMBASE"."FORUMBASEID"="TEST_JBYRD"."FORUM"."FORUMID") INNER JOIN "TEST_JBYRD"."THREAD"  ON  "TEST_JBYRD"."FORUM"."FORUMID"="TEST_JBYRD"."THREAD"."FORUMID") INNER JOIN "TEST_JBYRD"."THREADBASE"  ON  "TEST_JBYRD"."THREADBASE"."THREADBASEID"="TEST_JBYRD"."THREAD"."THREADID") INNER JOIN "TEST_JBYRD"."POST"  ON  "TEST_JBYRD"."THREAD"."THREADID"="TEST_JBYRD"."POST"."THREADID") INNER JOIN "TEST_JBYRD"."POSTBASE"  ON  "TEST_JBYRD"."POSTBASE"."POSTBASEID"="TEST_JBYRD"."POST"."POSTID") INNER JOIN "TEST_JBYRD"."POSTREPORT"  ON  "TEST_JBYRD"."POST"."POSTID"="TEST_JBYRD"."POSTREPORT"."POSTID") WHERE ( ( "TEST_JBYRD"."POSTREPORT"."ISCLOSED" = :IsClosed1)) GROUP BY "TEST_JBYRD"."POST"."POSTID" ORDER BY DateReported ASC
:IsClosed1: 0

This returns the following ORA error:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: ORA-00979: not a GROUP BY expression. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> Oracle.DataAccess.Client.OracleException : ORA-00979: not a GROUP BY expression

I know all that looks ugly but I basically want to fetch 3 fields. I use a GroupBy on the PostId and an Aggregate function on the other two. Using Oracle's SQLPlus Worksheet I've found the following things:

1) If I remove the ORDER BY statement, the query works as intended (without sorting) 2) If I change 'ORDER BY DateReported' to 'ORDER BY "DateReported"' (enclose "DateReported" in double quotes) it works as intended with sorting

As I said, I can't seem to get past this error. Any help you could give on this issue would be greatly appreciated.

Please try to specify an alias to the ordered by field:

fields.DefineField(PostReportFields.DateReported, 2, "MyAlias", AggregateFunction.Min);

Thanks for the swift reply, I tried an ailias called "DateAilias". The ora error was different, but the same problem exists.

The END of the SQL statement has changed to this:

ORDER BY DateAilias ASC

The new ORA error code is:

 SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: ORA-00904: "DATEAILIAS": invalid identifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> Oracle.DataAccess.Client.OracleException : ORA-00904: "DATEAILIAS": invalid identifier

Once again though, this is fixed by enclosing DateAilias in double quotes. So the end of the SQL code looked like this instead:

ORDER BY "DateAilias" ASC

Looks like this is something that has cropped up before. A temporary workaround that was posted then that may work now is to put a space in your alias to force the quotes. http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5060&HighLight=1 We'll forward this to Frans for a long term answer.

You may also try the latest version of LLBLGen. There have been a few fixes that may help this out since Oct.

Adding a space to the alias to force quoting did infact work. Thank you for all your help! I will check this thread periodically if there is a cleaner fix availible, but for now this workaround works just fine.