Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Appending data to a focus database
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Appending data to a focus database
 Login/Join
 
Gold member
posted
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 Frowner

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,


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
<FreSte>
posted
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-
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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).


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
<FreSte>
posted
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-
 
Reply With QuoteReport This Post
Guru
posted Hide Post
I once printed out a copy of the WebFOCUS Modify Reference, which is really old but still there:
WebFOCUS Modify Reference


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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 7 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Thank you all- this is very helpful and informative! I feel much better now Smiler No more yelling at my PC


WebFOCUS 7.7.2
Win2003
Excel, HTML, PDF
 
Posts: 61 | Registered: April 16, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Appending data to a focus database

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.