QM/400 vs Query/400

[Dennis Munro <DMunro@xxxxxxxxxxxxxxxxxxxx>]

I am trying to move to Query Management queries vs. Query/400.  QMQRY works
better because I then can pass variables into them as part of a job stream,
i.e. month end reports with dates.

I am having trouble trying to convert a Query/400 query that uses LIST &
NLIST in the "Select record tests" section.  The LIST parm has 12 values &
the NLIST parm has 17 values.

The QUERY/400 is as follows:

AND    ICLAS                LIST   '51' '52' '53' '54' '55' '56'  
                                        '58' '59' '60' '61' '62' 'GP'
AND    TTYPE             NLIST  '# ' 'C ' 'O ' 'B ' 'IR' 'II'   
                                                   'RD' 'Y ' 'RM' 'FP' 'T '
'A '   
                                                    'PA' 'WS' 'H ' 'RD' 'Y '


When entering a QMQRY via the "Prompt" method, it only lets me enter 6 on a
given "line".  I'm thinking that that is not going to get the same results
as if the 12 parms were all OR'd together.  I end up with the following:

And ICLAS contains '51' or '52' or '53' or '54' or '55' or '56'  
And ICLAS contains '58' or '59' or '60' or '61' or '62' or 'GP'

which generates this code when "converted" to SQL (option 10)

AND ((ICLAS LIKE '%51%')   
     OR (ICLAS LIKE '%52%')
     OR (ICLAS LIKE '%53%')    
     OR (ICLAS LIKE '%54%')    
     OR (ICLAS LIKE '%55%')    
     OR (ICLAS LIKE '%56%'))   
AND ((ICLAS LIKE '%58%')       
     OR (ICLAS LIKE '%59%')    
     OR (ICLAS LIKE '%60%')    
     OR (ICLAS LIKE '%61%')    
     OR (ICLAS LIKE '%62%')    
     OR (ICLAS LIKE '%GP%'))

What I have done is to change the "Generated" SQL statements to look like
this because there should only be one "AND" and all the rest of the values
should be in an" OR" situation - at least that is what I feel it should be. 

 AND ((ICLAS LIKE '%51%')    
     OR (ICLAS LIKE '%52%')
     OR (ICLAS LIKE '%53%')  
     OR (ICLAS LIKE '%54%')  
     OR (ICLAS LIKE '%55%')  
     OR (ICLAS LIKE '%56%')  
     OR (ICLAS LIKE '%58%')  
     OR (ICLAS LIKE '%59%')  
     OR (ICLAS LIKE '%60%')  
     OR (ICLAS LIKE '%61%')  
     OR (ICLAS LIKE '%62%')  
     OR (ICLAS LIKE '%GP%'))





The above situation is the same for the NLIST items but then I end up with
three "AND NOT" lines which each have a maximum of 5 values.  I think the
NLIST gets converted okay because there the "AND NOT" works like it should.

And TTYPE does not contain '# ' or 'C ' or 'O ' or 'B ' or 'IR' or 'II'  
Or TTYPE does not contain 'RD' or 'Y ' or 'RM' or 'FP' or 'T ' or 'A '   
Or TTYPE does not contain 'PA' or 'WS' or 'H ' or 'RD' or 'Y '


I also don't know why it also enclosed the constants/values with the per
cent sign.  I took them off & the query seems to run fine either way.  I've
spent the morning looking through the SQL manuals, the Query Management
manuals, & the CL manual.  I also don't seem to find any reference to the
LIKE verb in of the manuals either.  Where does it come from?  Another
question is the use of LIKE vs. Equal To - why & is there one way preferred
over the other?

I have even considered creating a logical file with the omits & includes &
then just pass in the dates to the QM/400 query to get my report.

Any help or comments would be appreciated.

Dennis

"One never notices what has been done; one can only see what remains to be
done." -- Marie Curie 
"I love deadlines.  I especially like the whooshing sound they make as they
go flying by."  Dilbert's Words Of Wisdom:

Dennis Munro    
Badger Mining Corporation
dmunro@badgerminingcorp.com <mailto:dmunro@badgerminingcorp.com> 
(920) 361-2388

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---