›> ›> Build a SQL Query for JDE's Data Dictionary (F9210)

Build a SQL Query for JDE's Data Dictionary (F9210)

28th January 2014

The SQL statement is a precursor to refreshing the EXTOL JDE Application Schemas, i.e. F47011, F47036. The purpose of the statement is to use several JDE database tables and ultimately query the data dictionary (F9210) to determine which numeric fields have decimals associated with them.

SELECT TDOBNM,
(Select SIMD From “OL910”.“F9860”
Where SIOBNM = TDOBNM
AND SIFUNO = ‘TBLE’)
Table_Description,
TDSQLC,
(Select FRDSCR From “DD910”.“F9202”
Where FRDTAI = TDOBND
AND FRSYR = ’ ’)
Column_Description,
(Select DRDL01 From “DD910”.“F9210”, “CRPCTL”.“F0005”
Where FRDTAI = TDOBND
AND LTRIM(RTRIM(FROWTP)) = LTRIM(RTRIM(DRKY))
AND DRSY = 'H98’ AND DRRT = 'DT’)
Column_Type,
(Select FRDTAS From “DD910”.“F9210”
Where FRDTAI = TDOBND)
Column_Length,
(Select FROWDI From “DD910”.“F9210”
Where FRDTAI = TDOBND)
DD_Item,
(Select FROWER From “DD910”.“F9210”
Where FRDTAI = TDOBND)
Edit_Rule,
(Select FROER1 From “DD910”.“F9210”
Where FRDTAI = TDOBND)
Edit_Rule_Parm1,
(Select FROER2 From “DD910”.“F9210”
Where FRDTAI = TDOBND)
Edit_Rule_Parm2,
(Select FRcdec From “DD910”.“F9210”
Where FRDTAI = TDOBND)
Decimal_Pos,
TDPSEQ
FROM “PY910”.“F98711”
WHERE TDOBNM = 'F47011’
ORDER BY TDOBNM, TDPSEQ

Action Items needed to utilize this statement:

  • The library/environment names need to be updated to reflect the values for the Users’ system. For example 'DD910’, 'CRPCTL’, 'PY901’, 'OL910’.
  • The second to last line should reflect the JDE table that users are referencing. For example: 'F47011’.

Additional information on JDE/EXTOL set-ups and how-to’s


By: on