Hi,

I am trying to run a Full Outer Join on to HOLD files. However, there are no options in Webfocus to do so in the GUI. So I was told to try the MATCH command. But when I used this command, the results seemed off sinec it would match the columns incorrectly.

I had the following code:
SET ALL = ON
-RUN
MATCH
FILE STEP1
SUM SHARES CAPITAL2
BY SYMBOL BY ORDER_ACTION
RUN
FILE STEP2
SUM FILL_AMOUNT CAPITAL
BY SYMBOL BY ORDER_ACTION
AFTER MATCH HOLD OLD-OR-NEW
END

The results seemed off because for a record that was in one table and not the other, it would skew the matching and throw off all the calculations.



WFSrv 7.64
WF Dev Studio 7.64

This message has been edited. Last edited by: Kerry,

if doing with a match i think you would want:
AFTER MATCH HOLD OLD
you would get all rows from STEP1 and the matching rows from STEP2

Doesn't this work?

SET ALL - PASS
JOIN LEFT_OUTER X IN Y TO ALL X IN Z AS J1

Frans - that would only get you a left outer join. That would be all the records in the left table along with any matches that were found in the right table. It looks like Ben is trying for a union more or less, which would be all the records from both tables, combining where possible the records that match on SYMBOL and ORDER_ACTION.

A match would get you what you need, with OLD-OR-NEW. Not sure why you're not getting what you need without seeing the master files and an example of the data and output.

quote:

BY SYMBOL BY ORDER_ACTION

What are the declarations for the two match-key fields in the two data sources? If the declarations are inconsistent, Focus will merge without regard to the key-field values.

I have an issue with a LEFT_OUTER join. If I say:

JOIN
LEFT_OUTER QUERYTOT.QUERYTOT.JOINKEYTOT IN QUERYTOT TO QUERY2A.QUERY2A.JOINKEYA IN QUERY2A
AS J1
END

I don't get the rows I need. If I put in the "ALL"

JOIN
LEFT_OUTER QUERYTOT.QUERYTOT.JOINKEYTOT IN QUERYTOT TO ALL QUERY2A.QUERY2A.JOINKEYA IN QUERY2A
AS J1
END

I get anerror nessage stating that I have exceeded max messages:

Reporting Server messages exceeded IBIF_max_messages, report retrieval aborted.

Please, contact your WebFOCUS administrator


Any ideas?

Are your calcs skewed because of missing data? If so, prior to the MATCH you can:

SET NODATA=0 

If you're adding SHARES to FILL_AMOUNT and CAPITAL to CAPITAL2, do so in a subsequent DEFINE. If not, are your numbers now good?

FYI, if you want a simple append of both files (not truly a full outer join) have you tried the MORE command? Only prerequisite is that all fields must be in the same format. In effect, the 2 hold files are appended. Resultant field names will be the names from the first file mentioned.

TABLE FILE STEP1
PRINT SYMBOL ORDER_ACTION SHARES CAPITAL2
ON TABLE HOLD AS STEP3
MORE
FILE STEP2
END
-RUN

(Note, this presumes SHARES and CAPITAL2 mean the same thing as FILL_AMOUNT and CAPITAL.)

quote:

get anerror nessage stating that I have exceeded max messages:

I believe your QUERY2A.QUERY2A.JOINKEYA has more than one record to match with QUERYTOT.QUERYTOT.JOINKEYTOT column. Webfocus will generate some warning messages saying that "DUPLICATES IN JOIN 'FROM' FIELD :" at the back end When your query returns more numbers of records and then the warning message also will get increasing for each duplicates. Started throwing this error when number of rows exceeds some limit

Use MATCH to get the same data to avoid the issue.