Focal Point
[closed] MODIFY syntax help

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2747071576

March 17, 2015, 10:30 AM
bug
[closed] MODIFY syntax help
Here is what I'd like to do:

Modify DB_TABLE_ABC from TEMP_INPUT, match KEY1 and KEY2, insert or update COL1; set MOD_TYPE='Updated' if there is a match in the table, set MOD_TYPE='Inserted' if there is no match and insert the record.

What is the correct syntax?



TABLE FILE TEMP_ABC
PRINT 
KEY1
KEY2
COL1
ON TABLE HOLD AS TEMP_INPUT FORMAT ALPHA
END


MODIFY DB_TABLE_ABC
FIXFORM FROM TEMP_INPUT
MATCH KEY1 KEY2
ON MATCH   UPDATE  COL1 MOD_TYPE='Updated'
ON NOMATCH INCLUDE COL2 MOD_TYPE='Inerted'
DATA ON TEMP_INPUT
END
-RUN




Thanks!

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


7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
March 17, 2015, 10:34 AM
Alan B
Try this approach:
MODIFY DB_TABLE_ABC
FIXFORM FROM TEMP_INPUT
MATCH KEY1 KEY2
ON NOMATCH COMPUTE MOD_TYPE='Inserted';
ON NOMATCH INCLUDE 
ON MATCH   COMPUTE MOD_TYPE='Updated';
ON MATCH   UPDATE  COL1 MOD_TYPE
DATA ON TEMP_INPUT
END



Alan.
WF 7.705/8.007
March 17, 2015, 04:15 PM
bug
This works! Thank you so much Alan!

quote:
Originally posted by Alan B:
Try this approach:
MODIFY DB_TABLE_ABC
FIXFORM FROM TEMP_INPUT
MATCH KEY1 KEY2
ON NOMATCH COMPUTE MOD_TYPE='Inserted';
ON NOMATCH INCLUDE 
ON MATCH   COMPUTE MOD_TYPE='Updated';
ON MATCH   UPDATE  COL1 MOD_TYPE
DATA ON TEMP_INPUT
END



7.66 and 7.704
System: Windows / AIX / Linux
Output: Mostly HTML, with some PDF, Excel and Lotus(!)
March 17, 2015, 05:45 PM
Waz
There are still MODIFY manuals on the documentation site, but they are a little difficule to find.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!