Join Query question

JeffWilson

Member
OK...this sounds simple, but I can't seem to get it. I am trying to do a
Query/400 or Query manager quesry to get ALL items from the F4101 and the
costs for each only if they exist and only if they are Ledger Type 07. So I
want Item 12345 with a it's "07" cost or with NO cost if there is no "07"cost
in F4105. Since I am selecting based on the COLEDG field, I am not getting
the records that are not in that file. Can someone point me in the right
direction on this? Thanks very much in advance for any help you can give.

Jeff Wilson
Director of IT - Manufacturing
Unilever Cosmetics International
 
Make sure you make F4101 the primary file and select Join type 2. That will
pull every item in F4101 even if it doesn't exist in F4105.

Kristin Alzak
J.D. Edwards Internal Consultant
A7.3 c 8
(781) 324-8094 x2330

New England Coffee Company
www.necoffeeco.com
 
As far as I know, you can't do this with World Writer. If it's
not there it won't print. However, I'll be anxious to see if
someone comes back and says that you can!

Becky Bowman
United Musical Instruments, Inc.
[email protected]
 
Use an outfile, heck I never print anything. Can't analyze a printout very
well.
Sure as heck can't make it pretty. Want to summarize? Use Excel's pivot
table.
 
World Writer cannot do outer joins. You'll have to use Query or if you
like, some third party software (e.g. Crystal Reports)


Thanks,

John H. Lee

Missouri-American Water Company
Business Support Specialist
Phone: (314) 996-2278
Fax: (314) 991-3423
 
By putting the data selection of '07' on the secondary file, you will not
get a "hit" if the record does not exist in the F4105 (secondary) file.

Either have the data selection to include both '07' and ' ' (blank)
or select the '07' records in a query prior to this one save that to a
file, then use a '2' join type against the new file. Take the data
selection of '07' out of the second query.


Dan Verner
Information Technology Manager
 
Make a query joining F4101 and F4105, in that order, join type 2, data selection for COLEDG equal to 7, put whatever other field selections you want in the file, run the query to create a file. Then, take that same query, change join type to "3" - unmatched records, remove the data selection for COLEDG, and run that query again for file output, BUT change the data in file parameter to "5" - to add this data to the previous data - then you have a combined file with all items with cost type 7, and also items with no cost type at all. I just tried this, and it looks like it works. If you have any questions, let me know.

David Bryant
[email protected]
 
Re: RE: Join Query question

Becky, Jeff is using Query/400, not World Writer.


Dave Kahn (World A7.3 cum 10)
=========
 
Kristin,

Thanks, but this is not the case. That is the problem. I thought this would
work, but if you select records based on the second file you do NOT get the
F4101 records if there are no F4105 records!

Jeff Wilson
Director of IT
Unilever Cosmetics International

<< Make sure you make F4101 the primary file and select Join type 2. That
will
pull every item in F4101 even if it doesn't exist in F4105.

Kristin Alzak
J.D. Edwards Internal Consultant
A7.3 c 8
(781) 324-8094 x2330 >>
 
David

Thanks! You are VERY close..but not yet. This query gives me all 07 records
and all records that have NO match in the 2nd file. The problem is that I
need all records that have no 07 match in the 2nd file. So if there is an 03
cost type, I do not get it in this query, but I need it. (I am looking for
things that have no final standard cost, but they may have a last in or other
cost). If you can figure this last wronkle out, I would owe you. Thanks for
what you ahve given me so far.

Jeff Wilson
Director of IT - Manufacturing
Unilever Cosmetics International


In a message dated 07/17/2001 5:26:17 PM Eastern Daylight Time,
[email protected] writes:

<< Make a query joining F4101 and F4105, in that order, join type 2, data
selection for COLEDG equal to 7, put whatever other field selections you want
in the file, run the query to create a file. Then, take that same query,
change join type to "3" - unmatched records, remove the data selection for
COLEDG, and run that query again for file output, BUT change the data in file
parameter to "5" - to add this data to the previous data - then you have a
combined file with all items with cost type 7, and also items with no cost
type at all. I just tried this, and it looks like it works. If you have any
questions, let me know. >>
 
Well then, re-do the first query below, except don't add the data selection
to pick "07" records. This would give ALL items with all cost records.
Then re-run the second query, as I said below, again adding to the file,
NOT replacing. This gives you an output file with all cost records and all
items without cost records. Then, if you want, you can run a further query
over that outfile to exclude "07" records.

Alternatively, you could set the data selection in the first query to
exclude "07" records, picking up everything else, (COLEDG NE 07 ), then run
the second query as before. If this isn't what you want, but is closer,
please feel free to mail me off line, because I obviously don't exactly
understand what you need.

Again, if you have further questions, please feel free to contact me
directly.

David Bryant




JeffWilson <[email protected]>@jdelist.com on 07/19/2001 01:15:39 PM
 
The problem is doing data selection off of the second file
You are asking for ONLY cost type equal to '07'. If there is no matching
join record in the second file (F4105), the value of the character field
will be blank.

In this case change your data selection to be a list of values '07' and '
'


Dan Verner
Information Technology Manager




JeffWilson
<[email protected] To: [email protected]
m> cc:
Sent by: Subject: Re: Join Query question
owner-jdeworldml@j
delist.com


07/19/01 10:53 AM
Please respond to
jdeworld






Kristin,

Thanks, but this is not the case. That is the problem. I thought this would

work, but if you select records based on the second file you do NOT get the

F4101 records if there are no F4105 records!

Jeff Wilson
Director of IT
Unilever Cosmetics International

<< Make sure you make F4101 the primary file and select Join type 2. That
will
pull every item in F4101 even if it doesn't exist in F4105.

Kristin Alzak
J.D. Edwards Internal Consultant
A7.3 c 8
(781) 324-8094 x2330 >>




--------------------------
 
Back
Top