Focal Point Banner


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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     When a MODIFY fails a subsequent MODIFY doesn't update

Read-Only Read-Only Topic
Go
Search
Notify
Tools
When a MODIFY fails a subsequent MODIFY doesn't update
 Login/Join
 
Virtuoso
posted
Following is a REPRO of a problem I had:
  
-SET &ECHO=ALL;
-* File modprob.fex
-DEFAULT &DB=SQLMSS
-*APP HOLD BASEAPP 
-*TABLE FILE CAR
-*SUM SALES RETAIL_COST DEALER_COST
-*BY CAR BY MODEL
-*WHERE MODEL OMITS '2000'
-*ON TABLE HOLD AS MODPROB FORMAT &DB
-*END
-*-RUN
-*EX -LINES 2 EDAPUT ACCESS,MODPROB,C,FILE
-*SEGNAME=SEG01,  TABLENAME=MODPROB, KEYS=0, WRITE=YES, $
-*-EXIT
-* 2
TABLE FILE CAR
SUM SALES RETAIL_COST 
-*DEALER_COST 
COMPUTE DEALER_COST/I2=2; 
BY CAR BY MODEL
WHERE MODEL CONTAINS 'DOOR'
ON TABLE HOLD AS MODINP
END
-RUN
-* 3
MODIFY FILE MODPROB
FIXFORM FROM MODINP
MATCH *
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA ON MODINP
END
-RUN
-* 4
TABLE FILE CAR
SUM SALES RETAIL_COST DEALER_COST 
BY CAR BY MODEL
WHERE MODEL CONTAINS 'AUTO'
ON TABLE HOLD AS MODINP
END
-RUN
-* 5
MODIFY FILE MODPROB
FIXFORM FROM MODINP
MATCH *
ON NOMATCH INCLUDE
ON MATCH INCLUDE
DATA ON MODINP
END

Notes:
1. Create a database table. This is done once, then the ACX file is replaced for KEYS=0 and the KEYS in the database are deleted.
2. Create input to the table with a field having a wrong format.
3. When updating the table, you get an error.
4. Create a "good" input.
5. When updating the table, all records are rejected.
See ECHO:
  
 -* 2
 TABLE FILE CAR
 SUM SALES RETAIL_COST
 -*DEALER_COST
 COMPUTE DEALER_COST/I2=2;
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'DOOR'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 -* 3
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 ERROR AT OR NEAR LINE     27  IN PROCEDURE modprob FOCEXEC *
 (FOC437) THE FORMAT USED WITH FIXFORM IS INCOMPATIBLE WITH THE DATA FORMAT
 BYPASSING TO END OF COMMAND
 -* 4
 TABLE FILE CAR
 SUM SALES RETAIL_COST DEALER_COST
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'AUTO'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        8  LINES=      8
 -* 5
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =     8  ACCEPTED=     0  REJECTED=     8
 SEGMENTS:             INPUT =     0  UPDATED =     0  DELETED =     0


However when all inputs are "good" then it works:
  
 -* 2
 TABLE FILE CAR
 SUM SALES RETAIL_COST
 DEALER_COST
 -*COMPUTE DEALER_COST/I2=2;
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'DOOR'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=       12  LINES=     12
 -* 3
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =    12  ACCEPTED=    12  REJECTED=     0
 SEGMENTS:             INPUT =    12  UPDATED =     0  DELETED =     0
 -* 4
 TABLE FILE CAR
 SUM SALES RETAIL_COST DEALER_COST
 BY CAR BY MODEL
 WHERE MODEL CONTAINS 'AUTO'
 ON TABLE HOLD AS MODINP
 END
 -RUN
 0 NUMBER OF RECORDS IN TABLE=        8  LINES=      8
 -* 5
 MODIFY FILE MODPROB
 FIXFORM FROM MODINP
 MATCH *
 ON NOMATCH INCLUDE
 ON MATCH INCLUDE
 DATA ON MODINP
 END
 -RUN
 0 WARNING.. ON MATCH INCLUDE INPUTS DUPLICATE SEGMENTS
 0 TRANSACTIONS:         TOTAL =     8  ACCEPTED=     8  REJECTED=     0
 SEGMENTS:             INPUT =     8  UPDATED =     0  DELETED =     0


Anybody has an idea how to circumvent this problem?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
[maybe]Use a different HOLD file name for each step.[/maybe]


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, 2005Report This Post
Virtuoso
posted Hide Post
Francis,
No such luck...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
Danny - My first hunch is that after the "bad" transaction, your database or database connection is left in some sort of inconsistent state (and yes I know that's very vague).

What is your AUTOCOMMIT setting on your database connection? You could try doing something like this:
  
SQL SQLMSS SET AUTOCOMMIT     ON FIN;

-*  Do your 1st Modify
-*  Check &FOCERRNUM.  If 0 proceed to your next MODIFY.  Otherwise do a rollback:
SQL SQLMSS
ROLLBACK [WORK] ;
END
-*  Next, do you want to do your 2nd MODIFY even if the first fails?  If so, go ahead.
-*  Finally, when you're all done, do a commit:
SQL SQLMSS
COMMIT ;
END
 
Posts: 164 | Registered: March 26, 2003Report This Post
Expert
posted Hide Post
I assumed this is a FOCUS DB. Perhaps you can try WebFOCUS COMMIT within the MODIFY.

MODIFY FILE NXMBRRSE
 COMMIT 1
 FIXFORM FROM TBRRDTAY
  MATCH USERID
   ON NOMATCH REJECT
   ON MATCH UPDATE &UPDATEFIELD DATE_CHANGED
 DATA ON TBRRDTAY
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, 2005Report This Post
Virtuoso
posted Hide Post
Francis,
SET SHADOW=ON
and use of COMMIT...
nope!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Eric,
No luck either...


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
quote:
[maybe]Use a different HOLD file name for each step.[/maybe]

...or toss in a CHECK FILE and ?FF right after creating each Hold file.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Maybe add a COMPUTE after the FIXFORM in each MODIFY?

FIXFORM FROM MODINP
COMPUTE DEALER_COST/D7 = DEALER_COST ;


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
Its been a while, but

Instead of issuing COMMIT 1, use ON MATCH COMMIT or Call a subroutine with a COMMIT on MATCH.

I seem to remember doing this in the 80's


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
If your first MODIFY fails because of the wrong format you could just set a flag. Use that to loop back to the code that recreates the database and from there to the second MODIFY - assuming you are always creating a new database each time.

But assuming you want the data from both passes why not just use a DEFINE to reformat the problematic DEALER COST right from the get go, which would ensure you don't have any incorrect data types at the MODIFY stage.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     When a MODIFY fails a subsequent MODIFY doesn't update

Copyright © 1996-2020 Information Builders