As this is my first post in this forum i am happy to joining with you all.
I need to create a report by using Easytrieve Reporting tool. I have to extract data from DB2 system using SQL Join query and create report based on key field. Below given sample code i used:
Code:
*LIST OFF
PARM DB2SSID('DB2T') PRESIZE(2048) LIST (NOPARM NOFILE)
*
DEFINE WS_PAGE1 S 3 N 0 VALUE 1
DEFINE WS_CNT S 3 N 0 VALUE 1
*
DEFINE HS-ACOL1 W 03 A
DEFINE HS-ACOL2 W 33 A
DEFINE HS-CCOL1 W 01 A
DEFINE CPLUSB W 07 A
DEFINE CPLUSC W 18 A
DEFINE HS-CCOL6 W 33 A
*
SQL DECLARE C1 CURSOR FOR +
SELECT A.ACOL1, A.ACOL2, C.CCOL1, +
C.CCOL2 CONCAT +
C.CCOL3 CONCAT +
' ' CONCAT +
B.BCOL1 AS CPLUSB, +
C.CCOL4 CONCAT +
' ' CONCAT +
C.CCOL5 AS CPLUSC, +
C.CCOL6 +
FROM TABLEA A, +
TABLEB B, +
TABLEC C +
WHERE +
B.BCOL2 = C.CCOL2 AND +
A.ACOL1 = C.ACOL7 AND +
ORDER BY A.ACOL1, +
C.CCOL1, +
CPLUSB
*
FILE RPT1 PRINTER
*
JOB INPUT NULL
WS_PAGE1 = 1
WS_CNT = 1
*
SQL OPEN C1
DO WHILE SQLCODE NE +100
SQL FETCH C1 INTO +
:HS-ACOL1, +
:HS-ACOL2, +
:HS-CCOL1, +
:CPLUSB, +
:CPLUSC, +
:HS-CCOL6
*
IF SQLCODE EQ 0
PRINT RPT1
END-IF
*
IF SQLCODE EQ 100
SQL CLOSE C1
STOP
END-IF
*
IF SQLCODE NE 0 AND SQLCODE NE 100
SQL CLOSE C1
STOP
DISPLAY 'SQL ERROR:' SQLCODE
END-IF
END-DO
*
STOP
************************************************************
REPORT RPT1 PRINTER(RPT1) LINESIZE 132 NOADJUST NODATE NOHEADING -
TITLESKIP 0 NOPAGE SUMCTL NONE
*
SEQUENCE HS-ACOL1 HS-CCOL1 CPLUSB
CONTROL HS-ACOL1 NOPRINT HS-ACOL2 NOPRINT
TITLE 1 COL 01 ' '
TITLE 2 COL 01 ' '
TITLE 3 COL 02 '************************' -
COL 30 'MAIN TITLE' -
COL 107 '************************'
TITLE 4 COL 02 '************************' -
COL 107 '************************'
TITLE 5 COL 02 '************************' -
COL 107 '************************'
TITLE 6 COL 26 'REPORT NAME' -
COL 46 'MODULE NAME' -
COL 119 'PAGE:' -
COL 127 WS_PAGE1
TITLE 7 COL 42 'REPORT TITLE' -
COL 107 'DATE:' -
COL 113 SYSDATE -
COL 122 SYSTIME
TITLE 8 COL 01 ' '
TITLE 9 COL 01 ' '
TITLE 10 COL 01 ' '
TITLE 11 COL 07 'CCOL1 CPLUSB CPLUSC HS-CCOL6 '
TITLE 12 COL 06 '----- ------- -------- ---------'
LINE 01 COL 02 HS-ACOL1, -
COL 07 HS-ACOL2
LINE 02 COL 06 HS-CCOL1, -
COL 15 CPLUSB, -
COL 25 CPLUSC, -
COL 45 HS-CCOL6
*
AFTER-LINE. PROC
IF WS_CNT EQ 45
WS_CNT = 1
WS_PAGE1 = WS_PAGE1 + 1
DISPLAY
ELSE
WS_CNT = WS_CNT + 1
END-IF
END-PROC
************************ MAIN TITLE ************************
************************ ************************
************************ ************************
REPORT NAME MODULE NAME PAGE: 1
REPORT TITLE DATE: 7/03/15 9.50.49
CCOL1 CPLUSB CPLUSC HS-CCOL6
------ -------- -------- ---------
AC1 XXXXXXXXXXXXXXXXXXXXXXX
CC BC1 XX XXXX XXXXXXXXXXXXXXXXXXXX
CC BC1 XX XXXX XXXXXXXXXXXXXXXXXXXX
CC BC1 XX XXXX XXXXXXXXXXXXXXXXXXXX
CC BC1 XX XXXX XXXXXXXXXXXXXXXXXXXX
CC BC1 XX XXXX XXXXXXXXXXXXXXXXXXXX
AC3 XXXXXXXXXXXXXXXXXXXXXXX
B BC2 XX XXXX XXXXXXXXXXXXXXXXXXXX
B BC2 XX XXXX XXXXXXXXXXXXXXXXXXXX
B BC2 XX XXXX XXXXXXXXXXXXXXXXXXXX
AC4 XXXXXXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
1
************************ MAIN TITLE ************************
************************ ************************
************************ ************************
REPORT NAME MODULE NAME PAGE: 2
REPORT TITLE DATE: 7/03/15 9.50.49
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
1
************************ MAIN TITLE ************************
************************ ************************
************************ ************************
REPORT NAME MODULE NAME PAGE: 3
REPORT TITLE DATE: 7/03/15 9.50.49
AC5 XXXXXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
BB CB8 XX XXXX XXXXXXXXXXXXXXXXXXXX
AC6 XXXXXXXXXXXXXXXXX XXX
C CBG 99 XXXXXXX XXXXXXXXXXXXXXX
AC7 XXXXXXXXXXXXXXXXXXXX
B BCG 99 XXXXXXX XXXXXXXXXXXXXXXXX
AC8 XXXXXXXXXXXXXXXX
C CCG 88 XXXXXXX XXXXXXXXXXXXXXX
For every 'LINE 02' Details records Blank lines are inserted as 'LINE 01' fields are defined as CONTROL BREAK. Please help me to remove these Blank Lines for 'Line 02' records. I hope these Blank lines are related to 'Carriage Control' items.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Code:
DEFINE WS_PAGE1 S 3 N 0 VALUE 1
DEFINE WS_CNT S 3 N 0 VALUE 1
Why are these prefixed WS_ if they are S-type fields? That's confusing, but maybe your site-standard.
Code:
IF SQLCODE NE 0 AND SQLCODE NE 100
SQL CLOSE C1
STOP
DISPLAY 'SQL ERROR:' SQLCODE
END-IF
What are you trying to do here? The SQL ERROR from what, are you trying to output? It is after the STOP and it is after the CLOSE. Do you want the error of the CLOSE?
As to your request, presumably if you look at the details on the output listing from the compile you'll see DTLCTL FIRST. That's how DTLCTL works.
If you want the DTLCTL FIRST behaviour but without blanks for each subsequent line with the control data, you'd probably be best to do it in a BEFORE-LINE proc, or to put the control data into the tile, and NEWPAGE on it.
Anyway, decide what it is you want, look at the chapter on the REPORT statement and try to do what you want.
Keeping your own line-count and page-count is a bit of a fools' game, but there we go.
Hi Bill,
Thanks for reply.
There is no reason to put WS_ as prefix and as you said its my program standard.
And in DB2 error handling condition, i would like to know SQL Error code in spool so i have put like that.
Please help me on how to use DTLCTL FIRST in BEFORE-LINE proc as i don't want create new page for the report.
As per my requirement the detail record line limit will be 45 so that i have defined like that.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
For the DB2 error, 1) you do STOP before the DISPLAY so you'll never see the DISPLAY output and 2) you do the DISPLAY after another Easytrieve SQL statement, so I'm not sure you're getting the one you want.
Complain about the WS_ naming standard, at least. There are two types of "working storage" fields in Easytrieve Plus, W-type fields and S-type fields.
Far more than in COBOL it is useful to be able to recognise the type from the name.
Have you tried letting Easytrieve Plus look after the lines and the page numbers?
If you want the report like you seem to want it you'll need to remove the LINE 01 (and make 02 into 01) and use a BEFORE-LINE proc. In that proc you'll have to do your own control-break processing, which will require two new S-type fields to store the two keys, and DISPLAY the key on change.
Remember to do it correctly for when the high-order key changes but the low-order does not.
A tip.
Get it agreed, through your Boss, that all reports produced by Easytrieve Plus should be reports that can naturally be produced through Easytrieve Plus without introducing error-prone code. For example, there is plenty of space, it seems, to include all the data on one line, and your "blank" line problem immediately disappears. You get rid of the existing nutty (and wrong) AFTER-LINE processing.
Make your life easy. If the report-layout fits what Easytrieve Plus can do, fine, if it doesn't, why twist Easytrieve Plus just to use it? You're making space for bugs which just don't need to be there.
I have done before exactly what you suggest to process the control-break in BEFORE-LINE Proc and the low order keys are not changed. So i left it and looking for other option.
Tip is nice and yes we can do as you said. As of now i am keep the report as it is. If i will not get any solution then i will go to deliver the same Un-formatted report to my boss.
Hi, i have tried with DTLCTL FIRST but not working. Still i am getting the blank lines for each Line 02 detail records. Please help me with code. Thanks.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
You were already using, be default, DTLCTL FIRST. DTLCTL can prevent the key value being printed, it will not ignore a line if it just so happens that a key value which is not present is the only thing on the line.
Post your BEFORE-LINE proc, we should be able to get it working. Include the definitions of the two key fields.
