Pages

Friday, February 13, 2015

Select Count Distinct

The Select statement doesn't have a way to let you do a count of distinct records.

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());
...