How do we join two focus files directly based on multiple fields? The fields are not indexed. Is there any alternative to going for hold files?

If the fieldsyou are joining to are not indexed then you can't do the join anyway with FOCUS files. You can with some non-FOCUS files like SQL Server or Oracle.
Syntax is
JOIN field1 and field2 IN FILE1 to field1 and field2 IN FILE2 AS joinname
END
If you have FOCUS files (ie .FOC) and the fields are not indexed then you will need to use MATCH not JOIN.

Or you can firts create a two holdfiles where you create an indexed new key based upon the two keys

So
DEFINE FILE XXX
NEWKEY=FIELD1||FIELD2;
END
TABLE FILE XXX
PRINT *
BY NEWKEY
ON TABLE HOLD AS HOLD1 FORMAT FOCUS INDEX NEWKEY
END
etc

Frank,

Per you comments, I tried concatenating multiple fields for a single field join. However, I get an error: (FOC279) NUMERIC ARGUMENTS IN PLACE WHERE ALPHA ARE CALLED FOR.

I assume it is because I'm concatenating alpha and numeric fields. WEEK_ID/I11, DEPT_ID/I6 and DIVISION_ID/I6 are integers and BUYER_VENDOR/A6 is an alpha.

DEFINE FILE F_INVE_VLR52W
NEWKEY=WEEK_ID||DEPT_ID||DIVISION_ID||BUYER_VENDOR;
END

Short of concatenating multiple fields for a single field join, is it at all possible to join multiple unique fields from 2 holds that are indexed?

I have 2 holds that have 4 fields that are indexed, WEEK_ID, DEPT_ID, DIVISION_ID and BUYER_VENDOR. When I join all 4 fields, Focus creates 4 separate joins, creating 4 separate sets of query tables unique to each field join.

The following is syntax for the holds:
. . . .
ON TABLE HOLD AS HOLD_INV_AVG FORMAT FOCUS INDEX WEEK_ID DEPT_ID DIVISION_ID BUYER_VENDOR
. . . .
ON TABLE HOLD AS HOLD_52W_VND FORMAT FOCUS INDEX WEEK_ID DEPT_ID DIVISION_ID BUYER_VENDOR
. . . .

The following join works in SQL (Access):
FROM HOLD_INV_AVG LEFT JOIN HOLD_52wk_VND ON (HOLD_INV_AVG.buyer_vendor = HOLD_52wk_VND.buyer_vendor) AND (HOLD_INV_AVG.week_id = HOLD_52wk_VND.week_id) AND (HOLD_INV_AVG.department_id = HOLD_52wk_VND.department_id) AND (HOLD_INV_AVG.division_id = HOLD_52wk_VND.wlsr_division_id)

Please check page 590 of manual 760snf.pdf, section called "Multi-field Joins to Fixed Format Files". You can't use FOCUS files but you can use hold files. Just make sure that both files are sorted in the same order.

You can download this manual (Summary of New Features, 7.6) from the doc link at the top right of this page.

If your original files are SQL I would do the join in sql too and build the report on that view.

Which gave me another idea, Frank.

AFS, since you are on 765, you can try SAME-DB altough there are a couple of issues with it.

Look it up and see if it might work for you.

Another possibility which I use now and then, is to join on one field (the indexed field in the to-file) and do the rest of the join by means of where statements. This will always give me the same results as if I were joining on more fields.
So: join field1 in a to field1 in b
And: where a.field2 eq b.field2 and a.field3 eq b.field3
This will do the trick, but you will have to have at least one field indexed in the to-file to be able to join at all (as already indicated earlier).

GamP,

Per your suggestion, I tested my fex by joining on one indexed field with rest of the join by means of a where statement. However, I'm not getting the same resolute as I get from SQL.

