Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Null/Missing values
 Login/Join
 
Gold member
posted
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.
 
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
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>,
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
Gold member
posted Hide Post
Well this one's getting filed in my Duh folder!
All I was missing was the WHERE TOTAL.

Thank you!
 
Posts: 68 | Location: Springfield MA | Registered: May 07, 2003Reply With QuoteReport This Post
<Sugiyama>
posted
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
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.