Guest
Guest
How can I query two files and determine which records are present in one but not the other? I have tried using an SQL statement and also WRKQRY on AS/400 v4r5.
mcogdell
Active Member
Do a work query and join the two files with the 'unmatched records with primary file'.
dale_draper
Well Known Member
Use join type #3 3=Unmatched records with primary file.
Obviously, the records you use to join the file, and the order in which you
increment the two files in the query are important to the outcome.
And you might want to try the "NE" test as well. (I't been a while since
doing this)
Dale
a7.3c9
v4r4
Obviously, the records you use to join the file, and the order in which you
increment the two files in the query are important to the outcome.
And you might want to try the "NE" test as well. (I't been a while since
doing this)
Dale
a7.3c9
v4r4
Ingramw
Member
With AS/400 WRKQRY , on the 'Specify Join Type' display, try
2=Matched records with primary file
3=Unmatched records with primary file
You should list the match field(s) from both files
2=Matched records with primary file
3=Unmatched records with primary file
You should list the match field(s) from both files
PhillipW
Member
WRKQRY can be used. Specify a primary and secondary file in file selection. For Join type specify 3.
IBM help text for this option:
3=Unmatched records with primary file
Selects, from the primary file, only records that have
no match in at least one of the secondary files. That
is, every primary record is selected that does not
have a matching record in all the secondary files.
Hope this helps.
Phillip
IBM help text for this option:
3=Unmatched records with primary file
Selects, from the primary file, only records that have
no match in at least one of the secondary files. That
is, every primary record is selected that does not
have a matching record in all the secondary files.
Hope this helps.
Phillip
KENNEY
Well Known Member
You can use the EXISTS clause
i.e
SELECT * from F0411 where RPFY=1 and not exist (SELECT * FROM F0101
where ABAN8=RPAN8)
This would retrieve all occurrences from F0411 where there was no
corresponding address book record.
Michael Kenney
IT Services
University of Windsor
i.e
SELECT * from F0411 where RPFY=1 and not exist (SELECT * FROM F0101
where ABAN8=RPAN8)
This would retrieve all occurrences from F0411 where there was no
corresponding address book record.
Michael Kenney
IT Services
University of Windsor
Guest
Guest
Thanks, but I got that far. My problem is how to set up the relations test.
Guest
Guest
Thanks, but I am having trouble with the relations test, not the actual set up for the files. Can you help with that?
Guest
Guest
I appreciate the input, but am still having trouble with the relations test. Should I enter primary field NE secondary? When I do I get back "no data found." Every time I try I get no records, and I KNOW there are records in one file and not the other.
Guest
Guest
Should I list them as NE? There are fields that match, but if there is no record present for that specific item #, then of course there is no record to match. In this case, the item# can be present in the secondary file, but it may not be an exact match ( different city) How do I narrow it down?
Guest
Guest
Re: RE: Query for unmatched records
Thank you Dale. I think my relations test is what throws it off. I have since created an SQL statement that I thought would work, but even htat missed a couple of records. I am using this to validate data in a new report that I created.
Thank you Dale. I think my relations test is what throws it off. I have since created an SQL statement that I thought would work, but even htat missed a couple of records. I am using this to validate data in a new report that I created.
Guest
Guest
Re: RE: Query for unmatched records
Sounds easy, but it's not. The files I am using are not standard JDE. There are existing logicals that contain the same keys; perhaps I should query those instead? Is it important?
Sounds easy, but it's not. The files I am using are not standard JDE. There are existing logicals that contain the same keys; perhaps I should query those instead? Is it important?
dale_draper
Well Known Member
This match type (#3) is for finding data that exists in file B and not in
file A.
File A File B
1 1
2
3
4 4
5
6 6
7
Result: 3,7
As for your other question, EQ or NE, test and see the results.
file A.
File A File B
1 1
2
3
4 4
5
6 6
7
Result: 3,7
As for your other question, EQ or NE, test and see the results.
John_Dickey
Reputable Poster
RE: RE: Query for unmatched records
Yes, you do have to be careful. You have the Join Comparisions Test (within
the Specify file selections) and then you Select Records section of the
query as well. I think for what you want to do is in the Join Comparisions
test within Specify File Selections, you want to do EQ conditions on the
field(s) that are key. Say, for example, Part Number file 1 field = Part
Number file 2 field. You would leave the Select Records section of the
query alone. Using the join method 03 you would get all records from file 1
with part number that has no record in file 2 containing that part number.
If you want to reduce that file 1 record list, then you would use Select
Records section of the query to select based on values in File 1 fields
(don't look at file 2 fields, because they will be all blank or zero values
in join method 3).
You do NOT need to use a file with the specific key list for what you want
to match. IBM's query will take care of using any existing key or building
its own key. I never worry in query about specifying a file with the exact
key - I usually just use the physical file.
Hope this helps some.
John Dickey
JDE Financial Systems
Administrator/Programmer/Analyst
White-Rodgers, Division of Emerson
St. Louis, MO
314-577-1466
Yes, you do have to be careful. You have the Join Comparisions Test (within
the Specify file selections) and then you Select Records section of the
query as well. I think for what you want to do is in the Join Comparisions
test within Specify File Selections, you want to do EQ conditions on the
field(s) that are key. Say, for example, Part Number file 1 field = Part
Number file 2 field. You would leave the Select Records section of the
query alone. Using the join method 03 you would get all records from file 1
with part number that has no record in file 2 containing that part number.
If you want to reduce that file 1 record list, then you would use Select
Records section of the query to select based on values in File 1 fields
(don't look at file 2 fields, because they will be all blank or zero values
in join method 3).
You do NOT need to use a file with the specific key list for what you want
to match. IBM's query will take care of using any existing key or building
its own key. I never worry in query about specifying a file with the exact
key - I usually just use the physical file.
Hope this helps some.
John Dickey
JDE Financial Systems
Administrator/Programmer/Analyst
White-Rodgers, Division of Emerson
St. Louis, MO
314-577-1466
PhillipW
Member
Use an equal match. This should work even with non-jde files. All records in the primary file that do not have a corresponding match in the second file will be selected.
PhillipW
Member
Use an equal match. This should work even with non-jde files. All records in the primary file that do not have a corresponding match in the second file will be selected.
Ingramw
Member
AS/400 WRKQRY: For your Join test use 'EQ' for your field match. Your
match fields must have the same attributes,
size and type. (8 Dec EQ 8 Dec)
(F4102) (F4101)
Field Test Field
T01.IBITM EQ T02.IMITM
SherylS
<sshepard@dwhomes To: [email protected]
.com> cc:
Sent by: Subject: Re: Query for unmatched records
owner-jdeworld@jd
elist.com
11/21/2002 12:35
PM
Please respond to
jdeworld
I appreciate the input, but am still having trouble with the relations
test. Should I enter primary field NE secondary? When I do I get back "no
data found." Every time I try I get no records, and I KNOW there are
records in one file and not the other.
--------------------------
To view this thread, go to:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=W&Number=45524
+ - - - - - - - - - - - - - - - - - - - - - - - -+
This is the JDEList World? mailing list/forum.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found on the JDEList Forum at
http://www.JDEList.com
JDEList is not affiliated with JDEdwards®
+ - - - - - - - - - - - - - - - - - - - - - - - -+
match fields must have the same attributes,
size and type. (8 Dec EQ 8 Dec)
(F4102) (F4101)
Field Test Field
T01.IBITM EQ T02.IMITM
SherylS
<sshepard@dwhomes To: [email protected]
.com> cc:
Sent by: Subject: Re: Query for unmatched records
owner-jdeworld@jd
elist.com
11/21/2002 12:35
PM
Please respond to
jdeworld
I appreciate the input, but am still having trouble with the relations
test. Should I enter primary field NE secondary? When I do I get back "no
data found." Every time I try I get no records, and I KNOW there are
records in one file and not the other.
--------------------------
To view this thread, go to:
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=W&Number=45524
+ - - - - - - - - - - - - - - - - - - - - - - - -+
This is the JDEList World? mailing list/forum.
Archives and information on how to SUBSCRIBE, and
UNSUBSCRIBE can be found on the JDEList Forum at
http://www.JDEList.com
JDEList is not affiliated with JDEdwards®
+ - - - - - - - - - - - - - - - - - - - - - - - -+
Guest
Guest
Thanks Willie. I will try. And to all the others who have sent me messages to help... I APPRECIATE it!
JWalker81
Active Member
The following SQL will select the records from F56100 that are not in
F4801.
select * from f56100 exception join f4801
on $idoco=wadoco
where $idcto='WO'
The 'exception join' tells it that it is looking for those records in
F56100 that do not have a match in F4801. The 'on' portion tells how to
define the relational test between the two files (in this case, that the
document number in F56100 should be equal to the document number in
F4801). The 'where' condition is telling what specific records from
F56100 that I want to match (in this case, F56100 has many different doc
types in it, but only work order doc types are in F4801; if I was to
remove the WHERE condition that I have specified, I would get a list of
a lot of orders that are in F56100 but that are not in F4801 - which
would be technically accurate but it does me no good from an analysis
standpoint - I want to compare apples to apples, thus I make sure I am
only looking at work orders in the primary file)
jw
J Walker
JDE A7.3 cum(mongrel)
F4801.
select * from f56100 exception join f4801
on $idoco=wadoco
where $idcto='WO'
The 'exception join' tells it that it is looking for those records in
F56100 that do not have a match in F4801. The 'on' portion tells how to
define the relational test between the two files (in this case, that the
document number in F56100 should be equal to the document number in
F4801). The 'where' condition is telling what specific records from
F56100 that I want to match (in this case, F56100 has many different doc
types in it, but only work order doc types are in F4801; if I was to
remove the WHERE condition that I have specified, I would get a list of
a lot of orders that are in F56100 but that are not in F4801 - which
would be technically accurate but it does me no good from an analysis
standpoint - I want to compare apples to apples, thus I make sure I am
only looking at work orders in the primary file)
jw
J Walker
JDE A7.3 cum(mongrel)
Guest
Guest
Thank you. This was the easiest and cleanest suggestion so far. I have received so much valuable input from just one post! I will try this with what I am trying to do as well as the other ideas.
