Mainframe FOCUS v7.2.5
-***********
SET ALL=PASS
JOIN CLEAR *
JOIN POL_RES_SEQ WITH POL_NO IN HOME1 TAG H1
TO POL_RES_SEQ IN HOSUPP TAG X1 AS J1
END
JOIN POLRES_NBR WITH POL_NO IN HOME1 TAG H1
TO POLRES_NBR IN VALART TAG X2 AS J2
END
-***********
HOSUPP is a FOCUS DB with POL_RES_SEQ the key and is indexed.
VALART is a FOCUS DB with POLRES_NBR the key and is indexed.

Some records on VALART are active, some are cancelled. Only want to use the active VALART records and not drop any records from HOME1.

However the IF X2.CANC_CODE EQ 'ACTIVE' will cause the associated HOME1 records to drop if the VALART records are cancelled (not active).

Seems like the SET ALL=PASS has no effect.

What am I missing or doing wrong?

IF X2.CANC_CODE EQ 'ACTIVE' OR X2.CANC_CODE IS MISSING

OR MAYBE EVEN
SET ALL=ON
or
SET ALL=OFF

Not sure which ALL

Hope this helps

Tex,

Changed the IF to read:
WHERE X2.CANC_CODE EQ 'ACTIVE' OR X2.CANC_CODE EQ MISSING
still got the same invalid result.

According the the FOCUS manual, SET ALL=PASS "includes parent instances that are missing descnedants, even if WHERE or IF commands exist to screen fields in the descendant segments that are missing instances.

For laughs I also tried SET ALL=ON

Is seems that if I need all records from the Host file I can not screen records from the cross-reference file.

Thanks anyway for the suggestion.

Ed

Also noticed you are not joining to ALL.

Also what is HOME1

could that be it.

Tex,

HOME1 is a flat file. There is a hard and fast 1-to-1 relationship between HOME1 and HOSUPP and at most a 1-to-1 between HOME1 and VALART. (Not all HOME1 records have a corresponding VALART record.)

I think my only solution is to not screen on VALART; create a HOLD file that includes the X2.CANC_CODE field and then screen in a seocnd pass at the data.

Ed

I believe that there is a misunderstanding as to what SET ALL = PASS means. In general, using your files as example, if HOME1 exists but has no VALART records in the segment where CANC_CODE is, then HOME1 values would be returned (if tests on a child segment when no child segment instances exist are returned). Unless I am misinterpreting your problem, it appear that the ones that are being dropped have CANC_CODE values (segments with CANC_CODE exist - just not ones with the value active). ALL = PASS has no effect here.

Assume HOME1 join values of 1 and 2 and 3. Assume VALART values of join value/ CANC_CODE values of 1/active , 1/inactive, 3/inactive. No CANC_CODE values with a home1 value 2 exist.


The result of IF X2.CANC_CODE EQ 'ACTIVE' ALL = OFF would be be
1/active would be returned.

With ALL=PASS set, 1/active would be returned and 2/. would be returned.

I must say that I'm not sure that this is consistent behavior across all data sources but I do believe it to be true against FOCUS databases.

If you want to return all instances of the parent file (HOME1) and also want to know whether or not CANC_CODE EQ 'ACTIVE' instances occur, try defining a counter like CANC_CODE_counter/I1= IF CANC_CODE EQ 'ACTIVE' then 1 else 0; Then sum this counter by POL_RES_SEQ keeping the ALL = PASS setting. This should return all values of POL_RES_SEQ. A non zero value in the defined field would indicate that this POL_RES_SEQ had at least one CANC_CODE EQ 'ACTIVE' VALART segment instance.

Please disregard if I did not interpret your problem correctly.

Good luck

ET,

You are exactly right. Not all HOME1 records have a corresponding VALART record, and when HOME1 does, some of the VALART records are active, some are not. I want all HOME1 records, irregardles of whether or not they have corresponding VALART record. However, I only want VALART records that are ACTIVE.

My situation seems to fit the definition of SET ALL=PASS. The descendant file has missing records and I want to apply a filter against the descendant file, but not loose any parent records in the process.

Seems my choices are to write a MATCH FILE procedure, or to still use a JOIN but not screen on VALART - do that in a second pass.

Thanks,

Ed

Your assumptions on how ALL=PASS are correct. I've used it many times in the past to identify host records without a cross refernce record. Everything being equal, your example should work. However, since you are doing define based joins, I would check with NY is APP=PASS is even supported for that type of join.

