 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| DB2 Table Extract Based on Date | Login/Join |
| Member |
Hello all, I would like to select all rows from a mainfrance DB2 table which contain the maximun date on the table. The MFD field is: FIELD=RUN_D ,RUN_D ,A10 ,A10 in the format MM/DD/YYYY. What is the best way to do this? Thanks to all for the help! mainfrane IBM ZOS Focus version 7 WebFocus 532 soon to be 714 IBM OS/390 | ||
|
| Platinum Member |
kt, If you can use SQL passthru, a subselect like; SELECT * FROM <TABLENAME> WHERE RUN_D IN (SELECT MAX.RUN_D FROM <TABLENAME>) would work. Kevin WF 7.6.10 / WIN-AIX | |||
|
| Virtuoso |
Probably the quickest (but not necessarily most efficient) is to use BY HIGHEST 1. For Example TABLE FILE CAR PRINT CAR BY HIGHEST 1 COUNTRY END You get all the car records that contains the MAX value of country: W GERMANY AUDI BMW What it's actually doing is retrieveing all records, then throwing them all out except for those that contain the highest sort value of COUNTRY. IF you don't have other selection criteria and your record count is extrememly large, this may not be the best solution. 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 | |||
|
| Virtuoso |
The following example would also work and is a little more efficient: TABLE FILE CAR SUM COMPUTE MAXCTY/A10=MAX.COUNTRY; NOPRINT SUM CAR BY COUNTRY BY CAR NOPRINT WHERE TOTAL COUNTRY EQ MAXCTY END Most efficient would probably be one request to determine the MAX.RUN_D, hold then either join to that holdfile or use WHERE RUN_D EQ (HOLDFIL) You can see there are several options. Any other ideas out there? 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 | |||
|
| Virtuoso |
I think the answer is a bit more complex. the field RUN_D is an alpha field with format MM/DD/YYYY, so if you try to find the max value of this field as it is you will get the wrong answer (or at least an answer you can not trust) try to find the max value in this small list 01/01/2008 01/05/2006 12/05/2007 12/13/2006 12/14/2007 the maxvalue is the first of jan 2008, but if you sort them in alpha format you will get the december 14 2007 as answer. so firts you have to convert this alpha field to real date field and then find the highest value
| |||||||
|
| Virtuoso |
Sorry - missed that. Frank is correct - convert it to a real date format (like A8yymd) first using edits. Then the rest would work. 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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |