As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.
Join the TIBCO Community TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.
From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
Request access to the private WebFOCUS User Group (login required) to network with fellow members.
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.
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.,
WebFOCUS App Studio 8.2.02 Windows 7, All Outputs
Posts: 141 | Location: Mclean, VA | Registered: December 04, 2012
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
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
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, 2012
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
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, 2012
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, 2012
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, 2012
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