Hi,

I am new to FOCUS language. Can someone throw some light on the difference between JOIN Command & MATCH FILE Command. And in what scenario each of these are used.

Thanks.

This message has been edited. Last edited by: <Kathryn Henning>,

You should look at the WebFOCUS Keysheet Release 8.0.2 - Gives lots of good information about Match and Join commands.

All of the WebFOCUS commands in one document!

Vivian

Hi Vivian,

Where can i find "WebFOCUS Keysheet Release 8.0.2"??

There are also courses provided by IBI on these.

The one thing that needs to be about JOIN is the way that it works. Especially in combination with SET ALL setting.

There was a great post done a couple of years ago in the forum. You should try to look it up.

Hello Mayank,

You can find this at www.aviter.com.

If you have any questions, feel free to contact me at Vivian@aviter.com.

Vivian

Hi,
The WebFOCUS manual called "Creating Reports With WebFOCUS Language" has two chapters dedicated to "Joining Data Sources" and "Merging Data Sources".

The two Information Builders training courses that explain JOIN and MATCH in a fair amount of detail are:
Course 351: Building Reports with Report Painter (Part 1)
Course 354: Building Reports with Report Painter (Part 2)
The 2nd course compares JOIN ands MATCH.

A (somewhat long-winded) helicopter view:
A JOIN is a temporary, virtual connection between two or more physical data sources. The data in the host file determines which records are retrieved from the cross-reference file. When a record from the host file of a one-to-many join has no corresponding record(s) in the cross-reference file, the record is automatically excluded from the report output. To control this, you could SET ALL to ON, in which case records from the host file, without corresponding records in the cross-reference file, will be displayed in the report output.

If you JOIN two files: WebFOCUS retrieves a record from the first data source (host file) and then searches for a corresponding record (or records) in the second file (cross-reference file), and stores the results in an Internal Table, which is eventually formatted to produce the report. NB: Only matching records are retrieved from the second file.

MATCH allows you to merge information from two or more data sources and capture the results (selected records and requested columns) in a HOLD file. If a report needs to be produced, then it must run as a subsequent request off the HOLD file.

If you MATCH two files: WebFOCUS retrieves the requested records from the first data source and writes the output to a temporary work area. WebFOCUS then retrieves the requested records from the second data source and writes the output to a temporary work area. WebFOCUS then merges the results based on the high-order sort fields and writes the merged results to an Internal Table. The results in the Internal Table are then written to a HOLD file.
If a report is required, you must then run a TABLE request against the resultant HOLD file.

So there is a lot more processing, and potentially Disk IO, that goes on in a MATCH process than in a JOIN process!

If the required indexes etc. are in place, and if you need an Inner Join or a Left Outer Join, then generally your starting point would be a JOIN.
However, MATCH is ideally suited if you need more capabilities than JOIN – such as merging data sources which do not have indexes, or such as merging two unsorted flat files, or such as merging ALL the requested records from the first data source with ALL the requested records from the second data source.

Focal point should have LIKE button in place.

Thanks ;-)

This is great! Would love to see more items like this for us Newbies.