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! Jen

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

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.

Cheers,

-Fred-

MODIFY is described in the mainframe manuals, try FOCUS for Mainframe Maintaining Databases (covering MODIFY) 7.6 .

(You'll probably get people screaming about licensing of MODIFY in a WebFOCUS world - it seems a little vague).

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!

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

For a multi-segment'd FOCUS file, like

FILENAME=COSTS   , SUFFIX=FOC     , $
SEGMENT=SEG01, SEGTYPE=S1, $
  FIELDNAME=COUNTRY, ALIAS=E01, USAGE=A10, $
SEGMENT=SEG02, SEGTYPE=S1, PARENT=SEG01, $
  FIELDNAME=CAR, ALIAS=E03, USAGE=A16, $
  FIELDNAME=DEALER_COST, ALIAS=E04, USAGE=D7, $

the modify should be:

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-

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.

Thank you all- this is very helpful and informative! I feel much better now No more yelling at my PC