Here is something I just thought about. Try doing a WHERE TOTAL. Something like

TABLE FILE ....
...
...
COMPUTE KEEP/A1 = IF X2.CANC_CODE EQ 'ACTIVE' OR X2.CANC_CODE IS MISSING THEN 'Y' ELSE 'N';
...
...
WHERE TOTAL KEEP EQ 'Y';
...
...
END

Hope this Helps

DHagen,
Based on your comment that the Define based Join might be the culrpit, I added a TABLE FILE procedure to create a hold file containing the two join fields. Then modified the JOINS accordingly - still no dice. Cancelled (inactive) VALART records cause the corresponding HOME1 host records to be dropped.

Tex,
I tried the the WHERE TOTAL approach. It had no impact.


So strange if i remove the IF, or this case the WHERE TOTAL, I get the all the HOME1 records, even those with the cancelled VALART records attached.

Ed

Ok, here's my two cents. SET ALL = OFF, ON or PASS is all about screening records from the host file, using the presence of or values from the cross reference file. I know of no way, with this feature, to get FOCUS to pretend there is no cross reference record when there is. You might try SET ALL = ON with a WHERE based join. I don't have a lot of experience with WHERE based joins myself, but it might work.

I tried to create an example using the car file. Hope this helps.

-*CREATE A FOCUS DATABASE

DEFINE FILE CAR
CANC_CODE/A8=
IF CAR EQ 'TOYOTA' OR 'JAGUAR' OR 'ALFA ROMEO'
THEN 'ACTIVE' ELSE 'INACTIVE';
END
TABLE FILE CAR
SUM CAR NOPRINT
BY COUNTRY
WRITE CANC_CODE
BY COUNTRY
BY CAR
IF COUNTRY NE FRANCE
ON TABLE HOLD AS VALART FORMAT FOCUS INDEX COUNTRY
ON TABLE SET HOLDLIST PRINTONLY
END

-* MASTER FOR THE FOCUS DATABASE_
FILE=VALART ,SUFFIX=FOC
SEGNAME=SEG01 ,SEGTYPE=S01
FIELDNAME =COUNTRY ,E01 ,A10 , FIELDTYPE=I, $
SEGNAME=SEG02 ,SEGTYPE=S01 ,PARENT=SEG01
FIELDNAME =CAR ,E02 ,A16 , $
FIELDNAME =CANC_CODE ,E03 ,A8 , $

-* CREATE A FILE OF JUST THE COUNTRIES

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD AS HOME1
END

-* DATA IN HOME1
COUNTRY
-------
ENGLAND
JAPAN
ITALY
W GERMANY
FRANCE

-* DATA IN VALART
TABLEF FILE VALART
PRINT SEG.CAR
BY COUNTRY UNDER-LINE
END
COUNTRY CAR CANC_CODE
------- --- ---------
ENGLAND JAGUAR ACTIVE
JENSEN INACTIVE
TRIUMPH INACTIVE
---------------------------------------
ITALY ALFA ROMEO ACTIVE
MASERATI INACTIVE
---------------------------------------
JAPAN DATSUN INACTIVE
TOYOTA ACTIVE
---------------------------------------
W GERMANY AUDI INACTIVE
BMW INACTIVE
---------------------------------------

-* JOIN AND DO SOME REPORTING THAT SHOWS ALL ITEMS IN HOME1 AND SHOW
-* valart items IF ACTIVE OR NOT

-* ALL = OFF IS THE DEFAULT HERE
JOIN COUNTRY IN HOME1 TO COUNTRY IN VALART AS J1
DEFINE FILE HOME1
CNT_ACT/I1=IF CANC_CODE EQ 'ACTIVE' THEN 1 ELSE 0;
END
TABLE FILE HOME1
SUM CNT_ACT
COMPUTE HAS_ACTIVES/A3=IF CNT_ACT GT 0 THEN 'YES' ELSE 'NO';
BY COUNTRY
END

COUNTRY CNT_ACT HAS_ACTIVES
------- ------- -----------
ENGLAND 1 YES
ITALY 1 YES
JAPAN 1 YES
W GERMANY 0 NO


