Topic: How to include a calculated field in SQL
I'm a little stuck, I have a database which has some calculated fields for dates.
I have written a SQL query and want to reference the calculated field that I have created in MVD.
My calculated fields cfDateRec and cfDateReq (which work when I let MVD do all the work) is:
strftime('%d ', rec_date)||(substr("--JanFebMarAprMayJunJulAugSepOctNovDec",strftime('%m', rec_date)*3,3)|| strftime(' %Y', rec_date))
.
The SQL I have so far in the search function is.
SELECT
a.tech,
b.sup_name,
c.part,
c.requestdate,
c.rec_date
FROM
techname a, supplier b, requests c
WHERE
c.id_techname = a.id AND
c.id_supplier = b.id AND
((a.tech LIKE '%{edtSearch}%') OR
(b.sup_name LIKE '%{edtSearch}%') OR
(c.part LIKE '%{edtSearch}%'));
I am hoping to parse c.requestdate and c.rec_date through the above Pascal calculated field.
I have tried letting MVD to do all the work using the 'Search' action from a button, but this fails as it keeps inserting a '.' in the WHERE clause which makes the whole thing fail.
I can post the project if that helps.