Hi,

i have written the code below

table file pwrccb
print *
on table hold as hold1
end

table file pwrccp
print *
on table hold as hold2
end

join econcer in hold1 to econcer in hold2 as ho2

table file hold1
print history file product plant
on table save as coninfo
end.

i have execute the above code i got the error
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED


How to resolve this error.
end

first try this:

? 1070

by typing ? and then the error number you'll get a fuller explantion of the error- have a look at this and see if you can work it from there, if you can't come back and ill let you know how to resolve it...

I should stipulate that what i mean by 'type this'

is enter it into a blank fex, the command console or in your code with an -EXIT after it...

quote:

table file pwrccb
print *
on table hold as hold1
end

table file pwrccp
print *
on table hold as hold2
end

join econcer in hold1 to econcer in hold2 as ho2

table file hold1
print history file product plant
on table save as coninfo
end.

i am running through the mainfram system i can't the more detail please let us know.

ah ive never used mainframe so i can't assume the error has the same cause in that environment even though intuitively i think they would share the same cause,

hopefully some mainframer will be able to sort this out...

quote:

table file pwrccb
print *
on table hold as hold1
end

table file pwrccp
print *
on table hold as hold2
end

join econcer in hold1 to econcer in hold2 as ho2

table file hold1
print history file product plant
on table save as coninfo
end.

try
table file pwrccb
print *
on table hold as hold1
end
-RUN

table file pwrccp
print *
on table hold as hold2
end
-RUN

join econcer in hold1 to econcer in hold2 as ho2

table file hold1
print history file product plant
on table save as coninfo
end.

i think you shoudl be able to do what i suggested on mainframe - can't see how you can run a .fex on mainframe but not be able to run a new fex or amend the fex you are using...

if you can tell us why you can't do this in mainframe someone may be able to offer a work around

If you check the documentation on joining flat or hold files, you will see that is says that the files being joined MUST be sorted in the same order. Rather than saying PRINT *, say PRINT FIELDS BY SORTFIELD and your problem will go away assuming that the sortfields have the same format (another requirement).

it's what i'd have said also, of course i was trying to encourage RK to do a bit more digging, providing him/her with a way of getting more information on an error and enabling them to work out other cryptic error messages in the future...

im pretty sure the ? [error num] can be done on mainframe if it lets you run a fex so they would have learnt that too...

quote:

table file pwrccb
print *
on table hold as hold1
end

table file pwrccp
print *
on table hold as hold2
end

join econcer in hold1 to econcer in hold2 as ho2

table file hold1
print history file product plant
on table save as coninfo
end

I have the same trouble... solution

table file pwrccb
print *
on table hold as hold1 FORMAT FOCUS INDEX ENCOCER
end

table file pwrccp
print *
on table hold as hold2 FORMAT FOCUS INDEX ENCOCER
end

join econcer in hold1 to econcer in hold2 as ho2

table file hold1
print history file product plant
on table save as coninfo
end

sorry for the other answer...

Alternate solution is to have both hold files sorted by the econcer field.
This is the cause of the message 1070, the sort order of both files is such that it is not in all cases an ascending order. The message basically means that a value (in this case for econcer) is found that is lower than the previous value.

1. On the Mainframe, when you get the prompt > >, just type ? 1070

2. Gerard is right that if you have both (sequential) hold files sorted in the same order, in your example you don't need to create FOCUS files.

3. If you do go the FOCUS files way, the best is to make the smallest file the FOCUS file. The other one can stay sequential.

4. If your extracted files are big (more than 20,000 records) there are techniques using TABLE (TABLEF when possible) and MODIFY, alternate master files with blocked fields, REBUILD INDEX, to make the load process much faster.