 |
|||||
|
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 |
| Multi-table MFD's | Login/Join |
| Member |
Mainframe Focus, accessing DB2 tables. (This may apply to WebFocus also.) We use many multi-table MFD's for automatic joins: MFD: .. .. segname = main,.. .. field=business_code,business_cd,A4,A4.. .. .. segname = smalltab,segtype=U,parent=main,$ field = ,business_code,A4,A4,.. field = business_desc,business_desc,A20,A20.. AFD: .. .. segname = main... .. segname = smalltab.. keys=00,write=yes,keyorder=low, keyfld = business_cd, ixfld = business_code The keyfld matches the DB2 name in the MFD for Main. The ixfld matches the DB2 name in the MFD for Smalltab (not shown). This works fine as long as keyfld and ixfld are the same format. If ixfld is I6,I4 (smallint in Db2) but keyfld is A3,A3, of course it doesn't work. I tried putting DEFINE BUSINESS_NR/I6 = EDIT(BUSINESS_CD) after the field in MFD Main seg and using it as keyfld. Doesn't work. Any suggestions, please? Phyllis | ||
|
| Expert |
Phyllis, would the WITH command in your JOIN work for you? Read up on "Define-based Joins" When you define a field for the basis of your join Instead of JOIN key IN file1 TO key IN file2 AS J1 JOIN definedfield WITH key IN file1 TO somekey IN file2 AS J1
| |||||
|
| Virtuoso |
DB2 doesn't like you trying to change formats on it in the MFD. However have you looked into define based join as an option? We have to do so due to differences in how school codes are stored in different tables, one place A7, another I9. Leah | |||
|
| Virtuoso |
Koslo, In a multi-table master you must have same formats for keyfld and ixfld. If you use DEFINE-based joins (not in the MASTER) then the JOINs will not be performed by DB2 but by FOCUS which could impact on performance. You could use SQL passthru or ask the DBA to normalize the database. Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF | |||
|
| Master |
Rel dbs will eqi join a number to an alpha by making the required conversion (Oracle,SQLServer). However with wf you will require field=business_code,business_cd,I4,A4.. .. .. segname = smalltab,segtype=U,parent=main,$ field = ,business_code,I6,I4,.. To make the access file version work even though a dynamic join command would work! Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2 Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo | |||
|
| 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 |