FIELD=GST_FLAG ,
A1 ,A1 ,MISSING=ON ,$

There are only 2 values it can be 'A' or '.'

In 5.3.4 the following worked

IF GST_FLAG NE 'A'

Upgraded to 7.1.6 and this no longer works. I receive 0 records I tried changing NODATA = 'X', NODATA = ' ' and tried screening on that...doesn't work...Tried all kinds of things setting missing and nodata...
Finally just held it as HOLD file and Did the same screen on that
IF GST_FLAG NE 'A'
...and it worked. Question is why would I have to do that?

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

The selection criteria should work. Did you try changing it to WHERE GST_FLAG NE 'A'?

Did you try regenerating the Master (if this is a DBMS table)?

This an Oracle Table. I've Tried the Where Statment...problem is..just get 0 records now.
I will try getting the Master regened.

P, i don't have an Oracle table to try it on, but im on 76 in win2k3
i did this
DEFINE FILE CAR
NEWSALES/I8 MISSING ON = IF COUNTRY IS 'ENGLAND' THEN SALES ELSE MISSING ;
END
TABLE FILE CAR
PRINT COUNTRY CAR MODEL
IF NEWSALES IS MISSING
END
worked for me, IS , EQ and NE also worked.
the thing about NODATA is its just a mask, something to prove you have a null value.
So you cant filter on it.
So i set it to '' in my edasprof, so that my excel users don't have that nasty dot to deal with in their exports.

Prarie

SQL and WebFOCUS treat null (missing) values differently in tests.

In SQL null anytest anyval is always false.
In WF null gets treated like a value thus
missing eq missing true
missing ne '2' true

I have to say that wf is the sensible one.

Now what has prob happened is that in the release change optimizer settings of how much of the query gets passed to Oracle has changed.

Originally the IF GST_FLAG NE 'A' would not have been passed to Oracle and WF would do the test hence the record would be accepted.

There are 2 solutions:

(a) Change the expression passed to Oracle to be equivalent to WF test.

WHERE GST_FLAG NE 'A' OR GST_FLAG EQ MISSING;

(b) Disable optimization for this expression

Define

NEW_GST_FLAG/A1 = IF LJUST(1,'A','A1') EQ 'B' THEN MISSING ELSE GST_FLAG ;

Where test becomes

IF NEW_GST_FLAG NE 'A'

The first part of the expression is always false but the inclusion of the LJUST function will force WF to de-optimize the IF test and hence the test is done on WF.

Incidentally WF as usual is more logically correct than SQL.

I think Ted Codd, the inventor of the RDBMS, was a very good salesman, but not as clear a thinker as our own Gerry Cohen.

But as usual its the salesmen who win!



John

The problem here is that MISSING (NULL) values are not equal to anything so equality tests do not work when the WHERE clause is passed to Oracle. I'm pretty sure they don't work for FOCUS/WebFOCUS either, so I'm not sure how this ever worked. Instead of using IF GST_FLAG NE 'A', use IF GST_FLAG IS MISSING. That should always work.

I'm curious. When you did your HOLD file, did the use ON TABLE HOLD MISSING OFF? Are you sure the value are 'A' and null? In other words are you sure the field is not blank? You might try the following just to verify the field contains what you think it does.

TABLE FILE X
COUNT FIELD/I8
BY GST_FLAG
END

Thanks for all the input and ideas.

quote:

IF GST_FLAG IS MISSING

Worked! Wonder why it ever worked before now.