0

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?

Aaron
  • 329
  • 3
  • 15
  • 1
    Your code won't run in Oracle because Oracle doesn't recognize square braces . . . and the table function call doesn't look right. Please tag with the database you are really using. – Gordon Linoff Apr 25 '20 at 12:10
  • Thanks! I have edit the code. What is about "('41','S1')" in the join? Is this approach right? – Aaron Apr 25 '20 at 12:45

0 Answers0