Hello,

I had this query that ran perfectly on SqlServer, but Oracle doesn't like it:

RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(MStatementEmissionCycleEntity.Relations.MStatementEmissionEntityUsingIdMAcquirerIdMProductGenerationDate);
filter.PredicateExpression.Add(PredicateFactory.CompareValue(MStatementEmissionCycleFieldIndex.IdMAcquirer, ComparisonOperator.Equal, idAcquirer));
filter.PredicateExpression.Add(PredicateFactory.CompareValue(MStatementEmissionCycleFieldIndex.IdMProduct, ComparisonOperator.Equal, idProduct));

ResultsetFields fields = new ResultsetFields(6);
fields.DefineField(MStatementEmissionCycleFieldIndex.IdMAcquirer, 0, "IdMAcquirer");
fields.DefineField(MStatementEmissionCycleFieldIndex.IdMProduct, 1, "IdMProduct");
fields.DefineField(MStatementEmissionCycleFieldIndex.Type, 2, "Type");
fields.DefineField(MStatementEmissionCycleFieldIndex.IdMCycle, 3, "IdMCycle");
fields.DefineField(MStatementEmissionFieldIndex.GenerationResult, 4, "GenerationResult", "MStatementEmission", AggregateFunction.Max);
fields.DefineField(MStatementEmissionCycleFieldIndex.GenerationDate, 5, "GenerationDate", "MStatementEmissionCycle", AggregateFunction.Max);
            
ISortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(MStatementEmissionCycleFieldIndex.GenerationDate, SortOperator.Descending));

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

DataTable dynamicList = new DataTable();
adapter.FetchTypedList(fields, dynamicList, filter, 0, sorter, true, groupBy);

I checked the query using the marvelous trace listeners, it goes like this:

Query: 

SELECT 
"MStatementEmissionCycle"."IdMAcquirer" AS "IdMAcquirer",
"MStatementEmissionCycle"."IdMProduct" AS "IdMProduct",
"MStatementEmissionCycle"."Type" AS "Type",
"MStatementEmissionCycle"."IdMCycle" AS "IdMCycle",
MAX("MStatementEmission"."GenerationResult") AS "GenerationResult",
MAX("MStatementEmissionCycle"."GenerationDate") AS "GenerationDate" 

FROM  "MStatementEmission", "MStatementEmissionCycle" 

WHERE  
"MStatementEmission"."IdMAcquirer"="MStatementEmissionCycle"."IdMAcquirer" AND 
"MStatementEmission"."IdMProduct"="MStatementEmissionCycle"."IdMProduct" AND 
"MStatementEmission"."GenerationDate"="MStatementEmissionCycle"."GenerationDate" AND 
( "MStatementEmissionCycle"."IdMAcquirer" = :IdMAcquirer1 And "MStatementEmissionCycle"."IdMProduct" = :IdMProduct2) 

GROUP BY 
"MStatementEmissionCycle"."IdMAcquirer",
"MStatementEmissionCycle"."IdMProduct",
"MStatementEmissionCycle"."Type",
"MStatementEmissionCycle"."IdMCycle" 

ORDER BY "MStatementEmissionCycle"."GenerationDate" DESC
    
    
    Parameter: :IdMAcquirer1 : Int16. Length: 0. Precision: 4. Scale: 0. Direction: Input. Value: 1.
    Parameter: :IdMProduct2 : Int16. Length: 0. Precision: 4. Scale: 0. Direction: Input. Value: 1.

At first sight it looks fine, but take a closer look at the ORDER BY clause. It says

ORDER BY "MStatementEmissionCycle"."GenerationDate" DESC

, but it should say

ORDER BY MAX("MStatementEmissionCycle"."GenerationDate") AS "GenerationDate"

Any thoughts on how to get it right?

Thanks for your time! álvaro.-

p.s.: some context, Oracle is 9i accessed using ODP.NET 9.2.0.7, LLBLGenPro version is 2004.2.final.

Please re-use the field with the aggregate you have just created (fields[5]) in the sortclause:

ISortExpression sortClauses = new SortExpression(new SortClause(fields[5], null, SortOperator.Descending));

To end up with:

ORDER BY GenerationDate DESC

Thanks for your suggestion, it's certainly an improvement but it still doesn't work.

Now the ORDER BY clause looks like:

ORDER BY GenerationDate DESC

Please note that it lacks the double quotes it has in the SELECT list, it should be

ORDER BY "GenerationDate" DESC

Any other suggestions?

Thanks, álvaro.-

That shouldn't matter as long as the casing is the same. You get an error the field isn't found? or do you get an error that an alias isn't supported in the order by list?

(edit): hmm. Also in 1.0.2005.1 the quotes are omitted, while the quotes in the select list are always specified. It always should produce a valid alias name, not the raw alias value.

I get this:

Exception: Oracle.DataAccess.Client.OracleException
Message: ORA-00904: "GENERATIONDATE": invalid identifier
Source: Oracle Data Provider for .NET

So I guessed it was missing the quotes.

cheers álvaro.-

Yes, bug.

I'll fix this for you in the 1.0.2004.2 DQE, and also in the 1.0.2005.1 runtimes.

For 1.0.2005.1 users, a workaround is to add a space in the alias, which forces the current code to formulate a proper alias with quotes in the order by.

Brilliant, this will be a wonderful, errm, "Magician Kings" gift (I wonder if other countries/cultures have a gift-giving holiday on january 6th as well).

Let me know when I can put this to work, as this is an important fix for me.

Thanks again, álvaro.-

Alvaro wrote:

Brilliant, this will be a wonderful, errm, "Magician Kings" gift (I wonder if other countries/cultures have a gift-giving holiday on january 6th as well).

Let me know when I can put this to work, as this is an important fix for me.

Thanks again, álvaro.-

I'll upload the updated runtimes for 1.0.2004.2 shortly (hopefully within the hour). I also fixed it in 1.0.2005.1, as a hotfix.

(edit) It's now available: http://www.llblgen.com/pages/secure/runtimelibraries.aspx

please scroll down to the 1.0.2004.2 archive.

Wonderful, I'll try it tomorrow and let you know how it works out.

Thanks a lot, álvaro.-

(edit) Tried it out, worked like a charm, thanks!!!