addAttributeRangeToQuery duplicates records in my result

Question

I am joining GeneralJournalEntry and GeneralJournalAccountEntry in the first part of my query. I am hard coding the MainAccount for now as i am not sure on how to properly use DimensionProvider class.

query = new Query();

qbdsGeneralJournalAccountEntry = query.addDataSource(tableNum(GeneralJournalAccountEntry));
qbr1 = qbdsGeneralJournalAccountEntry.addRange(fieldNum(GeneralJournalAccountEntry, LedgerAccount));
qbr1.value(queryValue('111111') + '*');

qbdsGeneralJournalEntry = qbdsGeneralJournalAccountEntry.addDataSource(tableNum(GeneralJournalEntry));
qbdsGeneralJournalEntry.relations(true);
qbdsGeneralJournalEntry.joinMode(JoinMode::InnerJoin);


if (_fromDate || _toDate)
{
    qbdsGeneralJournalEntry.addRange(fieldnum(GeneralJournalEntry, AccountingDate)).value(queryRange(_fromDate, _toDate));
    qbdsGeneralJournalEntry.addSortIndex(indexNum(GeneralJournalEntry, LedgerAccountingDateIdx));
    qbdsGeneralJournalEntry.indexIsHint(true);
}



dimensionProvider = new DimensionProvider();
dimensionProvider.addAttributeRangeToQuery(
                     query
                   , qbdsGeneralJournalAccountEntry.name()
                   , fieldStr(GeneralJournalAccountEntr, LedgerDimension)
                   , DimensionComponent::DimensionAttribute
                   , _costCenterNumber
                   , "CostCenter");

All my lines are being duplicated.. i think i am adding the GeneralJournalAccountEntry datasource again and that's why i have this behavior.

I can check the cost center value inside the while qr.next section but.. is there a way to do it right using the above method: addAttributeRangeToQuery?

score 1 · Accepted Answer · answered Oct 11 '16 at 21:14

Ok, after a lot of trying and searching and again trying i got to this:

select firstOnly dimAttr where dimAttr.BackingEntityType == tableNum(DimAttributeOMCostCenter);
dimAttrNameCostCenter = dimAttr.Name;

query = new Query();


qbdsGeneralJournalEntry = query.addDataSource(tableNum(GeneralJournalEntry));

qbdsGeneralJournalAccountEntry = qbdsGeneralJournalEntry.addDataSource(tableNum(GeneralJournalAccountEntry));
qbdsGeneralJournalAccountEntry.relations(true);
qbdsGeneralJournalAccountEntry.joinMode(JoinMode::InnerJoin);

qbr1 = qbdsGeneralJournalAccountEntry.addRange(fieldNum(GeneralJournalAccountEntry, LedgerAccount));
qbr1.value(queryValue('111111') + '*');


if (_fromDate || _toDate)
{
    qbdsGeneralJournalEntry.addRange(fieldnum(GeneralJournalEntry, AccountingDate)).value(queryRange(_fromDate, _toDate));
    qbdsGeneralJournalEntry.addSortIndex(indexNum(GeneralJournalEntry, LedgerAccountingDateIdx));
    qbdsGeneralJournalEntry.indexIsHint(true);
}


dimensionProvider = new DimensionProvider();

dimensionProvider.addAttributeRangeToQuery(query
                                        , qbdsGeneralJournalAccountEntry.name()
                                        , fieldStr(GeneralJournalAccountEntry, LedgerDimension)
                                        , DimensionComponent::DimensionAttribute
                                        , _costCenterNumber
                                        , dimAttrNameCostCenter
                                        , false);

It works now!! I've just switched the dataSources:

First add GeneralJournalEntry
Add GeneralJournalAccountEntry and join it with its "mother/father/header" table or whatever it is :) , namely qbdsGeneralJournalEntry.

So, no twins anymore, no duplicates. I am still wondering why that inner join is behaving like this..

Tomorrow i will try to use addAttributeRangeToQuery for the MainAccount too, and get rid of that pretty ugly range on LedgerAccount.

addAttributeRangeToQuery duplicates records in my result

1 Answers1