Hi,

I'm trying to produce the query below, but I do not know how to access the generated subquery column, total, or even aliased table "T_Cert" in the enclosing select clause (Oracle 11g).

SELECT total, "o"."CERT_ID"
FROM      "T_CERT" "o"
           LEFT JOIN
              (SELECT "T_HUM_CERT_LINK"."CERT_ID", SUM("T_HUM_CERT_LINK"."BALANCE") AS total
               FROM "T_HUM_CERT_LINK"
               GROUP BY "T_HUM_CERT_LINK"."CERT_ID") "s"
          ON  ( "s"."CERT_ID" = "o"."CERT_ID")

Table "T_CERT" has a one-to-many to its bridge table ("T_HUM_CERT_LINK"). I have tried to create a derived table and use it as a dynamic relation in my outer relation, but I can't get it to work.

ResultsetFields sumBalance = new ResultsetFields(2);    
sumBalance.DefineField(THumCertLink.CertId.SetObjectAlias("s"), 0);
sumBalance.DefineField(THumCertLink.Balance.SetAggregateFunction(AggregateFunction.Sum), 1, "total");

IGroupByCollection groupByCertId = new GroupByCollection();
groupByCertId.Add(THumCertLink.CertId);

DerivedTableDefinition sumOfAllBalanceDTD = new DerivedTableDefinition(sumBalance, "s", null, groupByCertId );

IDynamicRelation sumOfAllBalanceRelation = new DynamicRelation(sumOfAllBalanceDTD, JoinHint.Left, EntityType.TCertEntity, "tcert", new PredicateExpression(THumCertLink.CertId.SetObjectAlias("s") == TCertFields.CertId.SetObjectAlias("tcert")));

IRelationCollection relations = new RelationCollection();
relations.Add(sumOfAllBalanceRelation);


ResultsetFields resultSet = new ResultsetFields(2);

resultSet.DefineField(new EntityField(string.Empty, "total", typeof(decimal)), 0, "totalBalance");
resultSet.DefineField(TCertFields.CertId, 1, "pcid");

TypedListDAO.GetMultiAsDataTable(resultSet, 0, null, null, relations, false, null, 0, 0);

Can somebody point me in the right direction as to how to produce the query above? Thanks.

Ok, got it.

ResultsetFields sumBalance = new ResultsetFields(2);    
sumBalance.DefineField(THumCertLink.CertId, 0);
sumBalance.DefineField(new EntityField("Total", (THumCertLink.Balance + THumCertLink.Fee), AggregateFunction.Sum), 1);

IGroupByCollection groupByCertId = new GroupByCollection();
groupByCertId.Add(sumBalance[0]);

DerivedTableDefinition sumOfAllBalanceDTD = new DerivedTableDefinition(sumBalance, "TempTable", null, groupByCertId );

IDynamicRelation sumOfAllBalanceRelation = new DynamicRelation(sumOfAllBalanceDTD, JoinHint.Left, EntityType.TCertEntity, "tcert", 
(new EntityField(THumCertLinkFieldIndex.CertId.ToString(), "TempTable", typeof(int)) == TCert.CertId.SetObjectAlias("tcert")));

IRelationCollection relations = new RelationCollection();
relations.Add(sumOfAllBalanceRelation);

ResultsetFields resultSet = new ResultsetFields(2);
resultSet.DefineField(new EntityField("Total", "TempTable", typeof(decimal)), 0, "TotalBalance");
resultSet.DefineField(TCertFields.CertId.SetObjectAlias("tcert"), 1);

TypedListDAO.GetMultiAsDataTable(resultSet, 0, null, null, relations, false, null, 0, 0);