-* NOW SET ALL = PASS NOTE THAT FRANCE (NOT ON VALART) NOW APPEARS
-* SETTING IT TO ON WOULD YIELD THE SAME RESULT
TABLE FILE HOME1
SUM CNT_ACT
COMPUTE HAS_ACTIVES/A3=IF CNT_ACT GT 0 THEN 'YES' ELSE 'NO';
BY COUNTRY
ON TABLE SET ALL PASS
END

COUNTRY CNT_ACT HAS_ACTIVES
------- ------- -----------
ENGLAND 1 YES
FRANCE . NO <======== NO OCCURENCES ON VALART
ITALY 1 YES
JAPAN 1 YES
W GERMANY 0 NO <======== HAS OCCURENCES ON VALART BUT NO ACTIVES

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

I want to thank all those who have taken the time to respond to my query.

However none of the suggestion on this thread have proved successful, including using a Conditional JOIN.

Furthermore, as a last ditch effort, I created a multi-step process where I transformed the Host file into a FOCUS DB (e.g. HLDFOC1), JOIN'ed it to the first Cross-refernce file, to pick up the additional data element, creating a new FOCUS DB (e.g. HLDFOC2). Then the fun began trying the various sugggestions JOIN'ing HLDFOC2 to the second Cross-refernece file (also a FOCUS DB). Nothing has worked.

I have come to the conclusion that MVS FOCUS 7.2.5 does not support joins that involve screens/filters against the cross-reference file, when the desired outcome is to always have all the selected records from the Host file - analogous to a left outer join.

So my solution is to use a FOCUS MATCH FILE procedure to achieve the desired result.

Again, thanks all.

Ed

Ed, gosh i'ld hate to think that was true. That would be so counter to everything focus has been about. Can you open up a case and ask for help from IBI's Renee Teatro.. she totally knows everything about joins and everything about mainframe..

Have you looked at my example using the car file I posted earlier? I tried to emulate what I thought you wanted with the examples shown where all values from the "home1" file was returned even though the "valart" file had key values from the "home1" file with acitves, inactives and missing keys values that are present on the "home1" file. What I coded works for me on MVS Focus 7.11. This was done without using Match file.

Good luck

ET,

Yes, I've looked at your sample code and made some modifications to more closely resemble the situation I'm dealing with. Here is the code:
TSO ALLOC F(CAR) DA('FOCUS.CAR') SHR REU
-RUN
-****** CREATING FOC DB EHVALART
-STEP1A
-******
DEFINE FILE CAR
CANC_CODE/A8=
IF CAR EQ 'TOYOTA' OR 'JAGUAR' OR 'ALFA ROMEO'
THEN 'ACTIVE' ELSE 'INACTIVE';
END
TABLE FILE CAR
SUM CAR NOPRINT
BY COUNTRY
WRITE CANC_CODE DEALER_COST SALES
BY COUNTRY
BY CAR
IF COUNTRY NE FRANCE
ON TABLE HOLD
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-******
-STEP1B
-******
DEFINE FILE HOLD
CNTRY_CAR/A26 = COUNTRY | CAR;
END
TABLE FILE HOLD
BY CNTRY_CAR
PRINT CANC_CODE DEALER_COST SALES
ON TABLE HOLD AS EHVALART FORMAT FOCUS INDEX CNTRY_CAR
END
-RUN
-***** CREATING JUST COUNTRY CARS FOC DB EHHOMES
-STEP2
-*****
DEFINE FILE CAR
CNTRY_CAR/A26 = COUNTRY | CAR;
END
TABLE FILE CAR
BY CNTRY_CAR
SUM COUNTRY CAR
ON TABLE HOLD AS EHHOMES FORMAT FOCUS INDEX CNTRY_CAR
END
-RUN
-***** SIMPLE JOIN
-STEP3
-*****
JOIN CLEAR *
JOIN CNTRY_CAR IN EHHOMES TAG H1
TO CNTRY_CAR IN EHVALART TAG X1 AS J1
END
TABLE FILE EHHOMES
HEADING
"SIMPLE JOIN PRINT
H1.COUNTRY
H1.CAR
X1.CANC_CODE
X1.DEALER_COST
X1.SALES
END
-RUN
-***** SET ALL=PASS WITH IF TEST
-STEP4
-*****
SET ALL=PASS
JOIN CLEAR *
JOIN CNTRY_CAR IN EHHOMES TAG H1
TO CNTRY_CAR IN EHVALART TAG X1 AS J1
END
TABLE FILE EHHOMES
HEADING
"SIMPLE JOIN PRINT
H1.COUNTRY
H1.CAR
X1.CANC_CODE
X1.DEALER_COST
X1.SALES
IF CANC_CODE EQ 'ACTIVE'
END
-RUN
-***** TESTING CONDITIONAL JOIN
-STEP5
-*****
JOIN CLEAR *
JOIN FILE EHHOMES AT CNTRY_CAR TAG H1
TO ONE FILE EHVALART AT CNTRY_CAR TAG X1 AS J1
WHERE CANC_CODE EQ 'ACTIVE';
END
TABLE FILE EHHOMES
HEADING
"CONDITIONAL JOIN RESULTS PRINT
H1.COUNTRY
H1.CAR
X1.CANC_CODE
X1.DEALER_COST
X1.SALES
END
-RUN
Now for the results...
The SIMPLE JOIN produces:
==================================================================
PAGE 1

