The JDE Quirks Guide
JD Edwards stores data in ways that make a first extract wrong in exactly the ways that look right — dates as six-digit numbers, amounts with no decimal point, blanks where you expect nulls, codes that mean nothing until you decode them. This guide is the shortcut past every one of those, with copy-ready Databricks SQL.
Julian dates (CYYDDD)
JDE stores dates as a six-digit number, CYYDDD: C is the century offset from 1900, YY is the year within that century, and DDD is the day of the year. So 126203 is 1900 + 126 = 2026, day 203 — 2026-07-22. A raw 0means “no date” and must become NULL, not 1899-12-31.
Convert on the way out with the expression below. For filtering, don't convert every row — turn your date bounds into CYYDDD once and compare against the raw column, which keeps the predicate index-friendly.
-- CYYDDD Julian → DATE. This is the exact expression the generated
-- boilerplate SQL uses, so your docs and your queries never drift.
SELECT
CASE WHEN sd.SDTRDJ IS NULL OR sd.SDTRDJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(sd.SDTRDJ AS INT) DIV 1000, 1, 1), CAST(sd.SDTRDJ AS INT) % 1000 - 1) END AS order_date
FROM proddta.f4211 sd
-- Reverse conversion for filters. Don't convert every row — convert your
-- date bounds to CYYDDD once and compare against the raw column:
-- CYYDDD = (YEAR(d) - 1900) * 1000 + DAYOFYEAR(d)
WHERE sd.SDTRDJ BETWEEN
(YEAR(DATE '2026-01-01') - 1900) * 1000 + DAYOFYEAR(DATE '2026-01-01')
AND (YEAR(DATE '2026-12-31') - 1900) * 1000 + DAYOFYEAR(DATE '2026-12-31')Implied decimals
Numeric amount and quantity columns store no decimal point. The display-decimals setting in F9210 tells you where it belongs, and it varies by column: unit prices commonly carry four implied decimals, extended amounts two, whole-unit quantities none.
The trap: 100000 in a two-decimal amount column is 1,000.00, not one hundred thousand. Scale by POWER(10, n), and always verify n for a column in F9210 before you trust a raw figure — a wrong assumption is off by orders of magnitude.
-- Amounts and quantities carry no stored decimal point. The display-decimals
-- setting (F9210) says where it goes; divide by 10^n to get the real value.
SELECT
sd.SDUORG / POWER(10, 0) AS qty_ordered, -- 0 implied decimals
sd.SDUPRC / POWER(10, 4) AS unit_price, -- 4 implied decimals
sd.SDAEXP / POWER(10, 2) AS extended_price -- 2 implied decimals
FROM proddta.f4211 sd
-- 100000 in SDAEXP with 2 implied decimals is 1,000.00 — not one hundred
-- thousand. Always verify the display decimals for a column in F9210 before
-- trusting a raw amount.No nulls — blanks and zeros
JDE predates widespread nullable columns, so it doesn't use NULL. Character fields hold blanks; numeric fields hold zeros. Both usually mean “nothing here,” but they aggregate differently than NULL — a blank still counts, a zero still averages in.
Normalize at read time with NULLIF: collapse blank strings and sentinel zeros to NULL so counts, averages, and distinct-value logic behave the way analysts expect.
-- JDE stores blanks and zeros, never NULL. A blank string and a real "0"
-- both mean "no value" in most columns — collapse them so aggregates behave.
SELECT
NULLIF(TRIM(sd.SDLOTN), '') AS lot_number, -- blank → NULL
NULLIF(sd.SDAN8, 0) AS sold_to, -- 0 address → NULL
SUM(sd.SDAEXP / POWER(10, 2)) AS revenue -- zeros sum harmlessly
FROM proddta.f4211 sd
GROUP BY 1, 2Business unit padding (MCU)
The business unit / branch plant field MCU is a twelve-character field, right-justifiedand space-padded. So business unit “30” is stored as ten spaces followed by “30”.
This is the single most common silent join-killer in JDE analytics: join MCU to MCU without trimming and the padding mismatch drops rows with no error. TRIM both sides of every business-unit join, and expect the same padding on other right-justified keys.
-- MCU (business unit / branch plant) is CHAR(12), RIGHT-justified and
-- space-padded. " 30" and "30" are different strings, so an untrimmed join
-- silently drops rows. TRIM both sides — every time.
SELECT
sd.SDDOCO,
mc.MCDL01 AS business_unit_name
FROM proddta.f4211 sd
JOIN prodctl.f0006 mc
ON TRIM(mc.MCMCU) = TRIM(sd.SDMCU)Item numbers (ITM / LITM / AITM)
Every item has three numbers. ITM is the short, numeric internal key that transaction tables join on. LITM is the long, user-facing item code people actually recognize. AITM is the third number — a catalog or cross-reference code.
The gotcha is joining on the wrong one. Transaction tables like F4211 and the item ledger carry all three, but the reliable join to the item master F4101 is on the short number ITM. Display LITM; join on ITM.
-- Three item numbers travel together:
-- ITM (short) — the internal numeric key programs join on
-- LITM (long) — the user-facing item code people recognize
-- AITM (third) — a catalog / cross-reference code
-- Join the item master F4101 on the SHORT item number.
SELECT
sd.SDLITM AS item_code, -- show the long number
im.IMDSC1 AS item_description
FROM proddta.f4211 sd
JOIN proddta.f4101 im
ON im.IMITM = sd.SDITM -- join on the short numberUDC decode (F0005 / F0004)
User Defined Codes are JDE's lookup mechanism. A coded column — order type, line type, a category code — stores only the code; its meaning lives in F0005, keyed by a composite of system (DRSY), type (DRRT), and the code value itself (DRKY). F0004 defines each code type; F0005 holds the values you join to.
Two catches: DRKY is right-justified like other JDE keys, so TRIM it before comparing; and the system/type pair is specific to each field. Every column flagged UDC in this reference carries its own system and type — drop them into the two constants in the join and the decode is done.
-- Coded columns don't store their meaning; decode them against F0005 on the
-- composite key: system (DRSY) + type (DRRT) + code (DRKY). DRKY is
-- right-justified, so TRIM it. Worked example: decode SDDCTO (order type).
SELECT
sd.SDDCTO AS order_type_code,
TRIM(udc.DRDL01) AS order_type_desc
FROM proddta.f4211 sd
LEFT JOIN prodctl.f0005 udc
ON udc.DRSY = '00' -- system code for Document Type
AND udc.DRRT = 'DT' -- UDC type
AND TRIM(udc.DRKY) = TRIM(sd.SDDCTO)
-- F0004 holds the definition of each UDC type; F0005 holds its values. You
-- only need F0005 to decode. Every field flagged "UDC" in this reference
-- carries its own system/type — swap them into the two constants above.Audit columns
Every JDE table ends with the same audit trail, under a table-specific prefix but constant data-dictionary aliases: USER (who), PID (which program), JOBN (which job), UPMJ (update date, in Julian), and TDAY (update time). The generated boilerplate SQL in this reference omits them by default to keep SELECTs readable.
They're more than metadata, though. PIDis a free lineage marker: rows written by a given program carry that program's code, so filtering on PID isolates exactly what a batch job produced — invaluable for tracing where a number came from.
-- Every table carries an audit trail. The prefix changes per table (SD, IM,
-- AB, ...) but the data-dictionary aliases are constant:
-- USER = who, PID = which program, JOBN = which job,
-- UPMJ = update date (Julian CYYDDD), TDAY = update time.
-- Use PID as a free lineage marker: rows a program wrote carry its code.
SELECT *
FROM proddta.f4211 sd
WHERE sd.SDPID = 'R42800' -- only the lines Sales Update (R42800) bookedSales history (F4211 → F42119)
Open and in-process sales order lines live in F4211. At sales update (the R42800 batch job), finished lines are moved out of F4211 into F42119, the sales order history file, which shares the F4211 layout column-for-column.
The consequence for analytics: neither table alone is “all sales.” Reporting only on F4211 silently drops everything that has shipped. To see the full picture, query F4211 UNION ALL F42119.
-- Open order lines live in F4211. At sales update (R42800), finished lines
-- move to F42119, which shares the F4211 layout exactly. Neither table alone
-- is "all sales" — UNION them.
SELECT SDDOCO, SDLNID, SDITM, SDAEXP / POWER(10, 2) AS extended_price
FROM proddta.f4211 -- open / in-process
UNION ALL
SELECT SDDOCO, SDLNID, SDITM, SDAEXP / POWER(10, 2) AS extended_price
FROM proddta.f42119 -- shipped / closedZ tables (interface staging)
Interface and EDI tables carry a trailing “Z” suffix — the Z-file pattern — such as F4211Z1, F4101Z, or the F47 EDI files. They stage rows moving in or out of JDE: inbound documents awaiting processing, outbound documents awaiting transmission.
These are not the system of record. Their rows are duplicated, partial, or transient, and they clear as processing completes. Treating a Z file as a fact source double-counts or invents transactions — read the base table instead.
-- EDI and interface staging tables carry a trailing "Z" suffix (the Z-file
-- pattern): F4211Z1, F4101Z, F47011, and so on. They hold inbound/outbound
-- rows in transit — duplicated, partial, or not yet processed — and are NOT
-- the system of record. Exclude them from analytics.
--
-- Rule of thumb: if the table name ends in a Z-file suffix, don't treat it as
-- a fact source. Read the base table (F4211), not its interface file.Next numbers (F0002)
Document numbers (DOCO and friends) come from the next-number counters in F0002, kept per system code. When a transaction starts, it reserves the next number and increments the counter.
Because reserved numbers aren't always committed — an order entry is abandoned, a batch rolls back — gaps in a document-number sequence are normal. Never assume DOCO values are contiguous, and never infer a transaction count from the span between the smallest and largest number.
-- JDE assigns document numbers from the F0002 next-number counters, keyed by
-- system code. Numbers are reserved per session and not all get committed, so
-- GAPS in a document-number sequence are normal and expected.
SELECT
nn.NNSY AS system_code,
nn.NNN001 AS next_number_range_1
FROM prodctl.f0002 nn
WHERE nn.NNSY = '42' -- Sales Order Management
-- Never assume DOCO values are contiguous, and never derive counts from a
-- min/max document-number range.