World Writer Join

I have a need to join the F4311 (Purchase order) with the F4801 (Work Order) file. In F4311 there is the field PDSBL that contains the work order number corresponding to WADOCO in F4801, but they are of different data types (F4311 field is alpha whereas F4801 field is numeric) thus I cannot . Does anyone know how I can join these two files based on the work order number?

Val,

You are trying to do what JDE call a "Soft Join" which involves the following:
1) Create a new field (WADOCOALPH)
2) Use the DIGITS calculation to convert WADOCO to alpha in your new field
3) On the Data Selection screen request that PDSBL=WADOCOALPH
It's not a true join but will give you the desired result
(see page 3-39 of the A73 World Writer Manual for more details)

Yes this is possible to do with WW. There are three steps involved.
1. Identify the field that has a numerical value. In your case it is wadoco in F4801. Under Output field specifications, create a new field, say mydoco, and use the formula "= DIGITS ( wadoco )". This is now the alphnumeric wo number. Set mydoco to lenght of 8 characters.
2. Under Data Selection set the field pdsbl in F4311 equal to mydoco.
3. Do not join files F4311 and F4801 under the File Join Relations. You will get an error message from jde but just keep entering through it. Actually, I just create a dummy join while I'm creating the WW and just before it's time to run the WW, I delete this dummy join. This way the error message only happnes at the very end and not every time that I go into the WW. Just remember to delete it before running the WW.
Also be prepared for the WW to take a little longer to run.

World Writer does not support any kind of conversion (from Char to Num or Viceversa) at 'File Joining' stage.
Probably you can achieve the same thru Sql statements.
Select Wadoco, Pdsbl, <any other fields you need> From F4311, F4801
Where Wadoco = Int(PdSbl) And <other conditions you wish to add>

Set up a Calculated field in Output Field Specifcations. Calc is DIGITS(WADOCO). This converts a numeric field to an alphanumeric field.

I suppose there are a number of ways to do this, but I find an SQL View to be about the easiest. Start an interactive SQL session (STRSQL) and type something like this:
CREATE VIEW mylib/myview (NUMBER, WADOCO, WATRDJ, WAITM <whatever fields you want from the F4801> AS
SELECT DIGITS(WADOCO), WADOCO, WATRDJ, WAITM <your fields here> FROM F4801 <optionally put a WHERE clause here to limit the data your World Writer is going to have to deal with>
When you press enter, the system will respond with a message telling you the view has been created. If you like do a SELECT * FROM mylib/myview, and you'll see that the first column is the work order number converted to an 8 character alpha field by the DIGITS function.
Now you can create your World Writer using the F4311 and myview, joining PDSBL to NUMBER.
If you need to get rid of the view and recreate it the syntax is DROP VIEW mylib/myview.

But finish the information needed.

Then the join is done in the Data Selection Specifications by comparing the alpha field in the F4311 to the new field built in the Output Specifications.

Hope this makes sense but there are several places that one field is Alpha and another is Numeric. I believe the same problem exists between the PO Number in the Purchasing/Payables systems and the G/L transaction file, F0911.

Hello Val
I would be interested to know if you are using WW for a particular reason. I always found that it was a lot easier to create an extract file using query with an addition alpha PO number field and then run another query for the report.

Mike

You can also try joining on the WAPARS field. In our site, since we don't use parent work orders, we're able to use the WAPARS (Parent WO) field in F4801 joined to PDSBL. It's an 8 char alphanumeric version of the WADOCO IF the work order is its own parent. It works for us. It could work for you depending your site's configuration.