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,

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.

Thank you so much Francis. Worked great.

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?

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?

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))