View previous topic :: View next topic
|
Author |
Message |
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2023 Location: USA
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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 |
|
|
balaji81_k
Active User
Joined: 29 Jun 2005 Posts: 155
|
|
|
|
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 |
|
|
|