QMF subquery problem

Hi,

The following QMF query yields 2,108 records:

SELECT FIELD1                              
FROM   TABLE1
WHERE  FIELD2 NOT IN (SELECT FIELD2            
                                    FROM   TABLE1
                                    WHERE  FIELD1 <>'')  

Yet, the following QMF query yields 17,534 records:

SELECT FIELD1                              
FROM   TABLE1
WHERE  FIELD2 IN (SELECT FIELD2            
                             FROM   TABLE1
                             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.

Thanks,

Drew
LVL 7
DrewKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sagacitysolutionsCommented:
WHERE  TRIM(FIELD1) ='')  
DrewKAuthor Commented:
Hi sagacitysolutions,

Tried that.

TRIM is not a function available in my version of QMF (or any version of QMF that I'm aware of).

Thanks,

Drew
sagacitysolutionsCommented:
Sorry Drew, have you tried this

Where FIELD1 IS NULL
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

DrewKAuthor Commented:
Yes tried that too, but the problem with that (as I described in my second question) is that I have padded spaces...no records have NULLs

Thanks,

Drew
sagacitysolutionsCommented:
According to the documentation a field with all spaces would be equal to NULL
DrewKAuthor Commented:
I don't know about the documentation, but when I write a query using WHERE  FIELD1 IS NULL, I get ZERO records.

However, when I write a query using WHERE  FIELD1 ='', I get records with that field having all spaces.

Drew
DrewKAuthor Commented:
I'm increasing the point value for this one

Keep the ideas coming!

Thanks,

Drew
sagacitysolutionsCommented:
Lets try this

run the following to query's to determine the number of records returned

What is the record count for the following 2 queries

SELECT FIELD2 FROM TABLE1  WHERE  FIELD1 <> ''
SELECT FIELD2 FROM TABLE1  WHERE  FIELD1 = ''

DrewKAuthor Commented:
12,750 records:  SELECT FIELD2 FROM TABLE1  WHERE  FIELD1 =''
4,903 records:    SELECT FIELD2 FROM TABLE1  WHERE  FIELD1 <> ''

Note:  My original record count (above) is going to change by a small amount each time I run it, because I'm working with live data.  

Also, I might add that I'm trying to figure out what the LOGICAL reason is for my Question #1 (above). I'm starting to think that the solution is obvious...and I'm too brain-dead to realize it!  So, I won't be surprised if the answer is an easy one!  

Thanks,

Drew
DrewKAuthor Commented:
The following provides updated record count info and shows the two queries with the different "pots" of record-counts.

----------------------------------------------------
The following QMF query yields 2,112 records:

SELECT FIELD1                              
FROM   TABLE1 (17,653 records)
WHERE  FIELD2 NOT IN (4,903 records)  

Yet, the following QMF query yields 17,538 records:

SELECT FIELD1                              
FROM   TABLE1 (17,653 records)
WHERE  FIELD2 IN (12,750 records)  

Hope that helps,

Drew
sagacitysolutionsCommented:
Drew,

I understand that I run in to that all the time, the brain can only work so long
before the eyes give out....

Try This query
I am working on the first question, kinda stumped myself.

SELECT FIELD1 FROM TABLE1 a INNER JOIN TABLE1 b ON a.FIELD2 = b.FIELD2
WHERE b.FIELD1 <> ''


DrewKAuthor Commented:
INNER JOIN is not an acceptable syntax in QMF.

However, I do believe that my query is trying to do a join of some kind.

Let me explain...

I inherited this query, and now I'm trying to figure out how it works.

The full query is actually (I changed field names and values for security reasons):

SELECT DISTINCT                                                        
        CASE                                                            
          WHEN SUBSTR(FIELD1,6,1) = '' THEN FIELD1                  
          ELSE SUBSTR(FIELD1,2,5)                                      
        END                                         FIELD1_Title,          
        FIELD2                                                    
 FROM   TABLE1                                              
 WHERE  FIELD2 <> ''                                              
 AND    (FIELD3 IN ('XXXX','AAAA','BBBB','CCCC','DDDD')                
         AND FIELD4 LIKE ('%%%1'))                                      
 OR     FIELD3 IN ('EEEE','EEEE')  
                                     
 UNION          
                                                         
 SELECT DISTINCT                                                        
        CASE                                                            
          WHEN SUBSTR(FIELD1,6,1) = '' THEN FIELD1                  
          ELSE SUBSTR(FIELD1,2,5)                                      
        END                                         FIELD1_Title,          
        FIELD2                                                                        
 FROM   TABLE1                            
 WHERE  FIELD1 NOT IN (SELECT FIELD1                
                         FROM   TABLE1    
                         WHERE  FIELD2 <> '')    
 AND    (FIELD3 IN ('XXXX','AAAA','BBBB','CCCC','DDDD')                
        AND FIELD4 LIKE ('%%%1'))                    
 OR     FIELD3 IN ('EEEE','EEEE')  

Hope that helps you help me!

Drew
ghp7000Commented:
hmm have you tried:
SELECT FIELD1                              
FROM   TABLE1
WHERE  FIELD2 NOT IN (SELECT FIELD2            
                                    FROM   TABLE1
                                    WHERE  FIELD1 <>''")
and
SELECT FIELD1                              
FROM   TABLE1
WHERE  FIELD2 IN (SELECT FIELD2            
                             FROM   TABLE1
                             WHERE  FIELD1 =''")    
DrewKAuthor Commented:
Hi ghp7000,

That SQL will produce an error (I tried it anyway and confirmed this).

I must use single quotes when referring to data in QMF (Query Managment Facility), an IBM product.

Double quotes are used for naming columns and such.

Thanks,

Drew
david251Commented:
drew,

the function that acts like trim for use in your version of QMF is:

STRIP(FIELD, OPTION, CHARACTER)    
    --TRIMS CAHRACTERS FROM TEXT  
      OPTION                      
        'B' BOTH                  
        'L' LEADING                
        'T' TRAILING              
      CHARACTER TO STRIP ' '      
    EX. STRIP(CS_MRN,'T',' ')      

so try comparing

SELECT FIELD1                              
FROM   TABLE1
WHERE  FIELD2 NOT IN (SELECT FIELD2            
                                    FROM   TABLE1
                                    WHERE  STRIP(FIELD1,'B',' ') <>'')  

SELECT FIELD1                              
FROM   TABLE1
WHERE  FIELD2 IN (SELECT FIELD2            
                             FROM   TABLE1
                             WHERE  STRIP(FIELD1,'B',' ') ='')  

I hope this helps.
-David251
DrewKAuthor Commented:
Hi David251,

The first one yielded: 17,538 records
The second one yielded: 2,112 records

BTW, the second argument of the STRIP function does not use quotes around the argument.

So, STRIP(FIELD1,'B',' ') should actually be STRIP(FIELD1,B,' ')!

I didn't even know about the STRIP function so don't feel bad :)

But this might be the beginning of answering my *second* question.

I'm still trying to understand why I get two different results though....

Drew
david251Commented:
drew,

do you have anywhere you could post the sample table. I could then load it and see if the same occurs on my end.

-David251
DrewKAuthor Commented:
David251,

Unfortunately, I'm dealing with sensitive info and a LOT of it.

I also don't have anywhere to post the table.

Drew
david251Commented:
Drew,

I think i have got it.

Suppose your data looked like this, note field1 is blank padded

FIELD1    FIELD2
--------  ------
          a      
          a      
          a      
          a      
1         b      
1         b      
1         b      
          b      

The results of this query:
SELECT *                                              
FROM   TABLE1                                          
WHERE  FIELD2 NOT IN (SELECT FIELD2                    
                      FROM   TABLE1                    
                      WHERE  STRIP(FIELD1,B,' ') <>'')
is
FIELD1    FIELD2  
--------  ------  
          a      
          a      
          a      
          a      

This is b/c the inner select yeilds the value 'b' since it has data.  Then not 'b' is only 'a'.

and the results of this query:

SELECT *                                                    
FROM   TABLE1                                              
WHERE  FIELD2 IN (SELECT FIELD2                            
                             FROM   TABLE1                  
                             WHERE  STRIP(FIELD1,B,' ') ='')
is

FIELD1    FIELD2
--------  ------
          a    
          a    
          a    
          a    
1         b    
1         b    
1         b    
          b    

This is b/c the results of the inner select are 'a' and 'b' since both have an empty string.

I hope this helps.
-David251

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrewKAuthor Commented:
David251,

I think you hit the nail on the head.

That's the type of answer I was looking for, and it makes sense so I'll buy it!

Thanks again,

Drew
david251Commented:

Glad I could help:-)
Thanks for the points/grade.
-David251
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.