How do I test for a NULL in Filter?

[robert fuschetto]

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!

[David Beagan]

The WebFOCUS keyword MISSING is the counterpart to SQL's NULL.

You could try:

WHERE MF1.ID IS MISSING

[robert fuschetto]

Hmmm..no records return where as SQl and MsAccess return records. So there are entries inthe HOLD file NOT in the master file.

[robert fuschetto]

here is the JOIN:

JOIN

LEFT_OUTER HOLDFINAL.HOLDFINA.BILLPROVNBR IN HOLDFINAL TO MULTIPLE

TBLMSTR_NPISPECIALTYBENCHMARK.TBLMSTR_NPISPECIALTYBENCHMARK.NPINBR

IN tblmstr_npispecialtybenchmark TAG J1 AS J1

END

[robert fuschetto]

there should be BILLPROVNBRs in the hold file not in tblMstrNpiSpecialtybenchmark.

WHERE J1.TBLMSTR_NPISPECIALTYBENCHMARK.NPINBR EQ MISSING;

[David Beagan]

If you don't have this WHERE statement, then you see the NPINBR column on some rows that appear as just a . which indicates a NULL value? Is that right?

[robert fuschetto]

if I remove the WHERE and bring in the Value from each table I see the ones I want...ie blanks:

[David Beagan]

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.

[robert fuschetto]

sorry, yes...NULLS....it is not finding them though: WHERE J1.TBLMSTR_NPISPECIALTYBENCHMARK.NPINBR EQ MISSING;

[David Beagan]

Did you try the SET HOLDMISS=ON that I suggested at the end of my previous post?

[Patrick Huebgen]

As an alternative you could use MATCH - I presonally prefer MATCH to compare HOLD files

https://docs.tibco.com/appstudio/9000/doc/html/index.jsp?topic=%2Freporting%2FCreatingReportswithWFLanguage%2Fsource%2Fmrg159.htm