| View previous topic :: View next topic |
| Author |
Message |
rg_nath
Beginner
Joined: 03 Jul 2015
Posts: 23
Topics: 7
|
 Posted: Mon Jul 06, 2015 12:18 pm Post subject: Easytrieve: Advance Blank line in Detail records |
 |
|
Hi, This is my first post in this forum and i would like take a chance to post my (Friends) issue in this forum.
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
|
The output i have got is :
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0----+----1----+----2----+----3---
1
************************ 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
CCOL1 CPLUSB CPLUSC HS-CCOL6
------ -------- -------- --------
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
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
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: 3
REPORT TITLE DATE: 7/03/15 9.50.49
CCOL1 CPLUSB CPLUSC HS-CCOL6
------ -------- -------- --------
CB CB2 XX XXXX XXXXXXXXXXXXXXXXXXXX
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.
-RG |
|
| Back to top |
|
 |
William Collins
Supermod
Joined: 03 Jun 2012
Posts: 437
Topics: 0
|
| Posted: Mon Jul 06, 2015 12:46 pm Post subject: |
|
|
| Are you expecting to find a different answer than your friend has already received in the other place? |
|
| Back to top |
|
|
rg_nath
Beginner
Joined: 03 Jul 2015
Posts: 23
Topics: 7
|
| Posted: Mon Jul 06, 2015 1:57 pm Post subject: |
|
|
| Hi, In other forum have got few answers which we tried is not really working us. So i would like to get a resolution from this forum if any. Thanks. |
|
| Back to top |
|
|
William Collins
Supermod
Joined: 03 Jun 2012
Posts: 437
Topics: 0
|
| Posted: Mon Jul 06, 2015 2:21 pm Post subject: |
|
|
| Well, you may find out it doesn't work out like that. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Jul 06, 2015 2:49 pm Post subject: Re: Easytrieve: Advance Blank line in Detail records |
|
|
| rg_nath wrote: |
| Code: |
LINE 02 COL 06 HS-CCOL1, -
COL 15 CPLUSB, -
COL 25 CPLUSC, -
COL 45 HS-CCOL6
*
|
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.
-RG |
Did you notice that your LINE 02 is writing the host variables fetched from your sql? Are these values spaces in your DB2 table? or they binary/packed data items that are not visible to the eye?
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
rg_nath
Beginner
Joined: 03 Jul 2015
Posts: 23
Topics: 7
|
| Posted: Tue Aug 18, 2015 1:19 pm Post subject: |
|
|
Hi Kolusu,
Sorry for late response. I was in vacation this period.
Yes i am using SQL Host variable for report. but there is no spaces and null values in variables. if spaces and null variables are available in table the it automatically reflect in detail record. But here the situation is the entire empty record is appearing after every detail record for LINE2. Thanks |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
