Well, here's part trivia, part navel gazing, part bragging...but you can do
nested EVALUATE()s in a field expression within VFP SQL. And it can make
sense.
I was converting some old procedural code in VFP 7 to a SQL call where
there were multiple conditions for a person's age at a given event. The
prior code checked if the age was less than zero, make it zero (data entry
errors, not real life), if the person was 100 or over, use 99, if there's
no birthdate, indicate Unknown, otherwise calculate age.
There was a recent ProFox thread about dealing with either MAX() or MIN()
within SQL - it doesn't work. Well, the SQL MAX function works in VFP SQL
but not the VFP MAX function. I had a tip to the FPA a long time ago that
if you use the EVALUATE() function in VFP SQL, it would run the VFP MAX()
function and not the VFP SQL MAX() function and that was your workaround.
So with that I put this together for the calculation as a field expression
IIF(NOT EMPTY(<birth_date>), ;
PADL(EVALUATE("MAX(INT(EVALUATE([MIN(((<event_date> - <birth_date>) /
365.25), 99)])), 0)"), 2, [0]), [UK]) AS cAge
...where <> represents the appropriate date field in a table.
Not only does it work, it appears to be fast.
Bill Anderson
--- StripMime Report -- processed MIME parts ---
multipart/alternative
text/plain (text body -- kept)
text/html
---
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/ca+o1enhzhp36v8ttfsn9jgs5-vbcn7dhaht7rdy8-32xejo...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
