Hi
I have to convert the SQL code:
SELECT di1.nmi,
di2.nmi,
count(*)
FROM slim.detailed_inventory di1, slim.detailed_inventory di2
WHERE di1.slot_sun = di2.slot_sun
AND TO_DATE ('01/03/2008', 'dd/mm/yyyy') BETWEEN di1.date_fr AND NVL (di1.date_to,SYSDATE + 1 )
AND TO_DATE ('31/08/2009 ', 'dd/mm/yyyy') BETWEEN di2.date_fr AND NVL (di2.date_to,SYSDATE + 1 )
AND (di1.nmi in (select nmi from slim.nmi_allocation where contest_fl='Y' and euc_cd ='BCC') AND di2.nmi in (select nmi from slim.nmi_allocation where contest_fl='Y' and euc_cd ='BCC'))
AND di1.nmi <> di2.nmi
group by di1.nmi, di2.nmi
order by di1.nmi, di2.nmi;
into LLBLGen using Self Service.
I set up the attached code:
EntityRelation selfRelation = new EntityRelation(DetailedInventoryFields.SlotSun, DetailedInventoryFields.SlotSun, RelationType.OneToMany);
RelationCollection rcCount = new RelationCollection();
rcCount.Add(selfRelation, "a");
ResultsetFields fieldsCC = new ResultsetFields(3);
fieldsCC.DefineField(DetailedInventoryFields.Nmi, 0);
fieldsCC.DefineField(DetailedInventoryFields.Nmi.SetObjectAlias("a"), 1);
fieldsCC.DefineField(DetailedInventoryFields.Nmi, 2, "Count", AggregateFunction.Count);
Filter.Clear();
EntityField nvlDateTo1 = DetailedInventoryFields.DateTo;
nvlDateTo1.SetExpression(new DbFunctionCall("NVL", new object[] { DetailedInventoryFields.DateTo, System.DateTime.Now.AddDays(1.0) }));
Filter.Add(DetailedInventoryFields.DateFr <= dateFr);
Filter.AddWithAnd(nvlDateTo1 > dateFr);
EntityField nvlDateTo2 = DetailedInventoryFields.DateTo.SetObjectAlias("a");
nvlDateTo2.SetExpression(new DbFunctionCall("NVL", new object[] { DetailedInventoryFields.DateTo.SetObjectAlias("a"), System.DateTime.Now.AddDays(1.0) }));
Filter.AddWithAnd(DetailedInventoryFields.DateFr.SetObjectAlias("a") <= dateTo);
Filter.AddWithAnd(nvlDateTo2 > dateTo);
Filter.AddWithAnd(new FieldCompareSetPredicate(DetailedInventoryFields.Nmi, NmiAllocationFields.Nmi, SetOperator.In, (NmiAllocationFields.ContestFl == "Y" & NmiAllocationFields.EucCd == endUserCd)));
Filter.AddWithAnd(new FieldCompareSetPredicate(DetailedInventoryFields.Nmi.SetObjectAlias("a"), NmiAllocationFields.Nmi, SetOperator.In, (NmiAllocationFields.ContestFl == "Y" & NmiAllocationFields.EucCd == endUserCd)));
IPredicateExpression Filter1 = new PredicateExpression();
Filter1.Add(DetailedInventoryFields.EucCd != DetailedInventoryFields.EucCd.SetObjectAlias("a")).AddWithOr(DetailedInventoryFields.Nmi != DetailedInventoryFields.Nmi.SetObjectAlias("a")).AddWithOr(DetailedInventoryFields.DevTypeId != DetailedInventoryFields.DevTypeId.SetObjectAlias("a")).AddWithOr(DetailedInventoryFields.SlRateCd != DetailedInventoryFields.SlRateCd.SetObjectAlias("a")).AddWithOr(DetailedInventoryFields.ControlUsageTypeId != DetailedInventoryFields.ControlUsageTypeId.SetObjectAlias("a"));
Filter.AddWithAnd(Filter1);
// sort and group data
ISortExpression sort = new SortExpression();
sort.Add(DetailedInventoryFields.Nmi | SortOperator.Ascending);
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
sort.Add(DetailedInventoryFields.Nmi.SetObjectAlias("a") | SortOperator.Ascending);
groupBy.Add(fields[1]);
DataTable dtChanged = new DataTable();
TypedListDAO daoC = new TypedListDAO();
daoC.GetMultiAsDataTable(fieldsCC, dtChanged, 10, null, Filter, rcCount, false, groupBy, null, 0, 0);
The purpose of the code is to produce a report that compares data in the table to previous entries in the table.
I get an error on the line
fieldsCC.DefineField(DetailedInventoryFields.Nmi.SetObjectAlias("a"), 1);
saying that the field is already added.
Can you tell me what I am doing wrong.
thanks
