Be informed that query/400 has limitations not found in high level
languages.
When matching records in different files, be careful with definitions
associated with dead records, not just records coded for deletion, but also
records coded as completed orders, engineering whose effectivity dates have
expired.
Suppose you are matching records on two files & for some situations, there
are no records in one of the files (null value). You will not see any data,
even though it exists in the other files. Example, a report on production,
which we match with employee clock # file to show name of person who did it.
Where employee is no longer in the company, and someone has both soft
deleted the clock #, and run the job to remove coded for deletion, then all
production data with that clock # will be gone from your report.
Suppose you are matching records between 1 file that has only one record for
the item, and another file that has multiple records, and you are getting
some values out of both files. The multiple records file will cause
multiple lines, one for each record. The values from the file with a single
record will show up on each of those lines. If you doing a total on the
columns, the total of the single record file will be inflated by the number
of extra matches with the multiple record file.
Handling negative values for some people is counter intuitive, because how a
minus value is shown on 400-I is different from how it is shown in
Microsoft. Plus it is very easy in query to select based on values greater
than zero, when perhaps not equal to zero would be more valid.
Sometimes there is data in our computer systems that is invalid incorrect,
that we do not want averaging into our reports. Due to how different
software handles such situations, we can look at 2 different reports of
allegedly same data, get different results, misinterpret what is wrong.
-
Al Mac
-----Original Message-----
From: bpcs-l-bounces+macwheel99=wowway.com@xxxxxxxxxxxx
[mailto:bpcs-l-bounces+macwheel99=wowway.com@xxxxxxxxxxxx] On Behalf Of
David.ANTONISSE@xxxxxxxxxxxxxxxx
Sent: Friday, May 28, 2010 7:43 AM
To: bpcs-l@xxxxxxxxxxxx
Subject: [BPCS-L] Query - I get totals on a place which should be
empty,anyone can help?
Dear assistents,
see downstanding Query AS400 problem:
my report is almost ready, but only the 'strange' totals I can't get out of
the report.
The total quantity from 'AVAILQTY01' is placed in 'AVAILQTY03' when there
is no child item BCHLD.
The only thing I want is an '0' or ' ' in column AVAILQTY03 when the child
item is empty.
When I give fe in the last query 'AVAILQTY03 LE 500000' than it deletes in
the report all earlier information, here the 3rd and 4th line, but I want
to keep that information.
Explanation fields:
AVAILQTY1: Qty on hand from the can FLB5
AVAILQTY2: Qty in extern warehouse, consignment stock, from the can FLB5
AVAILQTY3: Qty on hand from the plate (childitem), information comes from
the outputfile were AVAILQTY1 is found as well.
Explanation used queries:
DA10 shows a list with salesquantities (cust nr/cust name/x-ref/imp
item/desc1/desc2)
DA11 same as DA10, but gives the totals per customer number and item
DA12 shows the info of DA10 with the totals of DA11
DA2 gives quantities on hand, on cust order and the diff
DA3 gives quantities in consigment stock
DA4 gives childitems from the parents (MBM)
DA5 shows a total overview, where I get the strange totals from
AVAILQTY01 when there is no childitem.
Do you have an advise or a tip?
Thank you very much!
The information is little changed for the privacy of our company:
I get a big total on AVAILQTY03 where I want to show the quantity on hand
from the child item of the can. Where there is no plate item as child I
want a qty = 0 or qty = ' '.
Thank you very much for your help and assitence!
Rgds,
David AntonissePlease consider the environment before printing this e-mail.
----------------------------------------------------------------------------
----------------------------------------- Discover Impress on
http://www.impressgroup.com The information contained in this e-mail and any
attachments is strictly confidential and may be legally privileged.It is
intended solely for the use of the individual or entity to whom it is
addressed. If you are not the named addressee, you are hereby notified that
it is prohibited and may be unlawful to disclose, distribute, store, copy
the information in any medium or usage for another purpose.
----------------------------------------------------------------------------
-----------------------------------------
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.819 / Virus Database: 271.1.1/2901 - Release Date: 05/28/10
01:25:00
midrange.com
