I'm having a problem with how MISSING (aka Null) values are being handled out of a conditional statement. My situation in specific deals with masterfiles, but can be illustrated with the example below. Consider the following, where NULLABLENUMBER supports MISSING:

SET NODATA = 'n/a'

DEFINE FILE TEST
test1/P16.3 = IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;
test2/P16.3S MISSING ON= IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;
test3/P16.3S MISSING ON=IF 0 EQ 0 THEN (IF TEST.TEST.NULLABLENUMBER EQ MISSING THEN MISSING ELSE TEST.TEST.NULLABLENUMBER) ELSE 0.0;
END

TABLE FILE TEST
PRINT
Scenario NULLABLENUMBER test1 test2 test3
ON TABLE PCHOLD FORMAT HTML
END


Results:
-----------------------------------------------------
Scenario NULLABLENUMBER test1 test2 test3
-----------------------------------------------------
NumHasVal 16 16 16 16
NumIsNull n/a .000 n/a
-----------------------------------------------------


In the case that the NULLABLENUMBER is null, I cannot understand the results for test2, where the defined field supports nulls, the source field supports nulls, but the null in the source field (which has to be picked, since 0 EQ 0) is converted to a zero (and then suppressed). There is a distinct difference between null and zero, but it doesn't seem like that is getting the gravity it deserves...

test3 illustrates a way to work around this issue, but it really seems like a bug that test2 doesn't work.

Could anyone explain this behavior?

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

Apologies - the "Results" table didn't post as cleanly as it should. hopefully this will work.

Results:
-----------------------------------------------------
Scenario        NULLABLENUMBER  test1   test2   test3
-----------------------------------------------------
NumHasVal          16              16      16      16
NumIsNull            n/a             .000            n/a
-----------------------------------------------------

My assumption is that the field edit option is coming into play, S, which will suppress zero.

Why I don't know.

What happens when you take the S off ?

you might want to try

testx/P15 MISSING ON NEEDS ALL DATA

For what it's worth, in 7.7.01 this repro on CAR file seems to produce the results you were expecting:

 TABLE FILE CAR
SUM RPM BY CAR
    ACROSS COUNTRY
ON TABLE SET HOLDMISS ON
ON TABLE SET ASNAMES FOCUS
ON TABLE HOLD FORMAT FOCUS
END
SET NODATA = 'n/a'
DEFINE FILE HOLD
test1/P16.3             = IF 0 EQ 0 THEN RPMENGLAND ELSE 0.0;
test2/P16.3S MISSING ON = IF 0 EQ 0 THEN RPMENGLAND ELSE 0.0;
test3/P16.3S MISSING ON = IF 0 EQ 0 THEN (IF RPMENGLAND EQ MISSING THEN MISSING ELSE RPMENGLAND) ELSE 0.0;
END
TABLE FILE HOLD
PRINT CAR RPMENGLAND test1 test2 test3
ON TABLE PCHOLD FORMAT HTML
END 

Waz - thanks for the response. I've added test4 to this, to test your idea:

test4/P16.3 MISSING ON= IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;

the results are as follows:
Results:
----------------------------------------------------------------------
Scenario        NULLABLENUMBER  test1   test2   test3   test4
----------------------------------------------------------------------
NumHasVal     16                         16        16       16       16
NumIsNull       n/a                        .000                n/a     .000
----------------------------------------------------------------------

Dave - thanks for your suggestion as well. I've tried test5/P16.3 MISSING ON NEEDS ALL DATA= IF 0 EQ 0 THEN TEST.TEST.NULLABLENUMBER ELSE 0.0;

Results:
----------------------------------------------------------------------
Scenario        NULLABLENUMBER  test1   test2   test3   test4   test5
----------------------------------------------------------------------
NumHasVal     16                         16        16       16       16        16
NumIsNull       n/a                        .000                n/a     .000      n/a
----------------------------------------------------------------------



This seems to work in the define. I'll see if I can get it to work in my master file.

Fixed. Checking the ALL box in the master file definition resolved this. Thanks to everyone on the forum that contributed their ideas - it really helped lead to a solution