I became alarmed when a report bug was discovered. It relates to nulls and the prefix operator FST: In A SUM, if the column in the first row contains nulls, the column in the second row is selected!

Here's an example. Ideally, in the second TABLE, I'd like the value of FST.WEIGHT2 to be null.

-* File fm_test_nulls1.fex

TABLE FILE CAR
SUM
WEIGHT
COMPUTE WEIGHT2/D5 MISSING ON = IF COUNTRY EQ 'ENGLAND' THEN MISSING ELSE WEIGHT;
BY COUNTRY

ON TABLE HOLD AS H001 MISSING ON
END
-RUN

TABLE FILE H001
PRINT
WEIGHT
WEIGHT2
FST.WEIGHT2
LST.WEIGHT2
END
-RUN

TABLE FILE H001
SUM
FST.WEIGHT2
LST.WEIGHT2
END
-RUN

This message has been edited. Last edited by: Francis Mariani,

Francis believe it or not this is an issue that goes back to version 4.3

see HT case 90901069

Have not had a chance to check if there is a solution at the moment but I would raise a P1 case with IBI

Yikes JG! By the way, I can't find the case you specified.

The manual does state "Generates the first physical instance of the field" - I'm not sure this is vague enough...

Apparently this is the "correct and default" behaviour. I'm not sure why it's the correct behaviour.

I would like the value encountered in the first row - whether the value is null or not.

Hi Francis,

Here are the notes from case 90901069. The Symptom-Problem-Solution document has been archived due to its age (early 2004). Please see if this gets you closer to what you're looking for.

 
Problem Section 

TABLE a HOLD file using FST. prefix operator on a FIELD with a MISSING value
will not select the MISSING value as the FIRST value. The next value, if it
exists (not MISSING), will be selected as the FIRST value.

Solution Section 

This is expected behavior. An easy way to avoid this behavior is to create the
HOLD file in question using the MISSING OFF option. By default HOLD format
files will respect and retain MISSING data. The HOLD statement using MISSING
ON:    ON TABLE HOLD MISSING OFF AS filename

Another option is to create a DEFINE field prior to creating the HOLD file.
The DEFINE field can use the MISSING OFF option and replace the MISSING value
with a different value such as blank or zero, depending on the format of the
field.
 

Regards,

Kathryn

Kathryn,

Thanks for the notes on this case.

I decided to use the DEFINE workaround:

PREV_DAY_AMT/D20.2 MISSING OFF = PREV_DAY_AMT;

Luckily, this DEFINE works - I can use the same column name.

Cheers,

Hi Francis,

I'm glad to hear that this worked for you.

Regards,

Kathryn

FYI,

I've seen similar things in SQL Server, so perhaps the FOCUS logic has been brought into line with SQL standards ?