SIMPLE JOIN

COUNTRY CAR CANC_CODE DEALER_COST SALES
------- --- --------- ----------- -----
ENGLAND JAGUAR ACTIVE 18,621 12000
ENGLAND JENSEN INACTIVE 14,940 0
ENGLAND TRIUMPH INACTIVE 4,292 0
FRANCE PEUGEOT 0 0
ITALY ALFA ROMEO ACTIVE 16,235 30200
ITALY MASERATI INACTIVE 25,000 0
JAPAN DATSUN INACTIVE 2,626 43000
JAPAN TOYOTA ACTIVE 2,886 35030
W GERMANY AUDI INACTIVE 5,063 7800
W GERMANY BMW INACTIVE 49,500 80390
===============================================================
This is okay, i.e. it shows that the JOIN between EHHOMES and EHVALART is working. However I need to modify the JOIN to produce the following result:
==================================================================
COUNTRY CAR CANC_CODE DEALER_COST SALES
------- --- --------- ----------- -----
ENGLAND JAGUAR ACTIVE 18,621 12000
ENGLAND JENSEN INACTIVE 0 0
ENGLAND TRIUMPH INACTIVE 2 0
FRANCE PEUGEOT 0 0
ITALY ALFA ROMEO ACTIVE 16,235 30200
ITALY MASERATI INACTIVE 0 0
JAPAN DATSUN INACTIVE 0 0
JAPAN TOYOTA ACTIVE 2,886 35030
W GERMANY AUDI INACTIVE 0 0
W GERMANY BMW INACTIVE 0 0
===============================================================
Here is what the SET ALL=PASS produces:
===============================================================
PAGE 1

SIMPLE JOIN

COUNTRY CAR CANC_CODE DEALER_COST SALES
------- --- --------- ----------- -----
ENGLAND JAGUAR ACTIVE 18,621 12000
ITALY ALFA ROMEO ACTIVE 16,235 30200
JAPAN TOYOTA ACTIVE 2,886 35030
===============================================================
SET ALL=PASS drops HOST file recors when the cross-reference records are NOT active.

Finally the CONDITIONAL JOIN produces:
===============================================================
PAGE 1

CONDITIONAL JOIN RESULTS

COUNTRY CAR CANC_CODE DEALER_COST SALES
------- --- --------- ----------- -----
ENGLAND JAGUAR ACTIVE 18,621 12000
ENGLAND JENSEN ACTIVE 18,621 12000
ENGLAND TRIUMPH ACTIVE 18,621 12000
FRANCE PEUGEOT ACTIVE 18,621 12000
ITALY ALFA ROMEO ACTIVE 18,621 12000
ITALY MASERATI ACTIVE 18,621 12000
JAPAN DATSUN ACTIVE 18,621 12000
JAPAN TOYOTA ACTIVE 18,621 12000
W GERMANY AUDI ACTIVE 18,621 12000
W GERMANY BMW ACTIVE 18,621 12000
===============================================================
This result is totally bogus, it is distributing the Jaguar numbers across all subsequent records!

My next step is to initiate a "Case" with IBI Tech Support. We'll see what the come up with.

Ed

Trying to follow this, so forgive me if I am wrong.

From your last example, are you trying to get:

COUNTRY CAR CANC_CODE DEALER_COST SALES
ENGLAND JAGUAR ACTIVE 18,621 12000
FRANCE PEUGOT . . .
ITAY ALFA ROMEO ACTIVE 16,235 30200
JAPAN TOYOTA ACTIVE 2,886 35030

