The following QMF query yields 2,108 records:
WHERE FIELD2 NOT IN (SELECT FIELD2
WHERE FIELD1 <>'')
Yet, the following QMF query yields 17,534 records:
WHERE FIELD2 IN (SELECT FIELD2
WHERE FIELD1 ='')
First question is: why don't I get the same record count for both queries?
Also, I first tried testing for NULL values (instead of using WHERE FIELD1=''), but this yielded no records. The reason for this is because a process pads the field with spaces instead of leaving it NULL when this table is populated. So, my second question is how else do I test for records that have a field full of spaces?
I'm using QMF Version 7, Release 1 for DB2.