Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Report using hold files sorting

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Report using hold files sorting
 Login/Join
 
cevans
Member
posted
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,
 
Posts: 23 | Location: ABQ, NM | Registered: August 24, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
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


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
cevans
Member
posted Hide Post
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.
 
Posts: 23 | Location: ABQ, NM | Registered: August 24, 2009Report This Post
GinnyJakes
Expert
posted Hide Post
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
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
cevans
Member
posted Hide Post
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.
 
Posts: 23 | Location: ABQ, NM | Registered: August 24, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
"I received an error"??? What was the error?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
cevans
Member
posted Hide Post
I recieved:

"(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED"
 
Posts: 23 | Location: ABQ, NM | Registered: August 24, 2009Report This Post
Francis Mariani
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
cevans
Member
posted Hide Post
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
 
Posts: 23 | Location: ABQ, NM | Registered: August 24, 2009Report This Post
GinnyJakes
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
cevans
Member
posted Hide Post
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
 
Posts: 23 | Location: ABQ, NM | Registered: August 24, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Report using hold files sorting

Copyright © 1996-2020 Information Builders