I am trying to compare two files with dates in them. Something like this:
MATCH FILE FILE1
PRINT
DATE1
BY KEY
RUN
FILE FILE1A
PRINT DATE1A
BY KEY
AFTER MATCH HOLD OLD-OR-NEW
END
DEFINE FILE HOLD
CMP1/A1 = IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';
END
TABLE FILE HOLD
PRINT
DATE1
DATE1A
CMP1
BY KEY
END

Since the date is missing in 1 file I get this error:

(FOC280) COMPARISON BETWEEN COMPUTATIONAL AND ALPHA VALUES IS NOT ALLOWED

If both dates are missing the CMP1 value should be "Y".

What is the best way to compare the dates when one or both may be MISSING?

Gary

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

You're not getting the error because a date is missing, you're getting the error because the formats of FILE1.DATE1 and FILE1.DATE1A are not the same, the code fails in this line:

CMP1/A1 = IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';

In my opinion, it is unusual to MATCH data in the same file, so perhaps describing what you're attempting to do might help us come up with suggestions.

Testing equality with missing data can also be fraught with danger.

May be a good idea to test to see if each field IS MISSING.

Why do you need to use MATCH for this? Both fields are in the same file?
Just do the DEFINE and TABLE on FILE1 and make sure the dates are in the same format, if not then do a DEFINE on one of them to make it the same format as the other one!

Simple rule,

Unless you are trying to do something very, very clever
Which in this case you are not, JOIN/MATCH is not required.

The single file contains ALL data, It's a simple WHERE test.

As has been mentioned, the error would seem to be because of format miss match.

I am matching data from one system to another. In this case and SAP table, and ClickSchedule. There is an interface from ClickSchedule that updates SAP, but it does not always work. I need a report that checks the values in both SAP and ClickSchedule. There are two date fields, and three text fields that are compared. In some cases SAP is missing the record entirely. I get the error when the date is MISSING (Yes it is really MISSING) in the hold file.

The date fields (and text fields) in my "real" are all the same format.

I typed my example from scratch, because the CAR database doens't have a date fields.

Gary

The second File in my example should be FILE2 or something. I typed it wrong.

BTW, are there Focus sample db's that have date fields?

Also, when I extract the data from SAP and ClickSchedule, it is saved as a Focus DB.

jobhist & salhist have ymd dates in them.

I would be double checking my formats in the databases....then double checking the format after the match. There must be something different by definition of that error.

Changing the define to this eliminates the error:

CMP1/A1 MISSING ON = IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';

I had to test the missing values to get the a value when the data is missing.

CMP1/A1 = IF DATE1 IS MISSING AND DATE1A IS MISSING THEN 'Y' ELSE
IF DATE1 IS MISSING THEN 'N' ELSE
IF DATE1A IS MISSING THEN 'N' ELSE
IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';

G

quote:

CMP1/A1 = IF DATE1 IS MISSING AND DATE1A IS MISSING THEN 'Y' ELSE
IF DATE1 IS MISSING THEN 'N' ELSE
IF DATE1A IS MISSING THEN 'N' ELSE
IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N'

CMP1/A1 = IF DATE1 IS MISSING THEN 'N' ELSE
IF DATE1A IS MISSING THEN 'N' ELSE
IF DATE1 EQ DATE1A THEN 'Y' ELSE 'N';

The first test made no sense in my case. It can't happen.