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     Join on only a maximum row

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Join on only a maximum row
 Login/Join
 
<Patch4Ever>
posted
I am trying to join two tables. I only need the rows in the second table that have the maximum date found in one of the fields it contains. An example would be to pull only rows that have a value of '2007/03/31 07:32' in the field called date modified. The format of the field is HYYMDI. Any help would be appreciated!
 
Report This Post
Leah
Virtuoso
posted Hide Post
quote:
I am trying to join two tables. I only need the rows in the second table that have the maximum date found in one of the fields it contains. An example would be to pull only rows that have a value of '2007/03/31 07:32' in the field called date modified. The format of the field is HYYMDI. Any help would be appreciated!


I assume your question is only wanting the max date value not the actual join. Have you tried the 'MAX.' operator to get the highest date?


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<Patch4Ever>
posted
No, I DO want the join to pull all row information for that max date. I DO NOT want to pull all the rows back and then decide in the query which one has the max date. Obviously, if I use a Multiple in my join it will pull all rows and not using it will give me the first occurance (or in my case the min date) it finds.
 
Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Patch,
I assume that you have a 1-to-many join between the 2 tables.
So, if you use BY HIGHEST 1 date_modified , you should get the row with the maximum value of date_modified .


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Darin Lee
Virtuoso
posted Hide Post
That doesn't eliminate the joins to all the other rows, it just throws everything out from the matrix that doesn't belong to the HIGHEST 1 record, so performance is going to take a hit. We also do it this way in many cases where I am trying to pull a most recent record for a claim from this history table.
I have two other suggestion which we also use. The first requires actually storing/updating the value of the max timestamp on the associated records in the host file. Requires a bit more maintenance, but saves a ton of time for reporting. The second suggestion is simply to use the BY HIGHEST 1 to get the max record, hold that, and then do a second pass with the JOIN.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
<Patch4Ever>
posted
Good suggestions. The BY HIGHEST 1 works fine and gives me the max record that I need. I also used the get max record from the 2nd table, joined it to the first table, and then continued on. This worked as well. My wish was I could do something in the actual join statement. Thanks for the suggestions.
 
Report This Post
Darin Lee
Virtuoso
posted Hide Post
There IS such a thing as conditional joins, where the join is only performed if a condition on a field in the host file is met. This wouldn't work in your case, however, because MAX. is only known after the matrix is loaded, not as the records are being read and joined to the crfield.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
One thing to watch, though, is that BY HIGHEST 1 does not get translated to SQL, all the rows are returned to WebFOCUS before the HIGHEST 1 is determined.

This
TABLE FILE BASEL_TIME_D
SUM
TIME_DIM_KEY
BY HIGHEST 1 TIME_DIM_KEY
END
produces this SQL
SELECT T1."TIME_DIM_KEY", SUM(T1."TIME_DIM_KEY")
FROM BASEL.TIME_D T1 GROUP BY T1."TIME_DIM_KEY"
ORDER BY T1."TIME_DIM_KEY" DESC
FOR FETCH ONLY;


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
Darin Lee
Virtuoso
posted Hide Post
quote:
That doesn't eliminate the joins to all the other rows, it just throws everything out from the matrix that doesn't belong to the HIGHEST 1 record, so performance is going to take a hit.


Right. This was what I was alluding to. It still performs joins on all the records retrieves everything into the matrix, then FOCUS throws everything except the Highest 1 record out. So if you've got a large DB, you could take a good hit on IOs, CPU time, etc.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report 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     Join on only a maximum row

Copyright © 1996-2020 Information Builders