Focal Point
How to create one masterfile for 2 tables

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5771009122

April 05, 2007, 03:18 AM
Frans
How to create one masterfile for 2 tables
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?
April 05, 2007, 03:39 AM
Alan B
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
April 05, 2007, 09:05 AM
Frans
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.
April 05, 2007, 09:19 AM
Tom Flynn
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
April 05, 2007, 10:23 AM
hammo1j
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
April 05, 2007, 10:33 AM
FrankDutch
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

April 05, 2007, 11:00 AM
Frans
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.
April 05, 2007, 11:15 AM
FrankDutch
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

April 05, 2007, 12:19 PM
smiths
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