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.
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;
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.
Comments
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.
Arhontis
https://forum.mibuso.com/search
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:
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
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.
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