Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] MODIFY FILE ON MATCH nulls

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] MODIFY FILE ON MATCH nulls
 Login/Join
 
Gold member
posted
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.

thanks all

Bob V

This message has been edited. Last edited by: BobV,


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
Virtuoso
posted Hide Post
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).


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
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 ...
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

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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
Bob,

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.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Gold member
posted Hide Post
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.

Here is the post:
http://forums.informationbuild...871088331#2871088331

Finally, the first thing I did was edit the MFD to MISSING=ON for each attribute. Alone this did not work, but perhaps it's needed in addition to the COMPUTE code I am now using.

thanks again to all.

BobV

This message has been edited. Last edited by: BobV,


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
Gold member
posted Hide Post
SOLVED


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] MODIFY FILE ON MATCH nulls

Copyright © 1996-2020 Information Builders