How to find distinct records in a table

manishKhare
Member Posts: 4
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.
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.
Tanks And Reagrds
Manish Khare.
Manish Khare.
0
Comments
-
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.0 -
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;
®obi
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯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.®obi
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions