Here is my scenario....

I have one file that has a PO # in it which is displayed as 8 digits all the time, Ex: 00123456

My other file also has PO # in it but it does not display the leading zeros on output, Ex. 123456.

So I need to be able to truncate the zero's from the PO field in the first file before I do my join right?

Well I tried creating a defined field in the Master which uses a substring function to just grab the last 6 numbers in the PO field.

But then when I do the join with the truncated PO from file 1 to the normal PO field in file 2 the report executes but does not return the corresponding data in file 2 relating to the PO# in file 1&2.

Code is below....... "POSHORT" is my defined field in the master, "GLPO" is the 8 digit PO # in the master file. "PONUM" is the 6 digit PO# in the second file.

JOIN
LEFT_OUTER T_SQL_F0911.T_SQL_F0911.POSHORT WITH T_SQL_F0911.GLPO IN T_SQL_F0911 TO UNIQUE
T_PROD_INVOICE.T_PROD_INVOICE.PONUM IN T_PROD_INVOICE AS J0
END
JOIN
LEFT_OUTER T_PROD_INVOICE.T_PROD_INVOICE.PONUM IN T_SQL_F0911 TO UNIQUE
T_PROD_POLINE.T_PROD_POLINE.PONUM IN T_PROD_POLINE AS J1
END
JOIN
LEFT_OUTER T_PROD_POLINE.T_PROD_POLINE.REFWO IN T_SQL_F0911 TO UNIQUE
T_PROD_WORKORDER.T_PROD_WORKORDER.WONUM IN T_PROD_WORKORDER AS J2
END
JOIN
LEFT_OUTER T_PROD_WORKORDER.T_PROD_WORKORDER.SITEID IN T_SQL_F0911 TO UNIQUE
T_PROD_MAX_ATTRIBUTES_LOCATION.T_PROD_MAX_ATTRIBUTES_LOCATION.SITEID
IN T_PROD_MAX_ATTRIBUTES_LOCATION AS J3
END
TABLE FILE T_SQL_F0911
PRINT
GLDGJ
GLDATE2
GLMCU AS 'LOC'
GLSBL AS 'EXP CTR'
GLOBJ AS 'ACCOUNT'
GLDCT AS 'DT'
GLDOC AS 'DOC NUM'
INVOICEAMOUNT
GLEXA AS 'DESCRIPTION'
GLVINV AS 'INVOICE NUMBER'
T_PROD_INVOICE.ENTERDATE/HMDYY AS 'INVOICE ENTER DATE'
T_PROD_WORKORDER.SITEID
T_PROD_WORKORDER.WONUM
POSHORT AS 'PO NUMBER'
T_PROD_WORKORDER.WORKTYPE
T_PROD_WORKORDER.DESCRIPTION
T_PROD_WORKORDER.EQNUM
T_PROD_WORKORDER.PROBLEMCODE
T_PROD_WORKORDER.REPORTDATE/HMDYY AS 'WO DATE'
T_PROD_WORKORDER.ACTFINISH/HMDYY AS 'WO COMPLETE DATE'
T_PROD_WORKORDER.TARGCOMPDATE/HMDYY AS 'LOS DATE'
WHERE ( GLOBJ EQ '650127' ) AND ( GLDGJ GE 108153 );

PO # ... Is that A6?
Does your join give an error?
Make sure they are both the same format.

Sayed

You might look in to defined based joins as well, as Sayed says the format must be the same, alpha to alpha, numeric to numeric.

Rob,

To supplement what everyone has said, remember that a defined based join can only occur in the parent file within the join, and remember to use the syntax WITH [fieldname] to ensure that the join occurs at the correct location.

I would agree with Tom in my preferred method of joining two fields such as this although that is preference and not necessity.

If you have PO number in your parent file declared as A8 - i.e. 00012345 or 00123456 and your child file has PO declared as P9 with equivalent values of 12345 and 123456 then I would define a field in the parent to match that of the child (bearing in mind what I stated above). So, if the child field format was numeric then you would expect code such as -

JOIN CLEAR *
JOIN [defined_po] WITH PO_NUMBER IN [parent_file] TO MULITPLE PO_NUMBER IN [child_file] AS J1
-*
DEFINE FILE [parent_file]
[defined_po]/P9 WITH PO_NUMBER = EDIT(PO_NUMBER);
etc.
END

