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] Limit the number of Rows per Excel Sheet

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Limit the number of Rows per Excel Sheet
 Login/Join
 
adFocus
Member
posted
I have query with large number of records and need to write each 65000 records in a new Sheet.

How can I do this?

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


WebFOCUS 7.6.8
Windows
Excel, HTML
 
Posts: 5 | Registered: January 20, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
Here's a quick and easy method using BYTOC:

-*-- Limit the number of rows per Excel worksheet

-SET &ROW_LIMIT = 15;

TABLE FILE EMPDATA
PRINT
COMPUTE ROW_COUNTER/D4 = IF ROW_COUNTER EQ &ROW_LIMIT THEN 1 ELSE ROW_COUNTER + 1; NOPRINT
COMPUTE WORKSHEET_COUNTER/D4 = IF ROW_COUNTER EQ 1 THEN WORKSHEET_COUNTER + 1 ELSE WORKSHEET_COUNTER; NOPRINT
SEG.LASTNAME

BY TOTAL WORKSHEET_COUNTER NOPRINT PAGE-BREAK
BY LASTNAME NOPRINT
BY FIRSTNAME NOPRINT

ON TABLE PCHOLD FORMAT EXL2K BYTOC
END
-RUN

You need "BY TOTAL" because the page-break field is a COMPUTE, not a DEFINE.


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
adFocus
Member
posted Hide Post
Thank you so much Francis. Worked great. Nice Thread


WebFOCUS 7.6.8
Windows
Excel, HTML
 
Posts: 5 | Registered: January 20, 2010Report This Post
iBeny
Gold member
posted Hide Post
Hi Francis,

This Worked like a charm when i limited the records from my SQL Passthru to 70K records, The resultset actually have more than 3Lacs and hence gives a Criticla Sort error,
=============================================
0 NUMBER OF RECORDS IN TABLE= 80000 LINES= 80000
0 ERROR AT OR NEAR LINE 176 IN PROCEDURE external_revenue_f
(FOC909) CRITICAL ERROR IN EXTERNAL SORT. RETURN CODE IS: 00000301
========================

Need Help?


Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
 
Posts: 64 | Location: Toronto, Ontario | Registered: May 15, 2014Report This Post
kg
Member
posted Hide Post
I am getting the same EXTERNAL SORT error. when i limit the number of records i am trying to pull this works, but when running for my entire data set, i get this error. does anyone know how to get around this?


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 2 | Registered: September 29, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
quote:
Limit the number of Rows per Excel Sheet

has nothing to do with EXTERNAL SORT error.

Because you say 'data set', I assume you are running this on an IBM mainframe.

If you're running this via a batch job, take a look at your JCL.This is what I had in some JCL back in 1998:

//FOCSORT  DD UNIT=PUBLIC,SPACE=(CYL,(500,75))
//S001WK01 DD UNIT=PUBLIC,SPACE=(CYL,(500,75))
//S001WK02 DD UNIT=PUBLIC,SPACE=(CYL,(500,75))
//S001WK03 DD UNIT=PUBLIC,SPACE=(CYL,(500,75))
//S001WK04 DD UNIT=PUBLIC,SPACE=(CYL,(500,75))
//S001WK05 DD UNIT=PUBLIC,SPACE=(CYL,(500,75))
//S001WK06 DD UNIT=PUBLIC,SPACE=(CYL,(500,75))
//FOC$HOLD DD UNIT=PUBLIC,SPACE=(CYL,(200,50))
//HOLD     DD UNIT=PUBLIC,SPACE=(CYL,(200,50))
//SAVE     DD UNIT=PUBLIC,SPACE=(CYL,(200,50))


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
  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] Limit the number of Rows per Excel Sheet

Copyright © 1996-2020 Information Builders