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]Deleting all records from a focus file using modify
Go
New
Search
Notify
Tools
Reply
  
[Solved]Deleting all records from a focus file using modify
 Login/Join
 
Platinum Member
posted
I've kind of run into a wall with this one. I'm trying to delete all records from a focus file using a temp table as the input.

Don't run this example, pretty sure it will delete all car file data... Eeker
 
TABLE FILE CAR
PRINT MODEL
ON TABLE HOLD AS HOLD_CARS
END
-RUN


MODIFY FILE CAR
FIXFORM FROM HOLD_CARS
MATCH MODEL
ON MATCH DELETE
DATA ON HOLD_CARS
END

 


Something like this, basically take all the data from hold_cars and try to match it against the car file and delete that record if the match is found. Am I missing something? I've done this transactionally before with freeform but can't seem to get fixform from a file working correctly.

This message has been edited. Last edited by: Tim P.,


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Couldn't you just issue a CREATE FILE CAR to truncate the car file?

You can always recreate car file using loadcar.fex in ibi/apps/ibisamp


WebFOCUS 8206, Unix, Windows
 
Posts: 1849 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Well I don't want to truncate the entirety of the car file, the initial table call would have some criteria on it based on user input.

I wasn't aware that command existed though, my knowledge base is a bit limited on some commands.


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
Tim, the CAR file is a multi-segment FOCUS database, you need to match the parents (COUNTRY, CAR) before matching the child (MODEL).

TABLE FILE CAR
PRINT 
COUNTRY
CAR
MODEL
ON TABLE HOLD AS HOLD_CARS
END
-RUN


MODIFY FILE CAR
FIXFORM FROM HOLD_CARS
MATCH COUNTRY
MATCH CAR
MATCH MODEL
-*ON MATCH DELETE
ON MATCH TYPE "Matched!"
DATA ON HOLD_CARS
END


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
Expert
posted Hide Post
The MODIFY code that doesn't rely on default behaviour:

MODIFY FILE CAR
FIXFORM FROM HOLD_CARS
MATCH COUNTRY
  ON MATCH CONTINUE
  ON NOMATCH REJECT
MATCH CAR
  ON MATCH CONTINUE
  ON NOMATCH REJECT
MATCH MODEL
-*  ON MATCH DELETE
  ON MATCH TYPE "MATCHED!"
  ON NOMATCH REJECT
DATA ON HOLD_CARS
END


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
Platinum Member
posted Hide Post
quote:
Originally posted by Francis Mariani:
The MODIFY code that doesn't rely on default behaviour:

MODIFY FILE CAR
FIXFORM FROM HOLD_CARS
MATCH COUNTRY
  ON MATCH CONTINUE
  ON NOMATCH REJECT
MATCH CAR
  ON MATCH CONTINUE
  ON NOMATCH REJECT
MATCH MODEL
-*  ON MATCH DELETE
  ON MATCH TYPE "MATCHED!"
  ON NOMATCH REJECT
DATA ON HOLD_CARS
END


I tried this thinking I had the syntax wrong but it seems to delete the first row from the table and then reject the rest for either being a dupl SEG1 or a nomatch.

It may also just being the echo but I'm noticing a '#' added to the front of the first match field.


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
Seems to work:

TABLE FILE CAR
PRINT 
COUNTRY
COMPUTE CAR1/A16 = IF CAR EQ 'DATSUN' THEN 'NISSAN' ELSE CAR; AS 'CAR'
COMPUTE MODEL1/A24 = IF CAR EQ 'BMW' AND MODEL EQ '2002 2 DOOR' THEN '2010 2 DOOR' ELSE MODEL; AS 'MODEL'
ON TABLE SET ASNAMES ON AND HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD_CARS
END
-RUN

MODIFY FILE CAR
FIXFORM FROM HOLD_CARS
MATCH COUNTRY
  ON MATCH CONTINUE
  ON NOMATCH REJECT
MATCH CAR
  ON MATCH CONTINUE
  ON NOMATCH REJECT
MATCH MODEL
-*  ON MATCH DELETE
  ON MATCH TYPE "MATCH - <COUNTRY - <CAR - <MODEL"
  ON NOMATCH TYPE "NOMATCH- <COUNTRY - <CAR - <MODEL"
DATA ON HOLD_CARS
END


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
Platinum Member
posted Hide Post
I analyzed the result from the car file and it seems I'm doing the same thing, it just isn't matching my fields. When I run mine, the first and last records match but the 3 records in between do not.

Weirder yet, if I take the value it says it doesn't have a match for and stick it into a filter on the table, I get a result. I gotta be doing something wrong. Throwing this error:

 (FOC444)TRANS     2 REJECTED  DUPL:  SEG1 

This message has been edited. Last edited by: Tim P.,


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Master
posted Hide Post
Doesn't the CREATE FILE before the MODIFY FILE wipe the data and inserts this one record?

CREATE FILE &FN
MODIFY FILE &FN
FREEFORM COL1 COL2 COL3
DATA
'Data1','Data2','Data3',$
END



- FOCUS Man, just FOCUS!
-----------------------------
Product: WebFOCUS
Version: 8.1.04
Server: Windows 2008 Server
 
Posts: 578 | Registered: October 01, 2014Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I don't have much experience with create file but I did find out what was wrong. When I was deleting the record, I wasn't taking into account the FOCLIST field. WF sees FOCLIST as it's "primary key" field in a permanent hold file and is created if there is no group by when you insert the records into the table. If you don't call it in modify it assumes a value and makes all the rows for FOCLIST the same, all of mine were 0.

I deleted the hold file and recreated it, this time with group by's in place and the FOCLIST field was not created and everything started working again. Since I have my own primary key I have no use for FOCLIST although I guess I could have used it, would of had to applied a value in the modify command for it though when the row was inserted.


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Tim,

If you just deleted the record at the COUNTRY level then all the subsequent lower level records will be gone also.

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD
END

MODIFY FILE CAR
FIXFORM FROM HOLD
MATCH COUNTRY
ON MATCH DELETE
etc....

Also CREATE FILE of course is the fastest and simplest way.


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Bear in mind that Focus generally does not reclaim the disk space occupied by deleted segment instances.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Vivian:
Tim,

If you just deleted the record at the COUNTRY level then all the subsequent lower level records will be gone also.

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD
END

MODIFY FILE CAR
FIXFORM FROM HOLD
MATCH COUNTRY
ON MATCH DELETE
etc....

Also CREATE FILE of course is the fastest and simplest way.


Correct, my issue as it turns out wasn't really with how modify was working but rather the fields that I was matching on. Because I was not referencing FOCLIST, the modify match was failing.

From what I can tell it looks like an XFOCUS file creates an internal primary key if you just print fields into a table, which is fine I can understand that. What I'm a bit confused on is even if you make your own primary key and FOCLIST is present in the focus file, why you can't use only your own primary key to match on.
Relationally I shouldn't make a difference as long as the primary key is unique.


WebFOCUS App Studio 8.2.02
Windows 7, All Outputs
 
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012Reply With QuoteReport This Post
Expert
posted Hide Post
FOCLIST is a column that gets generated when you use PRINT to create a FOCUS or XFOCUS database. It does not get generated when you use SUM. The reason is to ensure there's a unique index, which, with PRINT, may not occur.


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
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [Solved]Deleting all records from a focus file using modify

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