from a focus file joined to a flat file?

If so, try:
-STEP1A
-******
DEFINE FILE CAR
CANC_CODE/A8=
IF CAR EQ 'TOYOTA' OR 'JAGUAR' OR 'ALFA ROMEO'
THEN 'ACTIVE' ELSE 'INACTIVE';
END
TABLE FILE CAR
SUM CAR NOPRINT
BY COUNTRY
WRITE CANC_CODE DEALER_COST SALES
BY COUNTRY
BY CAR
IF COUNTRY NE FRANCE
ON TABLE HOLD
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-******
-STEP1B
-******
DEFINE FILE HOLD
CNTRY_CAR/A26 = COUNTRY | CAR;
END
TABLE FILE HOLD
BY CNTRY_CAR
PRINT CANC_CODE DEALER_COST SALES
ON TABLE HOLD AS EHVALART
END
-RUN
DEFINE FILE CAR
CNTRY_CAR/A26 = COUNTRY | CAR;
END
TABLE FILE CAR
BY CNTRY_CAR
SUM COUNTRY CAR
ON TABLE HOLD AS EHHOMES FORMAT FOCUS
END
-RUN
SET ALL=PASS
JOIN CLEAR *
JOIN CNTRY_CAR IN EHHOMES TAG H1
TO ALL CNTRY_CAR IN EHVALART TAG X1 AS J1
END
TABLE FILE EHHOMES
HEADING
"join to all with all=pass"
PRINT
H1.COUNTRY
H1.CAR
X1.CANC_CODE
X1.DEALER_COST
X1.SALES
IF CANC_CODE EQ 'ACTIVE'
END
-RUN

Hi Alan,

Thanks for the response, but no. I want to see on the report all ten cars, but only the numbers (DEALER_COST SALES) for the active cars.

By using SET ALL=PASS in conjunction with a JOIN TO ALL only shows the 3 active cars and the one car with a missing CANC_CODE. The other 6 cars should show but with DEALER_COST and SALES displaying zeroes.

Ed

Ah, okay. How about:

-STEP1A
-******
DEFINE FILE CAR
CANC_CODE/A8=
IF CAR EQ 'TOYOTA' OR 'JAGUAR' OR 'ALFA ROMEO'
THEN 'ACTIVE' ELSE 'INACTIVE';
END
TABLE FILE CAR
SUM CAR NOPRINT
BY COUNTRY
WRITE CANC_CODE DEALER_COST SALES
BY COUNTRY
BY CAR
IF COUNTRY NE FRANCE
ON TABLE HOLD
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-******
-STEP1B
-******
DEFINE FILE HOLD
CNTRY_CAR/A26 = COUNTRY | CAR;
END
TABLE FILE HOLD
BY CNTRY_CAR
PRINT CANC_CODE DEALER_COST SALES
ON TABLE HOLD AS EHVALART
END
-RUN
DEFINE FILE CAR
CNTRY_CAR/A26 = COUNTRY | CAR;
END
TABLE FILE CAR
BY CNTRY_CAR
SUM COUNTRY CAR
ON TABLE HOLD AS EHHOMES FORMAT FOCUS
END
-RUN
SET ALL=PASS
JOIN CLEAR *
JOIN CNTRY_CAR IN EHHOMES TAG H1
TO ALL CNTRY_CAR IN EHVALART TAG X1 AS J1
END
TABLE FILE EHHOMES
HEADING
"join to all with all=pass PLUS a couple of COMPUTEs"
PRINT
H1.COUNTRY
H1.CAR
X1.CANC_CODE
COMPUTE DEALER_COST/D12 = IF X1.CANC_CODE EQ 'INACTIVE' THEN 0 ELSE X1.DEALER_COST;
COMPUTE SALES/D12 = IF X1.CANC_CODE EQ 'INACTIVE' THEN 0 ELSE X1.SALES;
END
-RUN

Hi Alan,

I believe the COMPUTEs would do it. However, that is more of a work-a-round. Based on my understanding of what a Left Outer Join is, and the IBI documentation on SET ALL=PASS, that shouldn't be necessary. Also a MATCH FILE process handles it quite well - but I think it should be doable via a JOIN.

Thanks,

Ed

Hi Ed,

Not a workaround at all.

