Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query regarding SORTING with include option

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Mon Dec 24, 2007 10:22 am    Post subject: Query regarding SORTING with include option
Reply with quote

Hi,

I am trying to sort a VSAM file to a flat file with the condition that the date W1234-INPUT-DATE of the VSAM file should be in the range between 01012004 to 01012007 and W1234-MAIL-OPT-OUT = y .The JCL is running successfully but only the second condition is met and the record having the date which is not in the specified range are also getting copied.

Kindly help in this regard.

Thanks in advance.
Rahul
************************************
nuaxk67.TEST.PARMLIB(ADI36AA),

SORT FIELDS=(1,16,CH,A)
INCLUDE COND=(1417,8,CH,GE,C'01012004',AND,1417,8,CH,LE,C'27112007',
AND,1190,1,CH,EQ,C'Y')
******************************************************
JCL

//nuaxk67A JOB ,'ADI36AA',CLASS=I,MSGCLASS=X,MSGLEVEL=(1,1),
// NOTIFY=&SYSUID
//JOBLIB DD DSN=DBK.TEST.CICS.LOADLIB,DISP=SHR
// DD DSN=NUAXK67.TEST.LOADLIB,DISP=SHR
// DD DSN=ALC.PROD.BLOAD,DISP=SHR
// DD DSN=DBK.PROD.CICS.LOADLIB,DISP=SHR
// DD DSN=NUAXK67.PROD.LOADLIB.BATCH,DISP=SHR
//STEP010 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=NUAXK67.TEST.AG6981.INTERNET.HISTORY.LM36,
// DISP=SHR
//SORTOUT DD DSN=NUAXK67.TEST.AG6981.INTERNET.HISTORY.SORT.OUT5,
// DISP=(NEW,CATLG,DELETE),
// SPACE=(CYL,(20,10),RLSE),
// DCB=(LRECL=3500,RECFM=FB,BLKSIZE=3500)
//SYSIN DD DSN=nuaxk67.TEST.PARMLIB(ADI36AA),
// DISP=SHR

***********************************************************

COPYBOOK layout

W1234-RECORD 1 3500 3500
5 W1234-ENROLMENT-NUM X(8) 1 1 8 8
5 W1234-APPLICATION-NUM X(8) 2 9 16 8

W1234-INPUT-DATE 9(8) 139 1417 1424 8

5 W1234-MAIL-OPT-OUT X 84 1190 1190 1
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Dec 24, 2007 10:27 am    Post subject:
Reply with quote

Rahul,

Go thru the reply in your duplciate post -

http://www.ibmmainframes.com/viewtopic.php?p=112701#112701
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Mon Dec 24, 2007 3:52 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Hi Murali,

I am not bale to open the link.could you please let me know the solution.

Regards,
Rahul
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Mon Dec 24, 2007 3:58 pm    Post subject:
Reply with quote

Rahul,

Quote:
VSAM file should be in the range between 01012004 to 01012007


