|
midrange.com
E
Hello Dennis,
Read the prompt screen properly. When you specify the first test it looks like:
Type new comparison test, press Enter.
Field or expression . . . . . ______________________________________________
__________________
IS/ISNOT . . . . . . . . . . . IS___ IS, ISNOT
Test . . . . . . . . . . . . . __ EQ, LT, LE, GT, GE...
Value . . . . . . . . . . . . ______________________________________________
__________________
but after you have specified the first test it looks like:
Type new comparison test, press Enter.
AND/OR . . . . . . . . . . . . AND AND, OR
Field or expression . . . . . _____________________________________________
__________________
IS/ISNOT . . . . . . . . . . . IS___ IS, ISNOT
Test . . . . . . . . . . . . . __ EQ, LT, LE, GT, GE...
Value . . . . . . . . . . . . _____________________________________________
__________________
Notice the first field AND/OR which was not there previously? Change the
connector to
OR and you will achieve your desire. For example:
And ICLAS contains '51' or '52' or '53' or '54' or '55' or '56'
Or ICLAS contains '58' or '59' or '60' or '61' or '62' or 'GP'
will cause QM to generate the following SQL:
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%'))
Even though this is a little weird from an SQL point of view it will select the
desired records -- which is not the case with the ANDed version you originally
generated.
Also your negative test for TTYPE is not correct. It suffers from the same
logic flaw
as the above. NOT clauses should rarely be ORed together. Generally you want
to AND
them. You have them as:
> 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 '
when I think you should have them as:
And TTYPE does not contain '# ' or 'C ' or 'O ' or 'B ' or 'IR' or 'II'
And TTYPE does not contain 'RD' or 'Y ' or 'RM' or 'FP' or 'T ' or 'A '
And TTYPE does not contain 'PA' or 'WS' or 'H ' or 'RD' or 'Y '
because you want the selection to occur only when TTYPE is not one of those
values.
The selection may work in this particular case due to the actual data values in
the
file but I suspect it isn't working as you expect. It is a little more complex
than
that because you are using CONTAINS so ORing the clauses may actually omit the
records
you want omitted due to generic pattern matching although not for the reasons
you
think.
As an example in logic, if a given field can only contain three values ('A',
'B', and
'C') and you provide a clause like SELECT * FROM FILE WHERE FIELD <> 'A' OR
FIELD <>
'B' thinking you are excluding records with a value of 'A" or 'B' then you will
retrieve all records -- which is not what you wanted. Think it through -- for
any
gEven record, that field can contain only one of the three values. If the
field is
'C' the record is selected because it is not 'A' and it's not 'B', if the field
is 'B'
it is selected because it is not 'A', 'A' is selected because it is not 'B'.
The OR
predicate requires only one of the clauses to be true. To exclude both 'A' and
'B'
you must AND the clauses.
Prompted QM does use OR to tie the clauses together but then negates the entire
clause
with a NOT operator. For example:
AND NOT( (TTYPE LIKE '%PA%')
OR (TTYPE LIKE '%WS%')
OR (TTYPE LIKE '%H%')
OR (TTYPE LIKE '%RD%')
OR (TTYPE LIKE '%Y%') )
is the same as the more usual:
AND ( TTYPE NOT LIKE '%PA%'
AND TTYPE NOT LIKE '%WS%'
AND TTYPE NOT LIKE '%H%'
AND TTYPE NOT LIKE '%RD%'
AND TTYPE NOT LIKE '%Y%' )
LIKE is described in the SQL reference (look in the Language Elements chapter
under
the predicates section) and is used because SQL doesn't support a CONTAINS
predicate.
CONTAINS and LIKE are effectively synonyms. LIKE is a pattern matching
function. The
% signs are used to match any number of characters, an underscore is used to
match any
single character. TTYPE LIKE 'RD' is the same as TTYPE = 'RD' which is why the
%
signs are used because you are comparing for the characters 'RD' occurring
anywhere in
the TTYPE field -- that's what CONTAINS means. An equals test is comparing for
the
occurence of ONLY the characters 'RD'. Your query may run fine without the %
signs
because the values you specify may be the only characters in the field. You
should
use = when you are searching for an exact match and LIKE when you are searching
for a
pattern.
Regards,
Simon Coulter.
«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
«» FlyByNight Software AS/400 Technical Specialists «»
«» Eclipse the competition - run your business on an IBM AS/400. «»
«» «»
«» Phone: +61 3 9419 0175 Mobile: +61 0411 091 400 «»
«» Fax: +61 3 9419 0175 mailto: shc@flybynight.com.au «»
«» «»
«» Windoze should not be open at Warp speed. «»
«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»«»
//--- forwarded letter -------------------------------------------------------
> X-Mailer: Internet Mail Service (5.5.2650.21)
> Date: Fri, 12 May 2000 16:12:28 -0500
> From: "Dennis Munro" <DMunro@badgerminingcorp.com>
> To: "'Midrange Users'" <Midrange-L@midrange.com>
> Reply-To: MIDRANGE-L@midrange.com
> Subject: QM/400 vs Query/400
>
> 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%')
> OE (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
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
