I have a web form where users can update data that is in a SQL table. Periodically, I plan to take an extract of that updated data. If someone changes the data, my update process works except if the data is removed (changed to null or ' '). Here's the code I am using to update my table NE16UPDT:
MODIFY FILE NE16UPDT FIXFORM FROM NE16FINA MATCH LP_ID ON MATCH UPDATE WRKSHP_DT_YY WRKSHP_DT_MM WRKSHP_DT_DD APPT_DT_YY APPT_DT_MM APPT_DT_DD ON NOMATCH INCLUDE DATA ON NE16FINA END
Once any one of these attributes has a value in the source, it updates NE16UPDT unless the value in the source (NE16FINA) is removed.
I was reading in a prior post that I might need to have MISSING=ON in my MFD for each attribute. Or perhaps there's some code or set commands in my fex that is needed.
Bob VThis message has been edited. Last edited by: BobV,
WF (App Studio) 8.2.01m / Windows Mainframe FOCUS 8
December 12, 2012, 02:32 AM
Bob, MODIFY is a thing from the past with sometimes an unexpected behaviour. As in this case, when the source values are removed. I am assuming the key value for LP_ID is still there in the file. What happens is: - the fixform reads the data values from the source - key value LP_ID is present and it matches that value in the database - there is a match, so the other fields need to be updated - but there is no value for any one of those fields, so nothing happens. You'll have to force a value to be present for the modify to update. Point is, that the fixform command is a so-called 'conditional' read. Meaning that the field gets activated only if a non-zero (or null) value is present. You could try to give the command ACTIVATE RETAIN WRKSHP_DT_YY WRKSHP_DT_MM WRKSHP_DT_DD APPT_DT_YY APPT_DT_MM APPT_DT_DD to force these field sto be activated. Or, if that does not have the desired result, you'll have to resort to using the compute command, thus activating the field(s).
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
December 12, 2012, 08:19 AM
Bob, True, MODIFY, has been around for a long time and it has its quirks. Nonetheless, it is very useful. For your problem, I would suggest you use an explicit FIXFORM:
MODIFY FILE NE16UPDT
FIXFORM LP_ID/fmt WRKSHP_DT_YY/fmt WRKSHP_DT_MM/fmt ...
ON MATCH UPDATE WRKSHP_DT_YY WRKSHP_DT_MM WRKSHP_DT_DD APPT_DT_YY APPT_DT_MM APPT_DT_DD
ON NOMATCH INCLUDE
DATA ON NE16FINA
where /fmt are the ACTUAL formats of each field.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
December 12, 2012, 08:45 AM
To get the answer you need, you will need to post the masters for NE16UPDT and NE16FINA.
MISSING is the same as NULL and a field attribute of MISSING=ON says NULL are allowed. If MISSING=ON is not present, the value will be zero or blank depending of the field format if no values are present.
We use Oracle and I don't have any problem using MODIFY to update Oracle fields where NULL is allowed with NULL.
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
December 12, 2012, 10:34 AM
Thanks to all who replied with advice.
GamP, I took your suggestion of using COMPUTE... in the MODIFY and searched Focal Point to find where to place ACTIVATE RETAIN (rather than bother you). I found a post with a reply from Francis Mariani the gave sample code for the compute that looked like this:
MODIFY FILE NE16UPDT FIXFORM FROM NE16FINA MATCH LP_ID ON MATCH COMPUTE WRKSHP_DT_YY = IF WRKSHP_DT_YY EQ '' THEN '' ELSE WRKSHP_DT_YY; ON NOMATCH INCLUDE DATA ON NE16FINA END
this worked. I also tested to see if MISSING would work and it did: ON MATCH COMPUTE WRKSHP_DT_YY MISSING ON = IF WRKSHP_DT_YY EQ MISSING THEN MISSING ELSE WRKSHP_DT_YY;
I did an ON MATCH COMPUTE for all 6 of the attributes I need to update. Not sure if I need to use ON MATCH before each COMPUTE, but it works w/o errors.