Multple Joins on one JDE Tables

Question

I am trying to extract our Item Master from JDE (oracle). This Item Master contains some columns with codes as values. These codes are stored in a UDC-Table. The UDC-Table contains 4 columns. The first 2 are key columns with the codes. The last 2 are the description for the code. Now I have a column in Item Master with some codes. These codes are stored in the UDC-Table where UDC.C1 = '41' and UDC.C2 = '10'. Unfortunately, UDC.C2 contains leading spaces between. Actually it looks like this: ' 10'. THen I have a second column in Item Master with additional codes. These codes are stored in UDC like this: UDC.C1 = '51' and UDC.C2 = 'AB'.

My question/ challenge is, how can I join the Item Master with the UDC in an elegant way? I have found this SQL:

SELECT 
     IMLITM, 
     IMSRP1, 
     S1UDC.Desc1, 
     IMSRP2, 
     S2UDC.Desc1, 
     IMSRP3, 
     S3UDC.Desc1
 FROM pooldba.F4101 
     LEFT JOIN pooldba.UDC('41','10') S1UDC ON IMSRP1 = S1UDC.UDC
     LEFT JOIN pooldba.UDC('51','AB') S2UDC ON IMSRP2 = S2UDC.UDC

https://brandonkirsch.com/blog/index.php/2019/07/18/sql-jde-f0005-udc-helper/

Unfortunately, it is not working. I get a failure that the SQL is false. Maybe because of the ('41', '10') and the leading spaces?

Answer 1

Issue 1 In JDE, the Item Master is F4101, and is in the Business Data datasource. The UDC table is F0005, and is in the Control Tables datasource.

F4101 might be found using pooldba.F4101, whereas F0005 might be poolctl.F0005. Or they might both be pooldba.

Issue 2 I've changed your first left join, replacing pooldba.UDC('41','10') with a subquery from F0005. Because SRP1 is 3 characters, I've used SUBSTR to remove the leading 7 spaces from DRKY.

SELECT IMLITM, IMSRP1, S1UDC.Desc1, IMSRP2, S2UDC.Desc1, IMSRP3, S3UDC.Desc1 FROM pooldba.F4101 LEFT JOIN (select substr(DRKY,8,3) as “UDC”, DRDL01 as “Desc1” from pooldba.F0005 where DRSY = '41' and DRRT = '10') S1UDC ON IMSRP1 = S1UDC.UDC LEFT JOIN pooldba.UDC('51','AB') S2UDC ON IMSRP2 = S2UDC.UDC

Issue 3 UDC 41/10 is actually 6 characters, so you won't be able to match this to SRP1.

For all UDCs, putting SY/RT into F0004 will return CDL. CDL could be used to make your SUBSTR more flexible, eg: SUBSTR(DRKY,(10-CDL),CDL)

Table F0004, keys (DTSY, DTRT) will return DTCDL, which is the code length for