E9.2 How to join UDC's

[JDE Newbie]

I'm working on joining tables (product shipments and projects data). There's a number of UDC's that I need to know to make sense of the data. Based on the duplicate values I'm seeing it appears that to do this the three dimensions DRSY, DRRT, and DRKY need to be included in the join. Can someone please help me with one what these columns are named and how to properly join the data? Project Status an example of what I'm looking for. Right now I now which field in my engineering data is the status code but I want to join the UDC's to get the actual status description.

DRSY
DRRT
DRKY

Thanks

 

[Scott Beebe]

Unless the UDC is setup to be a 10 character code all the strings in F0005 are left padded with spaces. You won't be able to do a join to it.

 

[markdcci]

I'm working on joining tables (product shipments and projects data). There's a number of UDC's that I need to know to make sense of the data. Based on the duplicate values I'm seeing it appears that to do this the three dimensions DRSY, DRRT, and DRKY need to be included in the join. Can someone please help me with one what these columns are named and how to properly join the data? Project Status an example of what I'm looking for. Right now I now which field in my engineering data is the status code but I want to join the UDC's to get the actual status description.

DRSY
DRRT
DRKY

Thanks

Hi "JDE Newbie",

Not sure which table exactly the project status resides, but as an example take the BU type from F0006. That is usually a 2 character code in F0006, MCSTYL. It is validated by the UDC values held in F0005 where DRSY='00' and DRRT='MC'. As Scott said, it is a 10 character field in F0005 left padded with spaces, so you need to first trim it before you join to the value in F0006. I don't have an actual database handy, so you will likely need to tweak this depending on your platform and any typos I might have made. I'm used to DB2/400 but they all have something similar to the RIGHT function.

Select MCSTYL as UDC_CODE, DRDL01 as UDC_DESCRIPTION
from XXX/F0006, YYY/F0005
where DRSY='00' and DRRT='MC' and RIGHT(DRKY,2)=MCSTYL

Where XXX = location of business data and YYY=location of control tables


Let us know how it goes.

 

[JDE Newbie]

Hi "JDE Newbie",

Not sure which table exactly the project status resides, but as an example take the BU type from F0006. That is usually a 2 character code in F0006, MCSTYL. It is validated by the UDC values held in F0005 where DRSY='00' and DRRT='MC'. As Scott said, it is a 10 character field in F0005 left padded with spaces, so you need to first trim it before you join to the value in F0006. I don't have an actual database handy, so you will likely need to tweak this depending on your platform and any typos I might have made. I'm used to DB2/400 but they all have something similar to the RIGHT function.

Select MCSTYL as UDC_CODE, DRDL01 as UDC_DESCRIPTION
from XXX/F0006, YYY/F0005
where DRSY='00' and DRRT='MC' and RIGHT(DRKY,2)=MCSTYL

Where XXX = location of business data and YYY=location of control tables


Let us know how it goes.

Thanks. What are DRSY and DRRT? I'm lacking how you'd know for instance that DRSY would be '00' and DRRT would be 'MC' in this example

 

[DaveWagoner]

You will need to add aliases to each of your UDC joins if you're doing multiple in a query. for example

select * FROM f0101
INNER JOIN prdcom/f0005 CatCode04 ON ABAC04=trim(CatCode04.DRKY)
AND CatCode04.DRSY='01' AND CatCode04.DRRT='04'
INNER JOIN prdcom/f0005 CatCode05 ON ABAC05=trim(CatCode05.DRKY)
AND CatCode05.DRSY='01' AND CatCode05.DRRT='05'

Hope that's helpful!

DRKY = the UDC Value that the user selects (when you edit a UDC table 01 | 04 to add a value, that value becomes DRKY)
DRSY = product code of the UDC table (you can see this by selecting the visual assist of a field that is hooked up to a udc table)
DRRT = User Defined codes of the UDC table (you can see this by selecting the visual assist of a field that is hooked up to a udc table)

 

[JDE Newbie]

I appreciate the replies. Is there a defined way to know the proper DRSY to use? Each response has contained a "use DRSY = '00' or '01'" but did not include how you derived that special knowledge. Below is a specific dataset I'm working with.

CHNXTR from F5548011. How do I determine the correct UDC table join dimensions to bring in the proper status description? TIA

Select Case When 1=1 then RTRIM(CHINDL) end AS 'Level',RTRIM(WACO) 'Company', CHPRJM as 'ProjectNbr',RTRIM(CHDSC1) as 'PrjDescription',CHBSEQ AS ProjSortSeq, CHAN8 as 'Customer',RTRIM(CHALPH) as 'Customer Name', CHDOCO as 'WorkOrder', CHDCTO as 'Type',CHNXTR as 'Status', CHPARS as 'Parent WO'
From JDE_PDQUERY.PRODDTA.F5548011
Left outer join JDE_PDQUERY.PRODDTA.F4801 on WADOCO=CHPRJM
Where CHBSEQ<94999

 

[DaveWagoner]

I appreciate the replies. Is there a defined way to know the proper DRSY to use? Each response has contained a "use DRSY = '00' or '01'" but did not include how you derived that special knowledge. Below is a specific dataset I'm working with.

CHNXTR from F5548011. How do I determine the correct UDC table join dimensions to bring in the proper status description? TIA

Select Case When 1=1 then RTRIM(CHINDL) end AS 'Level',RTRIM(WACO) 'Company', CHPRJM as 'ProjectNbr',RTRIM(CHDSC1) as 'PrjDescription',CHBSEQ AS ProjSortSeq, CHAN8 as 'Customer',RTRIM(CHALPH) as 'Customer Name', CHDOCO as 'WorkOrder', CHDCTO as 'Type',CHNXTR as 'Status', CHPARS as 'Parent WO'
From JDE_PDQUERY.PRODDTA.F5548011
Left outer join JDE_PDQUERY.PRODDTA.F4801 on WADOCO=CHPRJM
Where CHBSEQ<94999

"DRSY = product code of the UDC table (you can see this by selecting the visual assist of a field that is hooked up to a udc table)"

 

[JDE Newbie]

"DRSY = product code of the UDC table (you can see this by selecting the visual assist of a field that is hooked up to a udc table)"

Thanks Dave

 

[DaveWagoner]

In your case the NXTR field is coming from a custom file that may or may not have a frontend on it. A backup way is to look up the default UDC for a field in the data dictionary

1) fastpath to dd, Work with Data Dictionary Items
2) look up alias NXTR, select the item to view detail
3) look here


NXTR is 40 | AT

40 = DRSY
AT = DRRT

And DRKY = the status such as 560