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?

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.

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)

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

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

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

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.

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

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