I know that many folks will now jump on board and say that because the [defined_po] field is declared in relation to the EDIT(PO_NUMBER) you do not strictly require the WITH PO_NUMBER, but I would encourage the use of WITH as a coding style because there will be times when the field is not inferred and you will require the WITH fieldname syntax. If you get into the habit of coding in this manner then (to my mind) that will be one less occasional error to deal with.

Good luck.

Leah,

Great to see you back

T

quote:

Originally posted by Tom Flynn:
Rob,

What if the PONUM is 5 digits???
I will suggest going the other way, ZERO fill PONUM to 8 bytes, then do the JOIN...

Tom

The PO Num is always 6 digits, no exceptions.

But if I were going to Zero fill the 6 digit PO Num in the second file what function do I use for that?

With regards to others suggestions, yes both fields are the same format, A8V.

Tom the join needs to go the other way.

The host file is the T_SQL_F0911, so I want to start with that file and pull all matching PO's in T_PROD_INVOICE.

Does this change things around then? I am lost now.

Rob,

If I understand you correctly both your PO num fields are A8V, but one has leading 0's and the other does not. Is the second one right justified or left justified?

quote:

Originally posted by Danny-SRL:
Rob,

If I understand you correctly both your PO num fields are A8V, but one has leading 0's and the other does not. Is the second one right justified or left justified?

The second one is the one where there are no leading zeros.

When I added that in the master it won't let me parse the file to begin setting up my joins.

For some reason it didn't like this in the master file? DEFINE POSHORT/I6S = GLPO; $

quote:

I have one file that has a PO # in it which is displayed as 8 digits all the time, Ex: 00123456

My other file also has PO # in it but it does not display the leading zeros on output, Ex. 123456.
. . . I tried creating a defined field in the Master which uses a substring function to just grab the last 6 numbers in the PO field.

. . . both fields are the same format, A8V.

From what you write, the layout of the values in the PONUM/A8V field in the second file is unclear -- it might be
[a] right-justified space-filled ('^^123456')
or [b] left-justified space-filled to length 8 ('123456^^');
or [c] left-justified and truncated to length 6 ('123456').

Whichever it is, the defined field must match the format and content layout:
POSHORT/A8V =
[a] '^^' | SUBSTV(8,GLPO,3,6,'A6');
[b] SUBSTV(8,GLPO,3,6,'A6') | '^^';
[c] SUBSTV(8,GLPO,3,6,'A6V');
... and you should be good to go.

Tom,

He has A8V fields in both files! So you need to take out the leading 0's in the host file and create a A8V field. That is why I asked about left or right justification.

Jack, Exactly!

This message has been edited. Last edited by: Danny-SRL,

Thanks for all the help so far. Still having lots of issues.....

(FOC370) THE FIELDNAME USED IN JOIN CANNOT BE FOUND IN THE FILE:
T_SQL_F0911.T_SQL_F0911.POSHORT
BYPASSING TO END OF COMMAND


Define in Master File #1 T_SQL_F0911
DEFINE POSHORT/I8L = EDIT(GLPO); $

Define in Master File #2 T_PROD_INVOICE
DEFINE PACMANPO/I8L = EDIT(PONUM); $


Here is my current code....

JOIN
LEFT_OUTER T_SQL_F0911.T_SQL_F0911.POSHORT IN T_SQL_F0911 TO UNIQUE
T_PROD_INVOICE.T_PROD_INVOICE.PACMANPO IN T_PROD_INVOICE AS J0
END
JOIN
LEFT_OUTER T_PROD_INVOICE.T_PROD_INVOICE.PONUM IN T_SQL_F0911 TO UNIQUE
T_PROD_POLINE.T_PROD_POLINE.PONUM IN T_PROD_POLINE AS J1
END
JOIN
LEFT_OUTER T_PROD_POLINE.T_PROD_POLINE.REFWO IN T_SQL_F0911 TO UNIQUE
T_PROD_WORKORDER.T_PROD_WORKORDER.WONUM IN T_PROD_WORKORDER AS J2
END
JOIN
LEFT_OUTER T_PROD_WORKORDER.T_PROD_WORKORDER.SITEID IN T_SQL_F0911 TO UNIQUE
T_PROD_MAX_ATTRIBUTES_LOCATION.T_PROD_MAX_ATTRIBUTES_LOCATION.SITEID
IN T_PROD_MAX_ATTRIBUTES_LOCATION AS J3
END
TABLE FILE T_SQL_F0911
PRINT
GLDGJ
GLDATE2
GLMCU AS 'LOC'
GLSBL AS 'EXP CTR'
GLOBJ AS 'ACCOUNT'
GLDCT AS 'DT'
GLDOC AS 'DOC NUM'
INVOICEAMOUNT
GLEXA AS 'DESCRIPTION'
GLVINV AS 'INVOICE NUMBER'
T_PROD_INVOICE.ENTERDATE/HMDYY AS 'INVOICE ENTER DATE'
T_PROD_WORKORDER.SITEID
T_PROD_WORKORDER.WONUM
POSHORT AS 'PO NUMBER'
T_PROD_WORKORDER.WORKTYPE
T_PROD_WORKORDER.DESCRIPTION
T_PROD_WORKORDER.EQNUM
T_PROD_WORKORDER.PROBLEMCODE
T_PROD_WORKORDER.REPORTDATE/HMDYY AS 'WO DATE'
T_PROD_WORKORDER.ACTFINISH/HMDYY AS 'WO COMPLETE DATE'
T_PROD_WORKORDER.TARGCOMPDATE/HMDYY AS 'LOS DATE'