Example Code.........
ON TABLE HOLD AS HOLD_INV_AVG FORMAT FOCUS INDEX WEEK_ID
..........
ON TABLE HOLD AS HOLD_52wk_VND FORMAT FOCUS INDEX WEEK_ID
..........
JOIN
LEFT_OUTER HOLD_INV_AVG.SEG01.WEEK_ID IN HOLD_INV_AVG TO UNIQUE
HOLD_52WK_VND.SEG01.WEEK_ID IN HOLD_52wk_VND AS J1
END
TABLEF FILE HOLD_INV_AVG
PRINT
COST
SALES
BY WEEK_ID
BY DEPT_ID
BY DIV_ID
BY VENDOR_ID
WHERE (( HOLD_INV_AVG.DEPT_ID EQ HOLD_52wk_VND.DEPT_ID ) AND ( HOLD_INV_AVG.DIVISION_ID EQ HOLD_52wk_VND.DIVISION_ID ) AND ( HOLD_INV_AVG.BUYER_VENDOR EQ HOLD_52wk_VND.BUYER_VENDOR ));
..........

With the where above, it come out as:
WEEK_ID DEPT_ID DIV_ID VENDOR_ID COST SALES
200732 1 1 123320 258,622.76 285,451.53
200832 1 1 123320 263,554.93 291,653.50

Without the where, It come out as:
WEEK_ID DEPT_ID DIV_ID VENDOR_ID COST SALES
200732 1 1 123320 258,622.76 285,451.53
200732 8 1 123320 258,622.76 285,451.53
200832 1 1 123320 263,554.93 291,653.50
200832 8 1 123320 263,554.93 291,653.50
200832 9 1 123320 263,554.93 291,653.50

From SQL, with the same where as above:
WEEK_ID DEPT_ID DIV_ID VENDOR_ID COST SALES
200732 1 1 123320 258,622.76 285,451.53
200732 8 1 123320 1,043.85 1,185.69
200832 1 1 123320 263,554.93 291,653.50
200832 8 1 123320 790.4 887.85
200832 9 1 123320 0 0

Ginny & Frank, thank you for you comments. They were very helpful as well.

quote:

Example Code.........
ON TABLE HOLD AS HOLD_INV_AVG FORMAT FOCUS INDEX WEEK_ID
..........
ON TABLE HOLD AS HOLD_52wk_VND FORMAT FOCUS INDEX WEEK_ID
..........
JOIN
LEFT_OUTER HOLD_INV_AVG.SEG01.WEEK_ID IN HOLD_INV_AVG
TO UNIQUE HOLD_52WK_VND.SEG01.WEEK_ID IN HOLD_52wk_VND AS J1
END

You didn't show the table requests that create the Holds. If they use single verbs (resulting in a single-segment Focus structure) then the join is not "UNIQUE". That would explain the data discrepancy - you are not retrieving all instances.

Either use "conditional join" syntax (placing conditions that ensure uniqueness in the Join rather than in the Table), or change UNIQUE to MULTIPLE, or (better yet) use multiple verbs in the second Hold request, so that the join on WEEK_ID is unique.
TABLE ...
SUM ... BY WEEK_ID
SUM ... BY WEEK_ID BY other keys
ON TABLE HOLD ... FORMAT FOCUS INDEX WEEK_ID
END

Note that the "from" file need not be indexed, in fact it need not be a Focus file.

This message has been edited. Last edited by: j.gross,

Thanks Jack!

Just by changing the JOIN to MULTIPLE, gave me the resolute I'm looking for. Now I'm able to finish my TYLY report with MATCH.

DIV_ID VENDOR_ID DEPT_ID WEEK_ID COST SALES
1 110603 1 200732 $224,361.21 $275,703.39
1 110603 1 200832 $224,499.27 $272,711.98
1 110603 6 200732 $8,293.20 $9,634.60
1 123320 1 200732 $258,622.76 $285,451.53
1 123320 1 200832 $263,554.93 $291,653.50
1 123320 8 200732 $1,043.85 $1,185.69
1 123320 8 200832 $790.40 $887.85

Thank you, all!