Today I found this neat way to do it anyway, in the method \Data Dictionary\Tables\LedgerJournalTable\Methods\numOfVouchers:
...
sql = 'select count(distinct %1) from %2 where %3 = %4 and %5 = %6 and %7 = %8';
sql = strFmt(sql,
ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Voucher)),
ReleaseUpdateDB::backendTableName(tableNum(LedgerJournalTrans)),
ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, JournalNum)),
sqlSystem.sqlLiteral(this.JournalNum),
ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, DataAreaId)),
sqlSystem.sqlLiteral(ledgerJournalTrans.DataAreaId),
ReleaseUpdateDB::backendFieldName(tableNum(LedgerJournalTrans), fieldNum(LedgerJournalTrans, Partition)),
getcurrentpartitionrecid());
...
3 comments:
It seems for the most part, you should get the same execution plan and performance using "GROUP BY", so the following example also work to achieve the same result:
static void CountingUsingGrouping(Args _args)
{
LedgerJournalTrans ledgerJournalTrans;
LedgerJournalId journalNum;
Voucher voucher;
// Some examples
journalNum = 'FJ-000004';
voucher = 'IF-000002';
select count(RecId) from ledgerJournalTrans
group by ledgerJournalTrans.Voucher, ledgerJournalTrans.JournalNum
where ledgerJournalTrans.Voucher == voucher
&& ledgerJournalTrans.JournalNum == journalNum;
info(int642str(ledgerJournalTrans.RecId));
}
Hi :)
I'm not totally agree...
I mean that:
A
A
B
C
B
count distinct = 3
Select count + group by
A 2
B 2
C 1
Different results.
I see what you mean, Manel. I still use GROUP BY when I want to Count, but you can use Count (distinct my_column) to achieve certain "distinct" results, but not so easily from X++. I try to avoid queries in strings because they can blow up at run-time.
More details on aggregation using Count distinct can be found here: http://www.sqlteam.com/article/how-to-use-group-by-with-distinct-aggregates-and-derived-tables
Post a Comment