1 (edited by CDB 2020-02-09 12:14:28)

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.

On a clear disk you can seek forever

Re: How to include a calculated field in SQL

Hi,
I've added request date and rec_date (both parsed) to the sqlquery button and it seems to be working okay.
Have a look at the attached.
Derek.

Post's attachments

Attachment icon cdb partsrequest.zip 351.13 kb, 584 downloads since 2020-02-09 

Re: How to include a calculated field in SQL

Thank you Derek,

As always such a simple solution.

At least it helps me understand how MVD works internally for future reference.

On a clear disk you can seek forever