IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SORT Query with the next record


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 2:39 pm
Reply with quote

Hi,

I got an issue and for that I need to delete records:
The criteria to drop the record if column 2-8 (which is actually date) should be less than 20130307. But the problem is I need to delete the next record of the matching criteria also.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Mar 07, 2013 2:43 pm
Reply with quote

what is that relates the <next> record to the one with the data ?
( just the position or they have some data which relates them )
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 2:55 pm
Reply with quote

actually the record is tool ong and it continues to the next record.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Thu Mar 07, 2013 2:57 pm
Reply with quote

Have you considered giving us some useful information to work with, including and example of the data.

Will this be for every sing occurence or selective occurences.

The more detail you give the more likely you are to get a good response
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 07, 2013 3:18 pm
Reply with quote

RECFM, LRECL, length of "too long" records, sample input, expected output.

You can probably do it with WHEN=GROUP,BEGIN=( and your or start,lenght,type,EQ,date ),RECORDS=2,and PUSH a marker to indicate that OUTFIL INCLUDE/OMIT should ignore it, but details depend on what you can give us.
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 3:20 pm
Reply with quote

Hi Expat,
there is a abend in which the i/p file contain records which has the transaction date for jan and feb also. If I am deleting those records then my program is going in loop for the next record of the record deleted. Only similar thing in between those back to back record is the transaction id as both the record belong to same transaction and I dont have the transacti. When I am deleting the first record then the 2nd record should also be deleted but the second record dont have the date field.
For example:
Code:
----+----1----+----2----+----
*****************************
0106601643020200001000[color=darkblue]0207[/color]095
1006601643021122<?xml version


the higlighted one is the date field.and the transaction id is from column 3-12
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 3:25 pm
Reply with quote

didnt realised if I will highlight the format will change.

Please check below:
Code:
----+----1----+----2----+----
*****************************
01066016430202000010000207095
1006601643021122<?xml version


column 3-12 is tran-id and column 23-26 is date in mmdd format.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 07, 2013 3:31 pm
Reply with quote

OK, more questions for you to not get to answering yet.

Is the tran-id always the same?

Are there any records from a previous year, which would be valid and not require deleting, on the file?
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 3:32 pm
Reply with quote

Yes the transaction id for both the record will be same but I dont know the transaction id.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 07, 2013 3:50 pm
Reply with quote

OK, can you read back through all the questions and answer them?

More.

Is there only one "record-type" on the file (plus its extension) or might there be the chance of a "false hit" at the position of the date?

Are there no valid records for the January/February dates you mention?
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 4:01 pm
Reply with quote

RECFM=VB,RECL=2052. There will be no false hit for the date position but the second record dont have date.
I didnt understood the below one:
Quote:
Is there only one "record-type" on the file
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Thu Mar 07, 2013 4:12 pm
Reply with quote

Would it not be easier to amend the actual program that abends to reject the invalid data, report on the invalid data for reference, and thus remove the need to constantly apply fixes to the data ?

Just a thought.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 07, 2013 5:05 pm
Reply with quote

You describe the first record, then the second record being a "continuation" of the first.

Are all the records on the file like that, being in "pairs", with each of the pair having its own "structure"?

So, there would be no "single" records on the file, nothing other than these "pairs" all in the same format, but some being "OK", and some being incorrect.
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 5:14 pm
Reply with quote

No there is one more indicator and if the indicator is one then the records are in paired and if it is 0 then it is single record.
But I can't pair it as the second record of the paired record don't have any specific value at the indicator place.
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 5:19 pm
Reply with quote

the indicator position is 145-146
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 07, 2013 5:28 pm
Reply with quote

Goodness. Can there be a clash with the value indicator on the "second" record. Ie, is the second record able to give a "false hit" for a pair?

You must have a program which reads the file. What logic does that program use?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Thu Mar 07, 2013 5:56 pm
Reply with quote

expat wrote:
Would it not be easier to amend the actual program that abends to reject the invalid data, report on the invalid data for reference, and thus remove the need to constantly apply fixes to the data ?

Just a thought.
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Thu Mar 07, 2013 7:00 pm
Reply with quote

The program change done and the job went through... But now from out of box whether this is possible with SORT.
I will put the program conditions.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Mar 07, 2013 7:09 pm
Reply with quote

Quote:
But now from out of box whether this is possible with SORT.


we might tell if the rules were clearly defined ...

once upon a time
there were two records with the same key and the first one contained a date

but
Quote:
No there is one more indicator and if the indicator is one then the records are in paired and if it is 0 then it is single record.
But I can't pair it as the second record of the paired record don't have any specific value at the indicator place.

completely fogged up the explanation

the main question is ...

is there a way to identify the first record ???
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Mar 07, 2013 8:23 pm
Reply with quote

It very probably can be done with DFSORT, but we'd need all the information, including some sample data showing the conditions (pairs, singles, good dates, bad dates), and the expected output.

Is the file so badly designed that there is no way of identifying the "first" record of a single-or-pair group, without doing so "implicitly", as in "you know for the first data record on the file, and then hope to keep up with the thing via the single/pair indicator"? It can still be done, but, like anything else reading such a file it is a very "insecure" way to do it, as you really have to hope that the marker is correct.

Knowing "this bit of data tells us record 1, this bit of data tells us record 2", allows the marker to be validated, so you know immediately if the file "goes up the wall". Without that, if the marker is ever wrong, records from the file will be "lost" for as long as it takes for someone to "notice" it.
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Fri Mar 08, 2013 3:43 am
Reply with quote

