I've searched the forums, the manuals, and the help file and I can't seem to get this down, so please don't yell at me to go search on this
I am querying an external database monthly and I want to append the results to an existing focus database each month. I ran my first month and created the master and .foc file with the following:
TABLE FILE ... ON TABLE HOLD AS PROCS FORMAT FOCUS INDEX ...
Then I ran my second month using a filedef and the same hold command. This just overwrites my file and does not append. FILEDEF PROCS DISK RAD_NQMBC/PROCS.FOC (APPEND TABLE FILE ... ON TABLE HOLD AS PROCS FORMAT FOCUS INDEX ...
Other posts have said that I need to use the MODIFY command, but I do not understand the syntax for this and cannot find it in any of the documentation. Could someone explain the syntax or point me to the correct document? Or help me correct the FILEDEF method? (I've already tried they Creating Reports with WF, Describing Data with WF, Developing Reporting applications, Dev Studio Help... ) Thanks! JenThis message has been edited. Last edited by: jjoyce,
WebFOCUS 7.7.2 Win2003 Excel, HTML, PDF
February 10, 2011, 04:46 PM
<FreSte>
Jjoyce,
MODIFY is what you need. If the HOLD-file has exactly the same structure as the file to append it to, it's not too complicated:
-* --- "Create initial file"
TABLE FILE CAR
SUM
DEALER_COST
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS COSTS FORMAT FOCUS
END
-RUN
-* --- "Create hold file with data to append"
TABLE FILE CAR
SUM
DEALER_COST
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'W GERMANY';
ON TABLE HOLD
END
-RUN
-* --- "Append data from HOLD file to (initial)FOCUS file"
MODIFY FILE COSTS
FIXFORM FROM HOLD
DATA ON HOLD
END
-RUN
-* --- "check result"
TABLE FILE COSTS
PRINT *
END
or you can specify the MODIFY part like:
-* --- "Append data from HOLD file to FOCUS file"
MODIFY FILE COSTS
FIXFORM FROM HOLD
MATCH COUNTRY
ON MATCH CONTINUE
ON NOMATCH INCLUDE
MATCH CAR
ON MATCH UPDATE DEALER_COST
ON NOMATCH INCLUDE
DATA ON HOLD
END
-RUN
Modify cannot be used for flat files. But if the logic is more complex and/or you are dealing with large data-volumes, I recommend the use of dataMigrator (Information Builders ETL product). It will give you much benifits, like logging, scheduling and more. Check the site for more info.
(You'll probably get people screaming about licensing of MODIFY in a WebFOCUS world - it seems a little vague).
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 10, 2011, 05:05 PM
jjoyce
Thanks Fred. In this case, I think the first modify would meet my needs because I'm working with activity data that will not be modified.
In your second modify example
quote:
MATCH COUNTRY ON MATCH CONTINUE ON NOMATCH INCLUDE MATCH CAR ON MATCH UPDATE DEALER_COST ON NOMATCH INCLUDE
Am I reading this correctly? if hold.country does not already exist in costs, it will add the row. Otherwise, if hold.car doesn't already exist in costs, it will add the row. Otherwise, it will update costs.dealer_cost with the value of hold.dealer_cost.
We do have DataMigrator as well but I haven't used it yet. I might investigate using it for this project.
And Thanks Francis... I almost started going through the FOCUS manuals but when I saw "mainframe" I thought that cannot possibley pertain to ME!
WebFOCUS 7.7.2 Win2003 Excel, HTML, PDF
February 10, 2011, 05:59 PM
<FreSte>
On second thought ... the above mentioned MODIFY should be (because it's a single segment-file):
-* --- "Append data from HOLD file to FOCUS file"
MODIFY FILE COSTS
FIXFORM FROM HOLD
MATCH COUNTRY CAR
ON MATCH UPDATE DEALER_COST
ON NOMATCH INCLUDE
DATA ON HOLD
END
-RUN
MODIFY FILE COSTS
FIXFORM FROM HOLD
MATCH COUNTRY
ON MATCH CONTINUE
ON NOMATCH INCLUDE
MATCH CAR
ON MATCH UPDATE DEALER_COST
ON NOMATCH INCLUDE
DATA ON HOLD
END
-RUN
You can read this like:
SEG01 is the parent of SEG02. If COUNTRY doesn't exist in SEG01, include the record It is does, continue with checking if CAR exists in SEG02. If it doesn't, include record in SEG02 If it does exist, update DEALER_COST in SEG02.
-Fred-
February 10, 2011, 06:50 PM
N.Selph
I once printed out a copy of the WebFOCUS Modify Reference, which is really old but still there: WebFOCUS Modify Reference
That document is old indeed, but it is still valid. Modify is one of the parts of the product that will not evolve any more, it is so to speak 'functionally stabilized', meaning no more effort will be put in to enhance the tool. As far as I know there is no extra licensing costs involved in using modify. There may be costs related to the database adapter you're modifying against, but not for the tool itself.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
February 11, 2011, 12:16 PM
jjoyce
Thank you all- this is very helpful and informative! I feel much better now No more yelling at my PC