Guest
Guest
I had tried this, but made the mistake of also setting the record selction incorrectly. Of course there were NO records selected. Try, try again.
Guest
Guest
Re: RE: Query for unmatched records
Excuse me, I do not understand this at all. Match type? I am relatively new to this...
Excuse me, I do not understand this at all. Match type? I am relatively new to this...
Guest
Guest
Re: RE: Query for unmatched records
This is excellent. Thank you. I copied a sample SQL for exception join, but it looked more complicated. I don't have to give the qualified name for the fields when doing the "ON"? ie..TESTDATA/F56100.$idcto
This is excellent. Thank you. I copied a sample SQL for exception join, but it looked more complicated. I don't have to give the qualified name for the fields when doing the "ON"? ie..TESTDATA/F56100.$idcto
Guest
Guest
Re: RE: RE: Query for unmatched records
Thank you for your input. This sounds exactly right. I made the mistake of selecting records from the secondary file, and used NE on the join comparison test. I will try again.
Thank you for your input. This sounds exactly right. I made the mistake of selecting records from the secondary file, and used NE on the join comparison test. I will try again.
Guest
Guest
I have tried all suggestions offered. neither method works. Here are samples of my last efforts:
STRSQL
select $BAC01, $BPDP1, $BLITM
from F58704 exception join F58501
on $BLITM = $TLITM
where $BAC01 = 'HOU' and
$BPDP1 between '010' and '015'
and $B$LPO < 102182
I also tried: WRKQRY
I used #3 for unmatched files. then $BLITM EQ $TLITM
I only selected records from the primary file
$BAC01 = HOU
$BPDP1 RANGE '010' '015'
$B$LPO LE 102182
Thanks for all the help.
STRSQL
select $BAC01, $BPDP1, $BLITM
from F58704 exception join F58501
on $BLITM = $TLITM
where $BAC01 = 'HOU' and
$BPDP1 between '010' and '015'
and $B$LPO < 102182
I also tried: WRKQRY
I used #3 for unmatched files. then $BLITM EQ $TLITM
I only selected records from the primary file
$BAC01 = HOU
$BPDP1 RANGE '010' '015'
$B$LPO LE 102182
Thanks for all the help.
Guest
Guest
Many thanks to all who responded. The task is finally complete. Here is how I was able to achieve the desired results. I used SQL because there are duplicate records that I did not need to display in the primary file and didn't know how to eliminate them using WRKQRY. I kept missing those 8 records because they DID exist in the secondary file, only for a different city. The ON statement is crucial to this working properly.
select distinct $BAC01, $BPDP1, $BLITM
from F58704 exception join F58501
on $BLITM = $TLITM and $BAC01 = $TAC01
where $BAC01 = 'HOU' and $B$LPO < 102182
and $BPDP1 between '010' and '015'
I learned quite a few new techniques through this process. Hope this helps someone else. Happy Thanksgiving all!!!
select distinct $BAC01, $BPDP1, $BLITM
from F58704 exception join F58501
on $BLITM = $TLITM and $BAC01 = $TAC01
where $BAC01 = 'HOU' and $B$LPO < 102182
and $BPDP1 between '010' and '015'
I learned quite a few new techniques through this process. Hope this helps someone else. Happy Thanksgiving all!!!
John_Dickey
Reputable Poster
Well, without being able to see your files and the query, I do not know what
else to say. It seem to be saying that for HOU, between 010 and 015, and
date LT 102182, all records in file 1 have a match in file 2.
Just this morning I did an IBM WRKQRY looking for parts in file 1 with no
record in file 2, and got a list of 17 parts, which I validated (doing
something for Production, not just for this reply) as correct. I did join
type 3. I see nothing wrong with your WRKQRY specs listed below on the
surface (not knowing your data). I did not do Record Selection.
Maybe as a test you take off the record selection and see if you get any
records. If so, see if any of those records match your record selection
specs. If not, then your query worked correctly.
John Dickey
JDE Financial Systems
Administrator/Programmer/Analyst
White-Rodgers, Division of Emerson
St. Louis, MO
314-577-1466
else to say. It seem to be saying that for HOU, between 010 and 015, and
date LT 102182, all records in file 1 have a match in file 2.
Just this morning I did an IBM WRKQRY looking for parts in file 1 with no
record in file 2, and got a list of 17 parts, which I validated (doing
something for Production, not just for this reply) as correct. I did join
type 3. I see nothing wrong with your WRKQRY specs listed below on the
surface (not knowing your data). I did not do Record Selection.
Maybe as a test you take off the record selection and see if you get any
records. If so, see if any of those records match your record selection
specs. If not, then your query worked correctly.
John Dickey
JDE Financial Systems
Administrator/Programmer/Analyst
White-Rodgers, Division of Emerson
St. Louis, MO
314-577-1466
Guest
Guest
Re: RE: Query for unmatched records
The query worked correctly, but I ended up using SQL to eliminate printing of duplicate records in the primary file. SQL has a DISTINCT keyword that takes care of that. Can you tell me how to make WRKQRY print a record only once?
Thank you.
The query worked correctly, but I ended up using SQL to eliminate printing of duplicate records in the primary file. SQL has a DISTINCT keyword that takes care of that. Can you tell me how to make WRKQRY print a record only once?
Thank you.
SallyWhite
Well Known Member
Re: RE: Query for unmatched records
Sheryl, the only way I know to do this w/ WRKQRY is to first create a query that summarizes your file into an outfile, by sorting and breaking on the keyfields, and using the summary option on the output screen (beneath the speicifcation for display/print/file as output). Then use the results from this to find unmatched records in your secondary file. (Or vice versa, whichever file has the duplicates.) HTH!!!!
Sheryl, the only way I know to do this w/ WRKQRY is to first create a query that summarizes your file into an outfile, by sorting and breaking on the keyfields, and using the summary option on the output screen (beneath the speicifcation for display/print/file as output). Then use the results from this to find unmatched records in your secondary file. (Or vice versa, whichever file has the duplicates.) HTH!!!!
