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...

 
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.,

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

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.

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

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

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.

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

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.,

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

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.

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.

Bear in mind that Focus generally does not reclaim the disk space occupied by deleted segment instances.

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.

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.