All,

I have searched the forum and have not found information on what I am looking for.

I have a report using several tables plus two hold files. I have "By'd" the hold files and the report all by their common field (work order number). I do not want my report sorted by this. I want it sorted by the close date. Any ideas?

The only thing I can think of is to make this report a hold file and basically re-create it based on itself so I can order by something different. (make sense?) It seems like there could be a better way.

This message has been edited. Last edited by: cevans,

You're joining several DBMS tables and HOLD files? There's a common column used in the JOINs? At the very least, you can create a HOLD file with the data retrieved from the JOINed tables and HOLD files and then use that for the report, sorted on whatever column you'd like.

I think you will have to formulate your question a little more clearly, I can't figure out what the problem is.

Francis,

Thank you, I think you did understand the question and told me what I had suspected.

I have a report based on tables and hold files. These are obviously joined together. As I have found, when using hold files in joins, everything needs to be sorted by a common column. I want to order the final report by a field not found in either hold. The file is found in a table the holds are joined to.

I planned on making this report, which I must sort on a field I do not want to, a hold file in order to be able to sort on a different field. I just thought there may have been something built in to solve this so one does not have to basically build the same report twice in the same procedure.

The hold files only need to be sorted in the same order to do the join. The output report from the join structure can be sorted on any field that you like. Or am I misunderstanding you?

Ginny,

That is what I had thought but the report only worked when I sorted by the same field as the hold files. I received an error when attempting to sort by another field.

I have gone ahead and done what Francis suggested and everything is working so thanks to both of you for the input.

Craig

P.S.: Sorry for the confusing explanations. I had a difficult time describing this problem.

"I received an error"??? What was the error?

I recieved:

"(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED"

That's because the HOLD files you're joining to must be sorted in the correct sequence - ordered by that famous common field, work order number.

Then you can sort the report in any sequence.

They are sorted by the same field (WONUM). Two clips from the procedure of parts of the two holds:

TABLE FILE NG_WORKORDER
PRINT
NEW
NEWDATE2
BY WONUM
HEADING
""
FOOTING
""
WHERE NEW EQ 'NEW';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS NG_WO_NEW_DATE

.
.
.

TABLE FILE NG_WORKORDER
PRINT
WQA
WQADATE2
BY WONUM
HEADING
""
FOOTING
""
WHERE WQA EQ 'WQA';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS NG_WO_WQA_DATE

Just for grins, change the hold statements to say ON TABLE HOLD AS ... FORMAT ALPHA. I find that tends to work betting in WebFOCUS.

Also, are the WONUM fields in the two files the exact same format? Do a ? HOLD holdname after each of the hold file creations to see.

ALPHA has not worked out for me in the past so I tend to stay away from that but I checked and the fields are the same format. This problem has been solved using the suggestion Francis had made earlier.

Craig