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     [SOLVED]Trouble with Append Part 2

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Trouble with Append Part 2
 Login/Join
 
Silver Member
posted
Back in July Tony A helped me come up with a solution to append data and rely upon the final HOLD to create a tab-delimited file.

His wisdom can be seen here:
http://forums.informationbuild...167022776#8167022776

His solution works great, and I've been using this method as a template for all of my files that need to be created in this manner. However, I've run into a new situation with a txt file where I can't quite make this method work.

Namely, I have to include a calculated total in my trailer record (TRAIL2 and TRAIL3 in my example below), and I can't quite figure out how to do that without breaking the append. I can get the header and content to append just fine, and I can get the trailer to append when I'm not trying to do any summing on numerical fields.

I've tried several different methods with no success, here is my latest failed attempt:

-DEFAULT &R_YR = 2015;
SET HOLDLIST = PRINTONLY
FILEDEF EOYFILE DISK 111111_test_file.txt (LRECL 21 RECFM V APPEND
-RUN
DEFINE FILE CAR
HEAD1/A4 = '&R_YR';
HEAD2/A7 = 'BBBBBBB';
HEAD3/A7 = 'CCCCCCC';
HEAD4/A3 = 'DDD';
HEADER/A21 = HEAD1|HEAD2|HEAD3|HEAD4;
CON1/A2 = 'EE';
CON2/A2 = 'FF';
CON3/A17 = 'GGGGGGGGGGGGGGGGG';
CONTENT/A21 = CON1|CON2|CON3;
TRAIL1/A2 = 'HH';
TRAIL2/I5L = 3;
TRAIL3/I9L = 1600;
TRAIL4/A5 = 'IIIII';
END
-* Getting the header record
TABLE FILE CAR
SUM HEADER
BY HIGHEST 1 COUNTRY NOPRINT
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END

-RUN
-* Appending the data
TABLE FILE CAR
PRINT CONTENT
BY COUNTRY NOPRINT
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END
-RUN
-* Calculating the sum of TRAIL2 and TRAIL3
TABLE FILE CAR
SUM
FST.TRAIL1
TRAIL2
TRAIL3
FST.TRAIL4
ON TABLE HOLD AS HOLD1
END
-* Appending the footer record
TABLE FILE HOLD1
SUM COMPUTE TRAILER/A21 = TRAIL1|FPRINT(TRAIL2,'I5L','A5')|FPRINT(TRAIL3,'I9L','A9')|TRAIL4;
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END
-RUN
TABLE FILE EOYFILE
PRINT *
ON TABLE PCHOLD FORMAT ALPHA
END
-RUN

Does anyone have any idea what I could do to get the desired results.

This message has been edited. Last edited by: rray9895,


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 47 | Registered: October 22, 2014Report This Post
Expert
posted Hide Post
I added MAX.COUNTRY NOPRINT to include a real column in the request - this is to avoid the message "(FOC493) NO ACCESSIBLE FIELDS ARE MENTIONED IN THE REQUEST". The fex then completes without error. This may solve the problem...

-DEFAULT &R_YR = 2015;
SET HOLDLIST = PRINTONLY
FILEDEF EOYFILE DISK 111111_test_file.txt (LRECL 21 RECFM V APPEND
-RUN
DEFINE FILE CAR
HEAD1/A4 = '&R_YR';
HEAD2/A7 = 'BBBBBBB';
HEAD3/A7 = 'CCCCCCC';
HEAD4/A3 = 'DDD';
HEADER/A21 = HEAD1|HEAD2|HEAD3|HEAD4;
CON1/A2 = 'EE';
CON2/A2 = 'FF';
CON3/A17 = 'GGGGGGGGGGGGGGGGG';
CONTENT/A21 = CON1|CON2|CON3;
TRAIL1/A2 = 'HH';
TRAIL2/I5L = 3;
TRAIL3/I9L = 1600;
TRAIL4/A5 = 'IIIII';
END
-* Getting the header record
TABLE FILE CAR
SUM HEADER
BY HIGHEST 1 COUNTRY NOPRINT
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END

-RUN
-* Appending the data
TABLE FILE CAR
PRINT CONTENT
BY COUNTRY NOPRINT
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END
-RUN
-* Calculating the sum of TRAIL2 and TRAIL3
TABLE FILE CAR
SUM
MAX.COUNTRY NOPRINT

FST.TRAIL1
TRAIL2
TRAIL3
FST.TRAIL4
ON TABLE HOLD AS HOLD1
END
-* Appending the footer record
TABLE FILE HOLD1
SUM COMPUTE TRAILER/A21 = TRAIL1|FPRINT(TRAIL2,'I5L','A5')|FPRINT(TRAIL3,'I9L','A9')|TRAIL4;
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END
-RUN
TABLE FILE EOYFILE
PRINT *
ON TABLE HOLD FORMAT ALPHA
END
-RUN


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
Silver Member
posted Hide Post
Of course!

Every time I've tried testing it, I always assumed that it was a field length issue,or a BY statement problem or that I wasn't using the FPRINT function correctly.

I didn't even think about isolating the calculated hold to see if there was an issue there. I guess it is very rare for me to deal with ONLY define files that I didn't even consider that. Thank you so much!


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 47 | Registered: October 22, 2014Report This Post
Silver Member
posted Hide Post
Actually, this method didn't fully work in my real data. It worked fine with the CAR file, but when I mirrored the logic in my actual file, it was a no-go, I'm not sure why. However, I made a small adjustment to it, and I got it to work this way:

-DEFAULT &R_YR = 2015;
SET HOLDLIST = PRINTONLY
FILEDEF EOYFILE DISK 111111_test_file.txt (LRECL 21 RECFM V APPEND
-RUN
DEFINE FILE CAR
HEAD1/A4 = '&R_YR';
HEAD2/A7 = 'BBBBBBB';
HEAD3/A7 = 'CCCCCCC';
HEAD4/A3 = 'DDD';
HEADER/A21 = HEAD1|HEAD2|HEAD3|HEAD4;
CON1/A2 = 'EE';
CON2/A2 = 'FF';
CON3/A17 = 'GGGGGGGGGGGGGGGGG';
CONTENT/A21 = CON1|CON2|CON3;
TRAIL1/A2 = 'HH';
TRAIL2/I5L = 3;
TRAIL3/I9L = 1600;
TRAIL4/A5 = 'IIIII';
END
-* Getting the header record
TABLE FILE CAR
SUM HEADER AS ''
BY HIGHEST 1 COUNTRY NOPRINT
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END
-RUN
-* Appending the data
TABLE FILE CAR
PRINT CONTENT AS ''
BY COUNTRY NOPRINT
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END
-RUN
-* Calculating the sum of TRAIL2 and TRAIL3
TABLE FILE CAR
SUM
MAX.COUNTRY
FST.TRAIL1
TRAIL2
TRAIL3
FST.TRAIL4
ON TABLE HOLD AS HOLD1
END

DEFINE FILE HOLD1
TRAILER/A21 = TRAIL1|FPRINT(TRAIL2,'I5L','A5')|FPRINT(TRAIL3,'I9L','A9')|TRAIL4;
END
-* Appending the footer record
TABLE FILE HOLD1
PRINT
TRAILER AS ''
BY COUNTRY NOPRINT
ON TABLE HOLD AS EOYFILE FORMAT ALPHA
END
-RUN
TABLE FILE EOYFILE
PRINT *
ON TABLE PCHOLD FORMAT ALPHA
END
-RUN


WebFOCUS 8.105M, Windows 10, App Studio
 
Posts: 47 | Registered: October 22, 2014Report This Post
Guru
posted Hide Post
I also had the same issue with appending a footer but I added the following line to limit time,

/*
WHERE READLIMIT EQ 1
/*


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Expert
posted Hide Post
FYI

When using this technique, FILEDEF APPEND and HOLD, HOLD, ETC, each time the HOLD happens, the master file will be replaced.


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

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Trouble with Append Part 2

Copyright © 1996-2020 Information Builders