We are having some issues with distinct producing double values when we say the format is ALPHA.

Here is the code for the distinct.

TABLE FILE REPORT
PRINT
DST.REINSROW
BY HIGHEST REINS_YR_ID NOPRINT
ON TABLE HOLD AS FREINS FORMAT ALPHA
END

Documentation claims that it is suppose to work like the SQL distinct command, which makes sense. However, in this example, the resulting file will have two values for each occurrence. For example:

2006
2006
2005
2005
etc.

If you switch the format type to TAB, instead of ALPHA, it works as it is suppose to. However, according to documentation, the TAB format has a 32k file size limit, which could cause problems. In order to use the ALPHA format, which apparently doesn't have size restrictions, the following code must be used.

TABLE FILE REPORT
SUM
REINSROW
BY HIGHEST REINS_YR_ID NOPRINT
ON TABLE HOLD AS FREINS FORMAT ALPHA
END

I have come to the conclusion that we will have to use the second set of code (although the sum command varies drastically from the SQL sum command, which won't work on alphanumeric fields), however, I would like to understand why the first set of code doesn't work like one would think.

Any help would be appreciated

Wayne, you've stumbled across one of the very coolest features of focus, the ability to sum alphabetic fields, which you can't do in an excel pivot table, you can't do in access;
for example,
you can produce a cross table of sales rep's names by client across region
and you would get the name of the current sales rep in each cell of your matrix. (assuming your db was in date order).
There are a zillion uses of this unique feature.
In your example
TABLE FILE REPORT
BY HIGHEST REINS_YR_ID NOPRINT BY REINSROW
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS whatever
END
should do the job nomatter what the output format

Thanks for the reply. But do you know why it behaves this way? Was this intentional and why does it cause a duplicate if we choose ALPHA as the format?

Also, what does the ON TABLE SET HOLDLIST PRINTONLY command do?

Thanks

Wayne

difference between PRINT and SUM, most likely.
PRINTONLY is a nice trick when your fex has no verbs, just BY fields, PRINTONLY prevents the assumed verb object field from showing up in the generated HOLD file. All that is greek, i'll bet , so the best thing to do is just try it, HOLD the output and then look at the generated master file for that output.

thanks.

PRINTONLY is much more powerful than just affecting requests that do not have a verb.

PRINTONLY means any field NOT specifically referenced as an output column
is not propagated to the hold file.

So if PRINTONLY is not set

Then the request

TABLE FILE AFILE
SUM FRED NOPRINT
COMPUTE FREDA/Axxx= GEROGE || BERT || ANYTHING;
END

Will create a hold file that contains FRED, FREDA, GEROGE, BERT and ANYTHING

With HOLDLIST EQ PRINTONLY set your file will contain only FREDA

Thanks... I will pass that along to my developers...

I appreciate the reply and the clarification.

Wayne