Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

How do I test for a NULL in Filter?


robert fuschetto

Recommended Posts

I have a simple file of ID (text) and FY (integer)....call it: HOLD1.

I have a master file of ID(text), FY(int) and other columns....call it: MF1

I do a LEFT OUTER JOIN using the GUI by ID and FY..

I want all records in HOLD1 NOT in MF1.

How do create my filter?

In any other tool I'd simply say: WHERE MF1.ID IS NULL.

Thanks!

Link to comment
Share on other sites

The ones you want are not blanks, they are NULLS or MISSING as inidcated by the dots or periods.

I was able to reproduce your issue with the ggsale file:

TABLE FILE GGSALESPRINT SEQ_NO UNITSWHERE SEQ_NO GT 10ON TABLE HOLD AS GG11END JOIN LEFT_OUTER SEQ_NO IN GGSALES TAG J0 TO UNIQUE SEQ_NO IN GG11 TAG J1 AS J1END TABLE FILE GGSALES PRINT SEQ_NOJ1.SEQ_NO WHERE J1.SEQ_NO IS MISSINGEND

Then I found that if I put the following in the beginning of the code I got only the desired rows on the output:

SET HOLDMISS=ON

Your situation is a little different, but maybe this would work for you too.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...