MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

SORT HELP

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Mon Oct 18, 2004 9:51 am    Post subject: SORT HELP Reply with quote
Hi All,

My input file looks as shown below.
Code:

DUNS-NBR   Date--------A(1) B(1) C(1) A(2) B(2) C(2) A(20) B(20)C(20)
210116992 2004-03-31 1----2----3----1---2----3---- 1-----2-----3
210116992 2004-03-31 4----5----6----4---5--- 6---- 4-----5-----6
210116992 2004-03-31 7----8----9----7---8----9---- 7-----8-----9

My program is inserting rows into a table depending on the values present in this file.
The query is as below.
Code:

INSERT INTO BAL_SHT_FIG
(DUNS_NBR,
FINL_STMT_PRD_END,
BAL_SHT_CLSF_CD,
BAL_SHT_CAT_CD,
AMT)
VALUES
(:GRN001-DUNS-NBR,
:GRN001-FINL-STMT-PRD-END,
:GRN003-BAL-SHT-CLSF-CD,
:GRN003-BAL-SHT-CAT-CD,
:GRN003-AMT)

Before this they are moving the values to host variables from the file like this:
Code:

MOVE DUNS-NBR TO GRN001-DUNS-NBR
MOVE DATE TO GRN001-FINL-STMT-PRD-END.
MOVE A(counter) TO GRN003-BAL-SHT-CLSF-CD.
MOVE B(counter) TO GRN003-BAL-SHT-CAT-CD.
MOVE C(counter) TO GRN003-AMT.

Counter varies from 1 to 20.

The sql inserts the values A1,B1,C1.
After inserting the query takes the values A2,B2,C2.
As already these values are inserted into the table the query fails with -803.

How can I overcome this with some SORT step.
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Mon Oct 18, 2004 10:46 am    Post subject: Reply with quote
Paneendra,

what is the format and length of the A,B,C? Post the copybook layout definitions for your input file and I will post the solution. Another option is to check in the program itself.

option 1: declare a working-storage variable to hold the values.
Code:

01 WS-PREVIOUS-KEY
   05 WS-PREV-DUNS-NBR
   05 WS-PREV-DATE
   05 WS-PREV-A
   05 WS-PREV-B
   05 WS-PREV-C


Check for this WS-PREVIOUS-KEY before the insert.
Code:

IF DB2-INSERT-KEY = wS-PREVIOUS-KEY
   CONTINUE
ELSE
   PERFORM INSERT INTO TABLE
   MOVE DB2-INSERT-KEY TO WS-PREVIOUS-KEY
END-IF


option : 2

Check for sqlcode of -803 after the insert.

Code:

 EVALUATE SQLCODE
     WHEN +0
          -803
          CONTINUE
     WHEN OTHER
          PERFORM INHOUSE-ABEND
 END-EVALAUATE


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Mon Oct 18, 2004 11:02 pm    Post subject: Reply with quote
Kolusu thanks for your reply.
In the program we can handle -803.
But I want to do it with a sort step.
This is the copybook:

05 BSF-TABLE-DATA.
10 BSF-DUNS-NBR PIC S9(11) COMP-3.
10 BSF-FINL-STMT-PRD-END PIC X(10).
10 BSF-FINL-DATA OCCURS 20.
15 BSF-BAL-SHT-CLSF-CD PIC S9(4) COMP.
15 BSF-BAL-SHT-CAT-CD PIC X(1).
15 BSF-AMT PIC S9(11) COMP-3.
Back to top
View user's profile Send private message Send e-mail
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
PostPosted: Tue Oct 19, 2004 6:52 am    Post subject: Reply with quote
VKphani,

The following DFSORT/ICETOOL jcl will give you the desired results. A brief explanation of the job. We need to break each occurance of BSF-FINL-DATA into individual records, so that we can eliminate the duplicates. The parm / is used to split the records. Once we break 1 single into 2 records then we sort the on the key and eliminate the duplicates.


Code:

//STEP0100 EXEC PGM=ICETOOL                                     
//TOOLMSG  DD SYSOUT=*                                         
//DFSMSG   DD SYSOUT=*                                         
//IN       DD DSN=YOUR INPUT FILE,
//            DISP=SHR                                         
//T1       DD DSN=&T1,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//OUT      DD DSN=YOUR NON DUPS FILE,
//            DISP=(NEW,CATLG,DELETE),
//            UNIT=SYSDA,
//            SPACE=(CYL,(X,Y),RLSE)
//TOOLIN    DD *                       
  COPY FROM(IN)         USING(CTL1)     
  SORT FROM(T1) TO(OUT) USING(CTL2)     
//CTL1CNTL DD *                         
  OUTFIL FNAMES=T1,                               
  OUTREC=(01,16,    $ DUNS-NBR + STMT-PRD-END     
          17,09,/,  $ FINL-DATA(1)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          26,09,/,  $ FINL-DATA(2)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          35,09,/,  $ FINL-DATA(3)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          44,09,/,  $ FINL-DATA(4)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          53,09,/,  $ FINL-DATA(5)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          62,09,/,  $ FINL-DATA(6)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          71,09,/,  $ FINL-DATA(7)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          80,09,/,  $ FINL-DATA(8)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          89,09,/,  $ FINL-DATA(9)                 
          01,16,    $ DUNS-NBR + STMT-PRD-END     
          98,09,/,  $ FINL-DATA(10)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         107,09,/,  $ FINL-DATA(11)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         116,09,/,  $ FINL-DATA(12)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         125,09,/,  $ FINL-DATA(13)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         134,09,/,  $ FINL-DATA(14)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         143,09,/,  $ FINL-DATA(15)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         152,09,/,  $ FINL-DATA(16)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         161,09,/,  $ FINL-DATA(17)               
          01,16,    $ DUNS-NBR + STMT-PRD-END     
         170,09,/,  $ FINL-DATA(18)               
          01,16,    $ DUNS-NBR + STMT-PRD-END 
         179,09,/,  $ FINL-DATA(19)           
          01,16,    $ DUNS-NBR + STMT-PRD-END 
         188,09)    $ FINL-DATA(20)           
//CTL2CNTL DD *                               
  SORT FIELDS=(01,06,PD,A,  $ DUNS-NBR         
               07,10,CH,A,  $ STMT-PRD-END     
               17,02,BI,A,  $ CLSF-CD         
               19,01,CH,A,  $ CAT-CD           
               20,6,PD,A)   $ AMT             
  SUM FIELDS=NONE                             
/*


Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group