Hello. I'm doing a simple match on two files, with OLD-AND-NEW. I'm doing the match on 2 BY fields, say, LEVEL1 and LEVEL2. The final results is displaying very strangely - when doing:

TABLE ...
PRINT
LEVEL1 AS 'my level1'
...
BY LEVEL1
BY LEVEL2
...
END

The result only displays values for these 2 fields when they're on a sort break (I mean, when the value is changing. All other values show as blanks). I tried format WP and EXCEL and EXL2K, but I get the same results. Any ideas? Thank you.

Just narrowed down the problem to the following:

When I do something like:
MATCH FILE f1
SUM
...
BY field1
BY field2
RUN

FILE f2
PRINT
..
BY field1
AFTER MATCH HOLD AS report1 OLD
END

...

I comment out the line that says "BY field2", I dont get the problem with only sort breaks showing.

Big, your second verb needs to be SUM.
Your first paragraph in a MATCH sets up an environment and your second paragraph SUMs into it.
You can use PRINT as the second verb, as ARt reminds me, but you gotta know what you're gonna get, and it's more likely that using SUM as the second verb is what most situations require.

Susannah, the reason why I do a SUM to a PRINT is because I need to make a MATCH that's 1:MANY. Is there another way?

turn your match around.

Thanks - that did it.

You have something else as a problem.
You can have in a MATCH a SUM followed by a PRINT.
Try this..
MATCH FILE CAR
SUM RETAIL
BY COUNTRY
RUN
FILE CAR
PRINT DEALER
BY COUNTRY BY CAR
END
TABLE FILE HOLD
PRINT *
END

Hmm, I think I'm still having trouble here. Here's the issue: imagine you have 2 tables, one is DEALERS, the other is CARS. You want to print out information about which cars each dealer sells. Note it's a 1:MANY, since a dealer can sell many or no cars at all.

So you want to do SUM:PRINT to replicate 1:MANY

MATCH FILE DEALERS
SUM
address
BY DEALER_ID
WHERE DealerRating GT 2
RUN

FILE CARS
PRINT
carModel
BY DEALER_ID
WHERE carPrice GT 1000
AFTER MATCH HOLD AS result1 OLD
END

This gives me strange results, with values missing at sort breaks. Any suggestions?

TURN YOUR MATCH AROUND!

I actually just noticed that the number of records in the resulting table is correct, and from the looks of it, the match is being performed correctly. However, the problem is that values are **displayed** only on sort breaks. So for 2 dealerships, say, Joe's and Bob's, it will show:

Joe's | Jeep_Cherokee
| Mazda_RX8
| Nissan_Pathfinder
Bob's | BMW_M3
| Nissan_Pathfinder

Notice the omitted values for DEALER field. Is there an option to enable this?
(PS: Reversing the match does not seem to produce the desired results for me)

Oops, the table above didn't come through right. It should display:

Joe's | Jeep_Cherokee
......| Mazda_RX8
......| Nissan_Pathfinder
Bob's | BMW_M3
......| Nissan_Pathfinder
Where "...." is a blank.

I DID turn the match around, using PRINT:SUM with AFTER MATCH set to "NEW" to get the desired results. However, same problem: only sort breaking values are *displayed*... Any help would be appreciated.

Sorry, I failed to mention: my DEALERS table has a sales_agent field, which I must not lose. So I have to do:

MATCH FILE CARS
PRINT
car_name
BY DEALER_ID
RUN

FILE DEALERS
SUM
country
BY DEALER_ID
BY sales_agent
AFTER MATCH HOLD AS rep1 NEW
END

This does not give me the desired results, however. Any help would be appreciated.