Re: QM/400 vs Query/400 -- MIDRANGE-L

Dennis,

to make long story short, you are right. Two logical conditions are not
equivalent (first one is never true), and you corrected it correctly. If
you know your queries, and feel comfortable with De Morgan's rules, I'd
suggest using SQL instead of generator.

LIKE keyword and % sign are rather well documented. Basically, %
together with LIKE serves as a wild card. It is not identical to "=". In
your case, I guess, ICLAS is two character field, so it doesn't make any
difference (you were lucky). If ICLAS were longer than 2, than any
combination of 'xxx53xxxx' would be true for ICLAS LIKE '%53%', and only
'53     ' would be true for ICLAS = '53'.
For 2 character field it doesn't matter, but you should be able to
notice performance difference, as query optimizer might choose different
ways to implement queries with LIKE and "=".

Hope this helps.

Vanja Jovic,
Canada


Dennis Munro wrote:
> 
> 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
> +---
+---
| 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
+---