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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How to create one masterfile for 2 tables

Read-Only Read-Only Topic
Go
New
Search
Notify
Tools
How to create one masterfile for 2 tables
 Login/Join
 
Frans
Guru
posted
Hi all,

I've got two tables; one with current data and one with historical data. The structure is almost identical (98%).

At this moment I'm using the match function to report out of both tables; but matching is a bit slow. I can't join them because the current data and historical data have no relation.

Does anybody have a better idea to make one table out of these two?
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Alan B
Virtuoso
posted Hide Post
Have you investigated using MORE.
TABLE FILE OLDFN
PRINT FLD1 FLD2
BY FLD3
MORE
FILE NEWFN
END

The restriction is that ALL fields used have to be available in both files. If not you can DEFINE dummy fields for the 2%.
DEFINE FILE OLDFN
FN_IN_NEW_NOT_OLD/A12=' ';
END
TABLE FILE OLDFN
PRINT FLD1 FLD2 FN_IN_NEW_NOT_OLD
BY FLD3
MORE
FILE NEWFN
END

It may work for you and is more efficient than MATCH.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Frans
Guru
posted Hide Post
OK thaks for your input. I've created 2 new masterfiles with identical tables.

I've made this:
-* File more.fex
TABLE FILE ORDERHIST
PRINT
ORDERNR
VOLGNR
BY DOSVOLG
MORE
FILE ORDER
WHERE (OPDRDEB EQ 123290)
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
END

I'm getting this error:

0 ERROR AT OR NEAR LINE 10 IN PROCEDURE more FOCEXEC *
(FOC953) INVALID STRUCTURE IN SUBREQUEST: ON
BYPASSING TO END OF COMMAND


I'm running WebFOCUS 7.6 an a IBM Universe 10.2 database (multivalue)


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
quote:
TABLE FILE ORDERHIST
PRINT
ORDERNR
VOLGNR
BY DOSVOLG
MORE
FILE ORDER
WHERE (OPDRDEB EQ 123290)
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
END



When using MORE, I always HOLD, then report. Put a semi-colon after the WHERE...
I assume OPDRDEB is an Integer column; if not, put quotes around 123290
Your ORDER file must be SORTed and all columns the same as in the ORDERHIST file:


TABLE FILE ORDERHIST
PRINT
ORDERNR
VOLGNR
BY DOSVOLG
ON TABLE HOLD
MORE
FILE ORDER
PRINT
ORDERNR
VOLGNR
BY DOSVOLG
WHERE (OPDRDEB EQ 123290);
END
-RUN
TABLE FILE HOLD
PRINT *
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
END
-EXIT

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
hammo1j
Master
posted Hide Post
Frans

TABLE FILE ORDERHIST
PRINT
ORDERNR
VOLGNR
BY DOSVOLG
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
MORE
FILE ORDER
WHERE (OPDRDEB EQ 123290)
END

will work. MORE only permits IF or WHERE statements to follow since the rest of the command is assumed to be identical.

I do what you did all the time myself.

Regards

John


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
 
Posts: 888 | Location: Airstrip One | Registered: October 06, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
Frans

You did not tell us what kind of table this are.
If it p.e is in SQL you can start with a union select in SQL and use that view for building your master.

In that case you only have one tabel select.

Frank




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Frans
Guru
posted Hide Post
Hi Frank,

These are kind off special tables since Universe has multivalued data. So this is not an relational table but a dimensional table.

I have created a report now with the MORE function, but I think it works slower then MATCH.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
I can not imagize why MORE is slower than MATCH

MORE just appends the two reports and does not have to look at the content of the fields.

There must be an other reason for the performance problem, did you real measure the response?

btw you shoud add some info in the signature like the system and version you run.

Frank




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
smiths
Platinum Member
posted Hide Post
With my version of WF, ReportCaster fails on MORE, so I needed to remove MORE in favour of APPEND, as in

FILEDEF HFILE DISK hfile.ftm (APPEND

I don't know the extent of WF versions impacted by this though (5.2 for sure).

Regards,
Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Report 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     How to create one masterfile for 2 tables

Copyright © 1996-2020 Information Builders