I have two hold files that I'm attempting to JOIN together to add a value from one file to every corresponding value in the other file. Here is a generic representation of what I have and the desired output:

Table 1:
ID1, ID2, ID3, ID4, Value1
1, 2, 3, 4, 500
1, 2, 5, 6, 800
1, 2, 7, 8, 750

Table 2:
ID1, ID2, ID3, ID4, ID5, ValueA, ValueB
1, 2, 3, 4, 11, 100, 25
1, 2, 3, 4, 12, 250, 100
1, 2, 3, 4, 13, 300, 75
1, 2, 5, 6, 11, 200, 125
1, 2, 5, 6, 14, 225, 90
1, 2, 7, 8, 11, 75, 50

Desired Output After Join:
ID1, ID2, ID3, ID4, ID5, ValueA, ValueB, Value1
1, 2, 3, 4, 11, 100, 25, 500
1, 2, 3, 4, 12, 250, 100, 500
1, 2, 3, 4, 13, 300, 75, 500
1, 2, 5, 6, 11, 200, 125, 800
1, 2, 5, 6, 14, 225, 90, 800
1, 2, 7, 8, 11, 75, 50, 750

So far I have not been able to make this work. In the example above, I'll bring Value1 from Table 1 to the first corresponding record, but all subsequent records bring in Value1 as '0'.

When I set this up as a SQL Join I get the exact output I want, but the FOCUS Joins don't seem to be working the same. Any suggestions.

shealy
we'ld have to see your masters
(so we can see the indexes) or
the code that produced the extract files (if by 'hold' files you mean that they are extract files)

then we'ld have to see your join
JOIN ID1 AND ID2 AND ID3 AND ID4 IN TABLE2 TO ID1 AND ID2 AND ID3 AND ID4 IN TABLE1 AS J2
then table against TABLE2
or..
JOIN ID1 AND ID2 AND ID3 AND ID4 IN TABLE1 TO ALL ID1 AND ID2 AND ID3 AND ID4 IN TABLE2 AS J1
and table agains TABLE1

Shealy

Susannah is right, the master files and your code would be most helpful.

If they are truly HOLD files, then a JOIN table2 to table1 would be the correct approach. That would be Susanah's first example.

More information please.

Shealy :
Looks like you need use the ALL option. By default, a FOCUS join only connects to 1 record from the right-side file, where as SQL join is an INNER JOIN by default and is a one-to-many default.

Try this..
JOIN CLEAR *
JOIN ID1 AND ID2 AND ID3 AND ID4 IN TABLE1 TO
ALL ID1 AND ID2 AND ID3 AND ID4 IN TABLE2 AS JN01
END
-RUN

Sandeep Mamidenna
-*

Sandeep

If the files are FIX format flat files, ie standard HOLD files, the unique join from table2 to table1 is correct. Using the ALL from table1 into table2 will not give the correct results. This is because of the way that flat files are read, in sequence order only.

Unfortunately we don't have our full metadata environment set yet, so at this time I'm pulling directly from SQL Server via SQL passthru. Below the the procedure I'm attempting to use once the pass through is complete (note: all 'id' fields are indexed keys on SQL Server)

--------------------------------------------------------------------------
TABLE FILE SQLOUT
SUM
COMPUTE cli_prod/A50 = IF close_client_name EQ open_client_name THEN 'Primary - ' | close_productcat
ELSE close_client_name | ' - ' | close_productcat;
COMPUTE cli_prod_id/I1 = IF close_client_name EQ open_client_name AND close_productcat EQ open_productcat THEN 1
ELSE IF close_client_name EQ open_client_name AND close_productcat NE open_productcat THEN 2
ELSE 3;
clicks
submits
installs
BY open_client_id
BY open_productcat_id
BY close_client_id
BY close_productcat_id
BY partner_id
ON TABLE HOLD AS EVENTS
END
-RUN
TABLE FILE EVENTS
PRINT
COMPUTE primary_submits/I6 = submits;
BY open_client_id
BY open_productcat_id
BY close_client_id
BY close_productcat_id
BY partner_id
WHERE cli_prod_id EQ 1;
ON TABLE HOLD AS PRI_ORD
END
-RUN
JOIN CLEAR *
JOIN
EVENTS.EVENTS.open_client_id AND EVENTS.EVENTS.open_productcat_id AND
EVENTS.EVENTS.partner_id
IN EVENTS
TO
PRI_ORD.PRI_ORD.open_client_id AND PRI_ORD.PRI_ORD.open_productcat_id AND
PRI_ORD.PRI_ORD.partner_id
IN PRI_ORD
AS JN01
END
TABLE FILE EVENTS
SUM primary_submits
submits
installs
BY open_client_id
BY open_productcat_id
BY partner_id
BY close_client_id
BY close_productcat_id
ON TABLE SET LINES 999999
ON TABLE SET PAGE NOPAGE
END
--------------------------------------------------------------------------
The returned results are only adding 'primary_submits' value to the first instance of the JOIN sequence. All other matches are being assigned 0.

I stand corrected Alan, Thank you. Guess I should have given my answer a little more thought before I pulled the trigger.

Gooooooooooood day !!
Sandeep Mamidenna

I'll wait to be proved wrong on this!

A join between two HOLD files cannot be compared to a join between SQL tables. A HOLD file (or any flat file I believe) is read only from first record to last record, and that includes the x-referenced file. A read cannot move back in the file, as happens in SQL.

On first glance I would have said that this should work. Then I noticed that the sort to create the HOLD files used 5 BY fields, with the JOIN on BY fields 1,2 and 5. Please move the BY field number 5 to be number 3 on both requests.

I am hopeful that this could be the cause of your problem.

Otherwise, I do question the need to have a join at all. I would have thought you could use
COMPUTE primary_submits/I6 = IF cli_prd_id EQ 1 THEN submits ELSE 0;

and then just have one pass of the EVENTS HOLD file.

If this fails, may I suggest MATCH!

You might use the command

ON TABLE HOLD AS...FORMAT FOCUS INDEX ....

Or

maybe instead of JOIN you can use the command

MATCH FILE ....
etc

ON TABLE HOLD OLD-AND-NEW

if you use the GUI it will help you into the good direction.

Alan it looks like your suggestion to group the 'BY' fields in the proper order has done the trick! I'm not sure why that didn't occur to me before. Probably b/c of the hierarchy sequence I tend to view my data in (open client, close client, partner, etc).

In case you're interested, I initially thought the same thing about no need for the JOIN, and tried something very similar to what you suggest here, but this won't work b/c I need the primary_submit value when cli_prod_id = 1 associated with every record regardless of the id.

MATCH was giving me similar problems, but it may work now using the same order logic.

I appreciate everyone's input!