One more thing, I double checked my field formats in the Master Files.

GLPO in Master File #1 T_SQL_F0911 is A16

PONUM in Master File #2 T_PROD_PO is A8V

Does this change things?

Still no dice, it is not recognizing POSHORT as being the same format as PONUM in the T_PROD_INVOICE table, thus no results from the T_PROD_INVOICE table (the last three columns are from the T_PROD_INVOICE table)....

Let me throw a wrench into this whole problem.....

So I was just checking the DB because our po #'s were approaching #1000000 thus pushing the PO# to 7 digits now.

Well wouldn't ya know it on Monday it hit 1000000.

What does that do to this whole thing?

Because old PO's would be 8 digits pre-filled with two zero's in master file 1, but just 6 digits in the master file 2 that I am joining.

New PO's would be 8 digits pre-filled with one zero in file 1 but just 7 digits in file 2 that joins against file 1.

Now's the time to rebuild the tables, making the PO Number columns uniform in all the tables. At this juncture you may just have sufficient leverage.

If the field is (forever and ever) strictly nummeric, consider declaring it as such, rather than as a varchar column holding numeric content.

How would I re-build the table? Can I just change the format of the column in my MFD? Or would I have to change the format in my source DB? (Not an Option).

The sad thing about this is that both of these DB's are sunsetting in the near future and we will be transitioning to all new datasources.

If I can't get this figured out in the next few days I will be ending the efforts.

What kind of data source are we talking about here? Is it relational?

What is the ACTUAL format for these two fields in the masters. If the ACTUAL is A8V, then there is no point in changing the master. What would that buy you? The PO number that you mentioned would fit in an A8V field.

I do not recommend changing relational masters by hand and there is no point in doing so if you are not changing the source table definition in the data base.

Not sure what relational means. (Sorry I know I'm a total Newbie!)

The main datasource is JD Edwards, and it is an ODBC connection, this datasource contains my paid invoice information.

The datasource I am joining against is an asset management system that contains all the work order information and corresponding PO # and invoice entry information.

Actual in JD Edwards is A16, Actual in other table is A8V.

Rob,

JDE is relational. Relational means that SQL is the native language of the data base.

Can you please tell me what the ACTUAL format is in the masters for PO number?

Ginny,

In JDE it is:

FIELD=GLPO, ALIAS=GLPO, USAGE=A16, ACTUAL=A16, $

In Asset Mgmt DB it is:

FIELD=PONUM, ALIAS=PONUM, USAGE=A8V, ACTUAL=A8V, MISSING=ON, $

Ok, since the data is in two different data bases, I would extract the data from each separately, 'fix' the PO number in one of them to match the other and put the data in hold files. Save the one that is to be the target of the join as a FORMAT FOCUS INDEX ponum_field.

Then do your join.

At this point, I'm not going to tell you how to fix the po number fields so that they are the same. That has been discussed at length in previous posts on this thread.

I think this method will simply your situation. Make sure that when you do your extracts, you apply appropriate WHERE clauses to each to minimize the size of the answer sets.

Also, if one of the files doesn't have a lot of discreet entries, you could use the WHERE IN FILE syntax to select data from the other one once you had the formats fixed.