I have two numeric fields in my SQL database, and I want to add them together in a define, like so:

GAINLOSS/D15.2=STGAINLOSS + LTGAINLOSS;

Then I want to select only records where GAINLOSS is NOT 0. So..

TABLE FILE something
PRINT stuff
WHERE GAINLOSS NE 0
END

Problem: This will only return records where there's a value in both STGAINLOSS and LTGAINLOSS. If either field is null (which this field can have null values, MISSING =ON in master file) it won't return the record. Even though the GAINLOSS defined field resolves to something other than zero. If I SUM GAINLOSS, I get the right results. What is going on? I've been all over the documentation, tried everything from SET ALL = PASS to MISSING = OFF to this crazy code:

SOMEDATA/I5 MISSING ON NEEDS SOME=RETURNS + DAMAGED (example from section 13-8 of Creating Reports with WF Lang)

Now, the obvious solution is to not have null values in those fields. In fact, I have a similar database with those exact fields that works fine because values are zero if missing. And I know I could redefine the fields. But I'm just really curious why this happens. And how to prevent it from happening to end users who don't have a clue it's happening.

Give this a try:

SET NODATA = 0
TABLE FILE something
PRINT stuff
COMPUTE GAINLOSS/D15.2=STGAINLOSS + LTGAINLOSS;
WHERE TOTAL GAINLOSS NE 0
END
I don't think you will need the 'SET NODATA = 0'. But give it a try and lets see what happens.

This message has been edited. Last edited by: <Mabel>,

Well this one's getting filed in my Duh folder!
All I was missing was the WHERE TOTAL.

Thank you!

Hi Jen

I know the computational method of the missing value.

Please refer to the following.

DEFINE FILE something
-*** Pattern 1 ***
-*** Both are null
-*** STGAINLOSS + LTGAINLOSS = 0
GAINLOSS/D15.2=STGAINLOSS + LTGAINLOSS;

-*** Pattern 2 ***
-*** Either is null
-*** STGAINLOSS + LTGAINLOSS != 0
GAINLOSS/D15.2 MISSING ON=STGAINLOSS + LTGAINLOSS;

-*** Pattern 3 ***
-*** Neither is null
-*** STGAINLOSS + LTGAINLOSS != 0
GAINLOSS/D15.2 MISSING ON NEEDS ALL=STGAINLOSS + LTGAINLOSS;
END


Try that
Good luck