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 Query for VB file

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Job Control Language(JCL)
View previous topic :: View next topic  
Author Message
sudoku
Beginner


Joined: 14 Aug 2006
Posts: 10
Topics: 4

PostPosted: Thu Nov 09, 2006 6:14 pm    Post subject: Sort Query for VB file Reply with quote

Hi,

I have a VB file and need to do the following:

Fetch H01 and F01 (1st and 2nd record) to sort file output

If on H01 record 20th column is 'RAW'
and
If on F01 record 24th column is 'R'


Code:

H01                     RAW          .
F01                         R           .   
B01
B01
B01
T01


Thanks for any help!

Thanks,
Sudoku
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Thu Nov 09, 2006 6:32 pm    Post subject: Reply with quote

It's not clear what you want to do here. If you want to write the H01 record to the output file if it has 'RAW' and write the F01 record to the output file if it has 'R', then you can use this DFSORT INCLUDE statement:

Code:

  INCLUDE COND=((5,3,CH,EQ,C'H01',AND,24,3,CH,EQ,C'RAW'),OR,
                (5,3,CH,EQ,C'F01',AND,28,1,CH,EQ,C'R'))     


Note that we have to add 4 to each starting position to account for the RDW since this is a VB file.

For the input you show, the output would be:

Code:

H01                RAW          .       
F01                    R           .     


If that's not what you want to do, then please explain more clearly what you want. Showing the input and expected output for different cases would help.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
sudoku
Beginner


Joined: 14 Aug 2006
Posts: 10
Topics: 4

PostPosted: Thu Nov 09, 2006 11:17 pm    Post subject: Reply with quote

Thanks Frank,

I have a VB file with following record type:
======================
H01 RAW .
F01 R .
B01
B01
B01
T01
======================
In want to check if Header H01 has 'RAW' on column 20 and
F01 record has 'R' on column 24

If true write the 2 records H01 and F01 to ouput file
Else write nothing

So for the above record layout the output should look like:

======================
H01 RAW .
F01 R .
======================

I tried running using SORT pgm and the INCLUDE control card you mentioned
but its failing with U016

Thanks,
Sudoku
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Fri Nov 10, 2006 12:20 pm    Post subject: Reply with quote

Do you mean that you want to write the two records only if the conditions for both records are true?

That would be the case for the example you showed.

But would you want no records written for these two cases where only one record meets the condition?

Case1:

H01 XXX .
F01 R .
B01
B01
B01
T01

Case 2:

H01 RAW .
F01 X .
B01
B01
B01
T01

Quote:
I tried running using SORT pgm and the INCLUDE control card you mentioned but its failing with U016


Make sure you have a blank in position 1 for both lines of the INCLUDE statement. If that's not the problem, then you need to show the //SYSOUT output so we can see what error message you received.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
Back to top
View user's profile Send private message Send e-mail Visit poster's website
sudoku
Beginner


Joined: 14 Aug 2006
Posts: 10
Topics: 4

PostPosted: Fri Nov 10, 2006 2:01 pm    Post subject: Reply with quote

Yes Frank!

I would like to have the two records "only if" condition for both the records are met.
Else it should be empty.

Expected output for Case1 and Case2:

Case1: (empty)


Case2: (empty)


Thanks,
Sudoku
Back to top
View user's profile Send private message
Frank Yaeger
Sort Forum Moderator
Sort Forum Moderator


Joined: 02 Dec 2002
Posts: 1618
Topics: 31
Location: San Jose

PostPosted: Fri Nov 10, 2006 4:36 pm    Post subject: Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG   DD  SYSOUT=*
//DFSMSG   DD  SYSOUT=*
//IN DD DSN=...  input file (VB)
//OUT DD DSN=...  output file (VB)
//TOOLIN   DD    *
SELECT FROM(IN) TO(OUT) ON(5,1,CH) ALLDUPS USING(CTL1)
//CTL1CNTL DD *
  INCLUDE COND=(5,3,SS,EQ,C'H01,F01')
  INREC IFTHEN=(WHEN=INIT,BUILD=(1,4,C'A',5)),
   IFTHEN=(WHEN=(6,3,CH,EQ,C'H01',AND,25,3,CH,NE,C'RAW'),
    OVERLAY=(5:C'B'),HIT=NEXT),
   IFTHEN=(WHEN=(6,3,CH,EQ,C'F01',AND,29,1,CH,NE,C'R'),
    OVERLAY=(5:C'C'))
  OUTFIL FNAMES=OUT,BUILD=(1,4,6)
/*

_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
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 -> Job Control Language(JCL) 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