bug in FETCH maths wizard

Solved a problem in an elegant manner and want to show off your code? Know a hard-to-find feature? Post it here for the benefit of others. Questions don't belong here.
Post Reply
steve
Posts: 397
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 83 times

bug in FETCH maths wizard

Post by steve » Thu Oct 02, 2014 11:58 am

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:
Image
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 ""

Image

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} + " )" ) 

Post Reply