[SQL] JDE F0005 UDC Helper

I waited far too long in my career to write this simple function to clean up SQL queries that involve looking up JDE UDC Descriptions.

As you may know, JDE has an entire catalog of “User Defined Codes” that are all stored in a single table called F0005. This table has many properties that make it annoying to work with in SQL: it’s located in a rarely-used Schema, it blends Numeric and Character data and it right-justifies both of those types with whitespace padding in a CHAR(10) Primary Key field!

These design deficiencies kill database performance and create a tedious development environment. While there’s nothing I can do to convince Oracle to clean up their SQL act, there is something I can do to make developing JDE SQL queries a little more delightful.

I present to you: Custom.UDC() It’s a simple function that does a simple thing, but provides usability and readability for the developers and analysts who have to work with raw SQL code. It automatically trims the key values to remove that annoying whitespace, which means you can quickly write (or read) a SQL query that involves many repeated joins to the UDC.

Create Script: Caution: You MUST have (or create) a “Custom” schema and that’s if you live a bit on the edge. DO NOT PUT THIS IN PRODDTA OR PRODCTL FOR ANY REASON.

CREATE FUNCTION [Custom].[UDC](
     @SY CHAR(2),
     @RT CHAR(2)
 )
 RETURNS TABLE AS RETURN
     SELECT
         LTRIM(DRKY) as [UDC],
         CASE WHEN DRDL01 = '.' THEN '' ELSE DRDL01 END as [Desc1],
         DRDL02 as [Desc2],
         DRSPHD as [SpecialHandling]
     FROM 
         [DataMirror].[Mirror].[JDESQLDB.JDE_PRODUCTION.PRODCTL.F0005]
     WHERE DRSY = @SY AND DRRT = @RT;

Example 1: Select all values for UDC 01|01

SELECT * FROM Custom.UDC('01','01')

Example 2: Retrieve UDC Descriptions for Item Master Sales Codes 1, 2 & 3.

SELECT 
     IMLITM, 
     IMSRP1, 
     S1UDC.Desc1 as [Sales Code 1 Desc], 
     IMSRP2, 
     S2UDC.Desc1 as [Sales Code 2 Desc], 
     IMSRP3, 
     S3UDC.Desc1 as [Sales Code 3 Desc]
 FROM DataMirror.Mirror.[JDESQLDB.JDE_PRODUCTION.PRODDTA.F4101] 
     LEFT JOIN Custom.UDC('41','S1') S1UDC ON IMSRP1 = S1UDC.UDC
     LEFT JOIN Custom.UDC('41','S2') S2UDC ON IMSRP2 = S2UDC.UDC
     LEFT JOIN Custom.UDC('41','S3') S3UDC ON IMSRP3 = S3UDC.UDC

The small but important difference is that the JOIN syntax is much cleaner to read. Specifying the UDC ‘co-ordinates’ is cleaner and there is no need to perform LTRIM() string manipulation in the JOIN predicate to the right-justified F0005 DRKY field.