bug in FETCH maths wizard

[steve]

When adding a FETCH statement to a maths script that requires you to test against an existing database field, the format of the command that is produced by the wizard is incorrect and will produce code that doesn't work.
For example, if you wish to count the number of units of WIP on a file logged after a certain date, you would enter info into the FETCH wizard something like:

This would make the following faulty code:

Code: Select all

FETCH("TOTAL","History","cost_no_units","FOR EACH History NO-LOCK WHERE History.case_no = '" + {case.key} + "' AND (completed_date >= {Accident Dte.Date} )")

The issue is that the wizard encapsulates the test on the database field in quotation marks, so the database value is not substitued.
The solution is to re-arrange the quotation marks so that the field is not treated as a string. Examine the correct code below and you will see that we have ended the quotation marks before the date field, then continued to add the date field using the + symbol, finally adding the parenthesis back in with a further + and set of ""



corrected code:

Code: Select all

FETCH( "TOTAL" , "History" , "cost_no_units" , "FOR EACH History NO-LOCK WHERE History.case_no = '" + {case.key} + "' AND (completed_date >= " + {Accident Dte.Date} + " )" )