Translation of SQL statement to Navision syntax

idiot · 2007-10-26

eg
SELECT [Document No_], SUM([Cost Amount (Actual)]) DocTotal
FROM [Cronus$Value Entry]
WHERE ([Posting Date] = '2007-01-01')
GROUP BY [Document No_]

What is the best translation for such a statement into Navision c/side?
Required analysis is the least cost or fastest time.
This is a an ad-hoc based selection.

Is there any more efficient way of doing other than this:
1. select all distinct Document No. & insert into temp table
2. loop through the temp table & for each Document No., loop through the Value Entry table again & sum based on the Document No.

ara3n · 2007-10-26

valueentry.setrange("Posting Date",'whatever date');
if valueentry.findset then repeat
  tempvalueentry.setrange("Document No.",valueentry."Docment No.");
   if tempvalueentry.findfirst then begin
     tempvalueentry."Cost Amount (Actual)" += valueentry."Cost Amount (Actual)";
    tempvalueentry.modify;
end else begin
  tempvalueentry."entry No." := valueentry."Entry No.";
  tempvalueentry."Cost Amount (Actual)" := valueentry."Cost Amount (Actual)";
  tempvalueentry.insert; 
end;
until valueentry.next = 0;

cnicola · 2007-10-26

Hi Rashed,

I hope you do not mind if I suggest an improved version of the code:

valueentry.setrange("Posting Date",'whatever date');
if valueentry.findset then
repeat
tempvalueentry.setrange("Document No.",valueentry."Docment No.");
if not tempvalueentry.findfirst then
begin
tempvalueentry.init;
tempvalueentry."Document No." := valueentry."Document No.";
tempvalueentry.insert;
end;

tempvalueentry."Cost Amount (Actual)" += valueentry."Cost Amount (Actual)";
tempvalueentry.modify;

until valueentry.next = 0;

The reason I think this is better is the fact that in your example the code for processing the temp record once you had it was written twice. So if you wanted to add to the code later you would need to write same thing in both places.
This way you only have one place you write it in.

ara3n · 2007-10-26

thanks. the init should be there.

idiot · 2007-10-29

Thanks guys.
Guess this standard way is already the most efficient...

Translation of SQL statement to Navision syntax

Comments

Categories