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] XFOCUS DB - Multi-field Joins

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] XFOCUS DB - Multi-field Joins
 Login/Join
 
Francis Mariani
Expert
posted
Is anyone else as annoyed as I am about the lack of multi-field joins in FOCUS databases? I had hoped this feature was introduced with XFOCUS, but it appears not.

This message has been edited. Last edited by: Francis Mariani,


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
Waz
Expert
posted Hide Post
I thought that multifield joins was coming in, in one of the later releases ?

I had hoped that a conditional join would work.

Seems to work for Loaded FOCUS files, but not Held FOCUS files.

JOIN FILE VIDEOTRK AT MOVIECODE TAG V1 TO ALL 
     FILE MOVIES   AT RELDATE   TAG M1 AS JW1
-*  WHERE DATEDIF(RELDATE, TRANSDATE,'Y') GT 10;
  WHERE V1.MOVIECODE EQ M1.MOVIECODE;
END
TABLE FILE VIDEOTRK

 PRINT LASTNAME
 TITLE
-*ON TABLE HOLD
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Alan B
Virtuoso
posted Hide Post
There IS multi-field JOIN using MDI, plus there is external index.

Diligent use of these, together with intelligent partitions, provides excellent performance in a data mart application.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Alan, thanks for pointing me to MDI. I have some reading to do!

Multi-Dimensional Index (MDI)


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
Waz
Expert
posted Hide Post
Interesting reading...

This could be useful.

Good One


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
<JG>
posted
Sorry to throw an old spanner into the works.

XFOCUS as we all know requires a seperate licence.

But did you know that the multi-dimensional indexing functionality

requires yet another additional licence.

You can have the first without the second but not the second without the first.
 
Report This Post
Wep5622
Virtuoso
posted Hide Post
That does seem interesting indeed! What I can't gather from the documentation though is how you would create a HOLD file with an MDI?

Say we have:
TABLE FILE CAR
PRINT *
BY COUNTRY
BY CAR
ON TABLE HOLD AS foo FORMAT XFOCUS
END


How would you add an MDI index to that? Can that be done during the creation of the HOLD-file or does it require a separate REBUILD command? What would that look like?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
From the doc:

Building and Maintaining a Multi-Dimensional Index
The REBUILD command is used to create or maintain a multi-dimensional index. This command can be issued in a FOCEXEC.

The best MDI is built by specifying the dimensions in order of best cardinality (most distinct values).

To issue the REBUILD command in a FOCEXEC, you place the REBUILD command and the user-supplied information needed for REBUILD processing in the FOCEXEC.

If the MDI file might be larger than two gigabytes or if you plan to add more data partitions to it, the MDI index file must be partitioned from the initial REBUILD phase. After the index has been created, you can use it in a retrieval request. You cannot use an MDI for modifying the data source. If you update the data source without rebuilding the MDI and then attempt to retrieve data with it, WebFOCUS displays a message indicating that the MDI is out of date. You must then rebuild the MDI.

I wonder how many people have tried MDI - I don't think I've ever seen a comment about it on FocalPoint.


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
Wep5622
Virtuoso
posted Hide Post
The reason I asked was that it isn't clear to me from the docs what such a REBUILD command would look like. I even checked the docs about REBUILD and although that does contain an example, it contains no explanation of what the example achieves.

I often have this problem with the help-files BTW - either they end just when things get interesting, or they show some example that make you go "Huh, wot?!?" and leave it at that. I get the impression that the people responsible for the documentation much rather have us pay for a course than actually understand things from the docs. Not that I mind taking a meaningful course if I get offered one.

Regarding never hearing about MDI before, I can entirely imagine people reading "MDI" and thinking it's about: http://en.wikipedia.org/wiki/M...e_document_interface (where I have to add that I think the section about advantages/disadvantages isn't done very well).


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Francis Mariani
Expert
posted Hide Post
I agree with you completely regarding WF documentation. I once posted a question/comment of FP regarding the lack of JSP examples for the ReportCaster API and someone named "Gerry" responded stating there indeed was one example, though without any explanation of the code. It's very frustrating.

I always thought MDI meant "Modern Demolition Initiation", but I now realize there are many meanings.


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
Waz
Expert
posted Hide Post
More Licenses !



Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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] XFOCUS DB - Multi-field Joins

Copyright © 1996-2020 Information Builders