Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
SORT INNER JOIN [F1] OUT OF SEQ ERROR

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

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Wed Jan 30, 2019 3:21 am    Post subject: SORT INNER JOIN [F1] OUT OF SEQ ERROR
Reply with quote

Hi Team,

I have two files which is of different in record length .

F1 - 80 bytes looks like below

consists of dept-no,sales-date,hour-nbr,sales-amount,........,last-year-date

Code:


----------------------------------------------------------
100,2019-01-30,00,100.30,XXXXXXXXXXXXXX..........2018-01-31
100,2019-01-30,01,200.04,XXXXXXXXXXXXXX..........2018-01-31
..
..
..
100,2019-01-30,23,100.04,XXXXXXXXXXXXX..........2018-01-31
100,2019-01-31,00,200.04,XXXXXXXXXXXXX..........2018-02-01
----------------------------------------------------------



F2- 21 bytes

Code:


----------------------------
2019-01-31 2018-01-31
----------------------------



I am doing inner join on matching records only as below card

Code:


SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(71,10,A)
JOINKEYS FILES=F2,FIELDS=(12,10,A)
REFORMAT FIELDS=(F2:01,21,F1:1,70)



I Got error on

Code:


SORTJNF1 (F1) KEY OUT OF SEQUENCE



Even if i have used NOSEQCK but still it is working for first set of records for one date not looking for next department for the same date matching.

Please advise.
Back to top
View user's profile Send private message

balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Wed Jan 30, 2019 3:25 am    Post subject:
Reply with quote

F2 file have more than one date

like below

Code:


----------------------------
2019-01-31 2018-01-31
2019-02-05 2018-02-01
----------------------------

Back to top
View user's profile Send private message
expat

Global Moderator


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

PostPosted: Wed Jan 30, 2019 1:04 pm    Post subject:
Reply with quote

RECFM of the two datasets ???
first guess for a silly mistake is that one of the files is VB and you haven't accounted for the RDW. But had you have specified the RECFM I wouldn't need to consider this.

Have the two datasets been presorted ? and if so - in the correct sequence

Again - When posting a request for help, please supply the following - Those in bold are missing

1) RECFM and LRECL of the datasets in use.
2) The product that you are using along with the release level.

3) The code that you are using.
4) Examples of the input AND the expected output.
5) A brief but concise overview of what you are trying to achieve.


Remember that the more information that you give us, the more likely you are to get useful feedback.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Jan 30, 2019 7:07 pm    Post subject:
Reply with quote

balaji81_k,

As expat asked above, your input data sets (F1 in this case) do not seem to be pre-sorted on the join key used.

ICE424A ddname (Fn) KEY IS OUT OF SEQUENCE

The error message suggests that you attempted to use the operand SORTED without NOSEQCK and DFSORT detected an out of sequence key situation. If you proceed to use NOSEQCK you might be able to get past the error but end up with incorrect results. The purpose of these operands is to use when the inputs are already pre-sorted, so that DFSORT does not need to repeat it before the JOIN.

Remove the SORTED & NOSEQCK from the JOIN statement if the input is not pre-sorted, so DFSORT can sort it before the JOIN operation.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Wed Jan 30, 2019 9:12 pm    Post subject:
Reply with quote

Arun Raj wrote:
balaji81_k,

As expat asked above, your input data sets (F1 in this case) do not seem to be pre-sorted on the join key used.

ICE424A ddname (Fn) KEY IS OUT OF SEQUENCE

The error message suggests that you attempted to use the operand SORTED without NOSEQCK and DFSORT detected an out of sequence key situation. If you proceed to use NOSEQCK you might be able to get past the error but end up with incorrect results. The purpose of these operands is to use when the inputs are already pre-sorted, so that DFSORT does not need to repeat it before the JOIN.

Remove the SORTED & NOSEQCK from the JOIN statement if the input is not pre-sorted, so DFSORT can sort it before the JOIN operation.



Hi Arun,

I have fixed this issue yesterday by trying with out 'SORTED' clause and I have base look on the results and it is good to me .
Many thanks for your inputs.

Thanks
Balaji K
Back to top
View user's profile Send private message
sergeyken

Active User


Joined: 29 Apr 2008
Posts: 471
Location: Maryland

PostPosted: Sat Feb 02, 2019 2:22 am    Post subject:
Reply with quote

balaji81_k wrote:

I have fixed this issue yesterday by trying with out 'SORTED' clause and I have base look on the results and it is good to me .
Many thanks for your inputs.

Thanks
Balaji K

It was obvious from the very beginning, that you tried to use SORTED option in your JOINKEYS statement(s). But you provided fake info in your original topic, without this option.

Colossians 3:9 wrote:
Do not lie to each other, since you have taken off your old self with its practices
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Sat Feb 02, 2019 4:46 am    Post subject:
Reply with quote

sergeyken wrote:
balaji81_k wrote:

I have fixed this issue yesterday by trying with out 'SORTED' clause and I have base look on the results and it is good to me .
Many thanks for your inputs.

Thanks
Balaji K

It was obvious from the very beginning, that you tried to use SORTED option in your JOINKEYS statement(s). But you provided fake info in your original topic, without this option.

Colossians 3:9 wrote:
Do not lie to each other, since you have taken off your old self with its practices


Hi sergeyken ,

I am really really sorry for this , since i should not do copy and paste of my original code from my work environment that causes misleading information from the beginning on my post .

I am sure from next time will post the correct info from the beginning.

I apologize.

Thanks
Balaji K
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2189
Location: Hampshire, UK

PostPosted: Sat Feb 02, 2019 3:35 pm    Post subject: Reply to: SORT INNER JOIN [F1] OUT OF SEQ ERROR
Reply with quote

Why can you not copy and paste? Everyone else does. They are sort control cards after all and not data. If they include some text string then you can always edit the string before posting.
Back to top
View user's profile Send private message
balaji81_k

Active User


Joined: 29 Jun 2005
Posts: 137

PostPosted: Mon Feb 04, 2019 11:24 pm    Post subject: Re: Reply to: SORT INNER JOIN [F1] OUT OF SEQ ERROR
Reply with quote

Nic Clouston wrote:
Why can you not copy and paste? Everyone else does. They are sort control cards after all and not data. If they include some text string then you can always edit the string before posting.


Hi Nic,

Since i am connecting my work environment thru Virtual development network hence copy and paste is not possible , i have see the code manually and typing and thus causes manual errors . sorry for that .

Thanks
BalajiK
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
No new posts need help with a tricky sort - Continued rajiv rengasamy SYNCSORT 8 Tue Jul 16, 2019 1:26 pm
No new posts Sort records within a data block rajiv rengasamy DFSORT/ICETOOL 0 Fri Jul 12, 2019 3:25 pm
This topic is locked: you cannot edit posts or make replies. need help with a tricky sort. rajiv rengasamy SYNCSORT 7 Fri Jul 12, 2019 12:36 pm
No new posts data conversion error maxsubrat PL/I & Assembler 4 Thu May 09, 2019 10:57 pm
No new posts To remove the spaces using SORT Card dharmaraok DFSORT/ICETOOL 1 Tue Apr 30, 2019 3:42 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us