Quote:
INCLUDE COND=(1417,8,CH,GE,C'01012004',AND,1417,8,CH,LE,C'27112007',

Did I read the conds correctly? Just wanted to know.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Dec 24, 2007 5:02 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Do You realize that the date field is not an "ordered" date ???

to carry on semi/pseudo arithmetic comparison on dates
( in a single comarison )
the dates themselves MUST be in the form "YYYYMMDD"
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Mon Dec 24, 2007 8:42 pm    Post subject:
Reply with quote

Rahul,

You can't compare an 'mmddyyyy' date like that. You can use a DFSORT job like this to do what you asked for:

Code:

//S1    EXEC  PGM=ICEMAN
//SYSOUT    DD  SYSOUT=*
//SORTIN DD DSN=...  input file (FB/3500)
//SORTOUT DD DSN=...  output file (FB/3500)
//SYSIN    DD    *
* Convert 'mmddyyyy' date to 'yyyymmdd' date in 3501-3508
  INREC OVERLAY=(3501:1421,4,1417,4)
  SORT FIELDS=(1,16,CH,A)
* Do INCLUDE on 'yyyymmdd' date in 3501-3508
  OUTFIL INCLUDE=(3501,8,CH,GE,C'20040101',AND,
    3501,8,CH,LE,C'20070101',AND,
    1190,1,CH,EQ,C'Y'),
   BUILD=(1,3500)
/*
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Wed Dec 26, 2007 9:18 am    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Thanks all for the reply. I will try to change the Date format to YYYYMMDD and let you know in case it solves the problem.

Many Thanks,
Anshul
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Wed Dec 26, 2007 5:49 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Hi Frank,

Could you please explain me the below syntax.As the date in the file is of the format DDMMYYYY,thereby do I need to write another INREC OVERLAY statement to convert DDMMYYYY to MMDDYYYY.

*********************
Convert 'mmddyyyy' date to 'yyyymmdd' date in 3501-3508
INREC OVERLAY=(3501:1421,4,1417,4)
SORT FIELDS=(1,16,CH,A)
* Do INCLUDE on 'yyyymmdd' date in 3501-3508
OUTFIL INCLUDE=(3501,8,CH,GE,C'20040101',AND,
3501,8,CH,LE,C'20070101',AND,
1190,1,CH,EQ,C'Y'),
BUILD=(1,3500)
**********************
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Wed Dec 26, 2007 6:11 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Rahul,

Most of the statements are self-explanatory. Anyways find below the explanations.

Code:
Convert 'mmddyyyy' date to 'yyyymmdd' date in 3501-3508
INREC OVERLAY=(3501:1421,4,1417,4)
SORT FIELDS=(1,16,CH,A)


The date which is of the form 'mmddyyyy' from pos 1417 till 1424 is overlayed in pos 3501 till 3508 in the form 'yyyymmdd'. Then it is sorted on the first 16 characters.

Code:
OUTFIL INCLUDE=(3501,8,CH,GE,C'20040101',AND,
3501,8,CH,LE,C'20070101',AND,
1190,1,CH,EQ,C'Y'),


Then in the INCLUDE condition records with the date greater than 20040101 and less than 20070101 are selected. These records should also have Y in the 1109th position.

Code:
BUILD=(1,3500)


After selecting all the records meeting our requirement we are ignoring the data which we overlayed in the 3501 - 3508. This is done by building only the first 3500 characters using the BUILD command.
Back to top
View user's profile Send private message
Rajesh-Suvera

New User


Joined: 26 Dec 2007
Posts: 3
Location: Chennai

PostPosted: Wed Dec 26, 2007 6:30 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Hi Rahul,

I beleive there might not be no records thats falls into the include condition option. let us say if any one of the condition among the three in the include is not met, it wont get included in the sort operation and it go for sorting as per you have given in the main sort card. (SORT FILEDS (....)).. So try with the same option by giving ONLY in the sort card.

Hope this may give some idea.

-Rajesh
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Wed Dec 26, 2007 6:34 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Rajesh,

Welcome to the forums. I am not able to get your point mentioned in your last post. Can you please explain it clearly?
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Wed Dec 26, 2007 8:55 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Hi Rajesh,

Only those records having Input-date between 01012004 to 01012007 and Indicator set to 'y' will be included.

My doubt is that how I can convert DDMMYYYY to YYYYMMDD format.Do I have to use the INREC OVERLAY statement twice forst to convert the DDMMYYYY to YYYYDDMM and then to convert YYYYDDMM to YYYYMMDD.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Wed Dec 26, 2007 9:40 pm    Post subject:
Reply with quote

You just have to change the INREC OVERLAY slightly like this:

Code:

* Convert 'ddmmyyyy' date to 'yyyymmdd' date in 3501-3508     
  INREC OVERLAY=(3501:1421,4,1419,2,1417,2)                   
Back to top
View user's profile Send private message
Rahul_kumar
Warnings : 2

New User


Joined: 24 Jun 2006
Posts: 50

PostPosted: Thu Dec 27, 2007 2:21 pm    Post subject: Reply to: Query regarding SORTING with include option
Reply with quote

Hi,

I have tried it by removing the spaces as well but still I am getting the same error.

The input file is of length 3500 FB so will it matter if in the INREC I Overlay it from 3501-3508.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Thu Dec 27, 2007 3:53 pm    Post subject: Re: Reply to: Query regarding SORTING with include option
Reply with quote

Rahul_kumar wrote:
Hi,

I have tried it by removing the spaces as well but still I am getting the same error.
Give you new SYSOUT messages

The input file is of length 3500 FB so will it matter if in the INREC I Overlay it from 3501-3508.
It wont matter

It would be better if you could explain as to what you are trying to do?
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Dec 27, 2007 10:41 pm    Post subject:
Reply with quote

These DFSORT control statements do NOT get syntax errors for me. Note that you must have at least one space at the start of each line.

Code:

   INREC OVERLAY=(3501:1421,4,1419,2,1417,2)
   SORT FIELDS=(1,16,CH,A)
   OUTFILE INCLUDE=(3501,8,CH,GE,C'20040101',AND,
      3501,8,CH,LE,C'20070101',AND,1190,1,CH,EQ,C'Y'),
    BUILD=(1,3500)


If you get syntax errors with these control statements, show the error messages using code tags, and tell me exactly where the $ points in each line that got a syntax error.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us