How to find distinct records in a table

manishKhare · 2004-12-14

hi all,
As we can write a query in oracle to find number of distinct records in a table...how to find it in navision? i want to count distinct records.please help me.

Arhontis · 2004-12-14

Hi,

To get the count of distinct records in a table, set the appropriate key, perfrom setranges and use COUNT or COUNTAPROX function of the table.

RobertMo · 2004-12-19

well, SETRANGE and COUNT won't help much since he's trying to count distinct records not the records that have one or more fields common.

What you can do is to set the appropriate key - it should include exactly the fields you are trying to count distinction for (let's say A and

. Then you should run a "Navision-classic" loop IF-FIND-THEN-REPEAT-UNTIL-NEXT.
Within a loop you increase counter whenever your fields A or B change.

The code could look like:

rMyRec.SETCURRENTKEY(A,B);
IF rMyRec.FIND('-') THEN REPEAT
  IF (rPreviousRec.A <> rMyRec.A) OR (rPreviousRec.B <> rMyRec.B) THEN BEGIN
    iMyDistinctCount := iMyDistinctCount + 1;
  END;
  rPreviousRec := rMyRec;
UNTIL rMyRec.NEXT=0;

RobertMo · 2004-12-19

if you expect a small number of distinct records compared to all records (<1/10) you could try to improving the loop by using filters like:

rMyRec.SETCURRENTKEY(A,B);
IF rMyRec.FIND('-') THEN REPEAT
  iMyDistinctCount := iMyDistinctCount + 1;
  rMyRec.SETRANGE(A,rMyRec.A);
  rMyRec.SETRANGE(B,rMyRec.B);
  rMyRec.FIND('+');
  rMyRec.SETRANGE(A);
  rMyRec.SETRANGE(B);
UNTIL rMyRec.NEXT=0;

But beware that although this code appears to retrieve less records than previous code and should be faster, Navision is optimized for IF-FIND-THEN-REPEAT-UNTIL-NEXT thus the first code is extremely fast.
Try both and than decide.

How to find distinct records in a table

Comments

Categories