Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Limit the number of Rows per Excel Sheet
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Limit the number of Rows per Excel Sheet
 Login/Join
 
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, 2010Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
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: 60 | Location: Toronto, Ontario | Registered: May 15, 2014Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

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-2018 Information Builders, leaders in enterprise business intelligence.