Hi Bill,

Sorry for the late response.
Please find below some more information:
the first two columns are rec ind . so if it is a header record then it 00, if detail record then it is 01 and trailer it is 99. But I can see the second record of the paired record always starts with 10.
always the second record has "xml version="1.0"" at column 19-34.
If the first record ind of the first paired record is 1 which I mentioned earlier then the record is paired with the second one else it is not paired.
The date is 4 digit which is in mmdd format and the file has records from january to current date also the time which is hhmmss format starts after the date column and we need to drop all the records which comes before 0306190000.(mmddhhmmss).
In the below example first pair, 2nd pair,3rd pair ,7th pair and the second single record after the 7 pair records doesnt satisfy 0306190000(mmddhhmmss) condition. Those single records have the indicator value "0" and those pair records have indicator value as "1"

Code:
----+----1----+----2----+----3----+----4---
0106600126410100502000030618115503070514548
1006600126411705<?xml version="1.0" encodin
0106600126400100320000030618113603070514548
1006600126401705<?xml version="1.0" encodin
0106600126390100501000030718103203070514548
1006600126391705<?xml version="1.0" encodin
0106600126380100320000010718100503070514548
1006600126381705<?xml version="1.0" encodin
0106600126370620912000030718092303070514451
1006600126371370<?xml version="1.0" encodin
0106600126360200522000030718090603070514451
1006600126361705<?xml version="1.0" encodin
0106600126350100502000020718080903070514451
1006600126351705<?xml version="1.0" encodin
010660012619020001200003071710190307901503
010660012618020001200003061710180307901503
010660012617020001200003071710180307901503
010660012616020001200003071710180307901503
0106600126150100502000030718080903070514451
1006600126151705<?xml version="1.0" encodin

Will check more in detail on tomorrow morning whether I can find more information.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Mar 08, 2013 5:05 am
Reply with quote

OK. Looks more reasonable now. If you confirm when you have provided all the information, I think it won't be too much code.

Can you show what you expect as your output from the sample input. Otherwise people will make up their own results and you get left to fix it :-)
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Fri Mar 08, 2013 4:40 pm
Reply with quote

Hi Bill,
Me expected output is:
Code:
----+----1----+----2----+----3----+----4---
0106600126390100501000030718103203070514548
1006600126391705<?xml version="1.0" encodin
0106600126370620912000030718092303070514451
1006600126371370<?xml version="1.0" encodin
0106600126360200522000030718090603070514451
1006600126361705<?xml version="1.0" encodin 
010660012619020001200003071710190307901503 
010660012617020001200003071710180307901503
010660012616020001200003071710180307901503
0106600126150100502000030718080903070514451
1006600126151705<?xml version="1.0" encodin
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Fri Mar 08, 2013 5:28 pm
Reply with quote

This is what I used to test with fixed-length records.

Code:
  OPTION COPY
  INREC IFTHEN=(WHEN=INIT,
        BUILD=(1,4,10X,5,76)),
        IFTHEN=(WHEN=GROUP,BEGIN=(1,2,CH,EQ,C'01'),
                           END=(1,1,CH,EQ,C'10',
                            AND,27,2,CH,EQ,C'<?'),
            PUSH=(5:33,10))
  OUTFIL OMIT=(1,2,SS,EQ,C'01,10',
           AND,5,10,CH,LT,C'0306190000'),
         BUILD=(1,4,15,75)


Produces, after I added a Header and Trailer:

Code:
00HEADER                                     
0106600126390100501000030718103203070514548   
1006600126391705<?XML VERSION="1.0" ENCODIN   
0106600126370620912000030718092303070514451   
1006600126371370<?XML VERSION="1.0" ENCODIN   
0106600126360200522000030718090603070514451   
1006600126361705<?XML VERSION="1.0" ENCODIN   
010660012619020001200003071710190307901503   
010660012617020001200003071710180307901503   
010660012616020001200003071710180307901503   
0106600126150100502000030718080903070514451   
1006600126151705<?XML VERSION="1.0" ENCODIN   
99TRAILER                                     


Note that I have not included the "marker", just used the presence of the 10 record.

For your VB, it should be something like this, untested:
Code:
  OPTION COPY
  INREC IFTHEN=(WHEN=INIT,
        BUILD=(1,4,10X,5)),
        IFTHEN=(WHEN=GROUP,BEGIN=(15,2,CH,EQ,C'01'),
                           END=(15,2,CH,EQ,C'10',
                            AND,31,2,CH,EQ,C'<?'),
            PUSH=(5:37,10))
  OUTFIL OMIT=(15,2,SS,EQ,C'01,10',
           AND,5,10,CH,LT,C'0306190000'),
         BUILD=(1,4,15)


You can include the marker as an "AND" in the BEGIN, but what I'd do is separately check, with some similar code, that the marker accurately relates to the 10-type records. If it does, you don't need to include it. If it doesn't your file is either not as described, or it is "broken".
Back to top
View user's profile Send private message
abhijit.nayak01

Active User


Joined: 22 Mar 2009
Posts: 161
Location: South Africa

PostPosted: Sat Mar 09, 2013 12:49 am
Reply with quote

Hurray!!!!!. Its working icon_smile.gif
Thanks Bill.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL Goto page 1, 2  Next

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts JCL sort card - get first day and las... JCL & VSAM 9
No new posts To find whether record count are true... DFSORT/ICETOOL 6
Search our Forums:

Back to Top