The computes are setting the dealer cost and sales to 0, which is what you asked for. With the computes out, you get the values for dealer cost and sales, as expected. How else would you expect the values to be 0, except for Peugot which is essentially missing, for the INACTIVE children when they have actual values? Remeber you put these values into the data in your STEP1B.

To recap:
JOIN one to one - gives the parent a maximum of 1 child across the JOIN. In FOCUS it is treated as a unique segment and is therefore part of the parent for reporting purposes. SET ALL has no bearing on this relationship.
JOIN TO ALL one to many - gives the parent a possibility of many children. This child is treated as part of the reporting hierarchy. Using a JOIN TO ALL, even if there is a maximum of one child per parent, allows the SET ALL parameter to kick in.

When the SET ALL parameter is set to OFF and FOCUS is asked to report a parent and child, and the child does not exist, neither the parent or missing child will be displayed. In your case this would stop PEUGOT printing out.
When ALL is set to ON, missing segments are shown if a sceening condition (WHERE or IF) is not used on the segment where the missing child is. This actually works for you if you are not screening on CANC_CODE.
When ALL is set to PASS, missing segments are included in the report regardless of the screening conditions. That is the missing segment is said to PASS any screening conditions applied to it. So screening CANC_CODE to be ACTIVE, will bring out PEUGOT.

Do not confuse any of this with MISSING fields, which is a different subject altogether.

In fact I notice that I used SET ALL=PASS and JOIN TO ALL, in the final bit of code. This is not necessary to give you what you want, I understood you only wanted ACTIVE and PEUGOT, my wrong.

With JOIN one to one, CANC_CODE can be screened as ACTIVE, INACTIVE or blank (for the missing segment). The child segment being part of the parent.
With JOIN TO ALL, the setting of ALL determines what is actually retrieved from the child, under different circumstances.

Which bit is not working as expected?

Ed,

The issue that you are having is how FOCUS treats one to one relationship vs. one to many relationships.

When using a one to one relationship, FOCUS takes any numeric field from the second file and treats it as blank and numeric fields as 0. In a one to many relationship, the fields from the second file will be missing.

To show this, I'm creating as second FOCUS file from the CAR file where the Japan record will be active, and the W Germany record will be inactive. All other records are missing from the file.

TABLE FILE CAR
SUM
COMPUTE ACTIVE/A8 = IF COUNTRY EQ 'JAPAN' THEN 'ACTIVE' ELSE 'INACTIVE';
BY COUNTRY
IF COUNTRY EQ 'W GERMANY' OR 'JAPAN'
ON TABLE HOLD AS CARACTV FORMAT FOCUS INDEX COUNTRY
END

Now for the one to one join. Notice that the active field contains blanks for all other contries other than Japan and W Germany.

JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN CARACTV AS J
TABLE FILE CAR
SUM SALES ACTIVE
BY COUNTRY
END

COUNTRY SALES ACTIVE
------- ----- ------
ENGLAND 12000
FRANCE 0
ITALY 30200
JAPAN 78030 ACTIVE
W GERMANY 88190 INACTIVE

In this instance, having IF ACTIVE EQ 'ACTIVE' will only show the Japan record regardless of the all setting because FOCUS does not see England, France, or Italy as missing.

If I tell FOCUS that I'm using a one to many relationship, then it will recognized the missing instances.

JOIN CLEAR *
JOIN COUNTRY IN CAR TO ALL COUNTRY IN CARACTV AS J1
TABLE FILE CAR
SUM SALES ACTIVE
BY COUNTRY
END

COUNTRY SALES ACTIVE
------- ----- ------
ENGLAND 12000 .
FRANCE 0 .
ITALY 30200 .
JAPAN 78030 ACTIVE
W GERMANY 88190 INACTIVE

Now I can apply the selection criteria with the ALL=PASS

JOIN CLEAR *
JOIN COUNTRY IN CAR TO ALL COUNTRY IN CARACTV AS J1
TABLE FILE CAR
SUM SALES ACTIVE
BY COUNTRY
ON TABLE SET ALL PASS
IF ACTIVE EQ 'ACTIVE'
END

COUNTRY SALES ACTIVE
------- ----- ------
ENGLAND 12000 .
FRANCE 0 .
ITALY 30200 .
JAPAN 78030 ACTIVE
W GERMANY 88190 .

W Germany still shows, because the selection criteria eliminated the record containing INACTIVE only within the secondary file, not my host.

Rob