Pages

Tuesday, June 7, 2011

JDEdwards -=- How to search JDE EnterpriseOne Data Dictionary from SQL

The Issue

The JD Edwards EnterpriseOne database does not contain any table or field name aliases with meaningful English descriptions. This lack of descriptions creates an issue for application and software developers in accessing the Table File Definitions and Field Descriptions contained in the JDE Data Dictionary. Without access to the table descriptions and the field descriptions, developers are not able to easily navigate the data needed for developing applications or reports.
Normally there are only two ways to get this information; both native methods of accessing table and field description information are inside the JD Edwards application. This requires that developers be trained in JDE to access the information they need. The first native method of access is to log into the JDE Fat Client and search through the Data Dictionary for the specific field aliases and/or table names. The second method is to pull up the Table Browser inside the EnterpriseOne Web Application, or Fat Client, and browse the data table, toggling between the field names and the field descriptions.
Most developers prefer to stay inside of SQL, or their chosen Integrated Development Environment (IDE), while maintaining access to all the information necessary to identify JDE fields and tables.  Additionally, the ability to see field lengths and data, to search for specific field aliases throughout JDE, and to to perform wild card searches for field descriptions, aliases, data types, and other information in the Data Dictionary, from the development environment, is a huge time saver.

The Solution

I have developed a simple table function, called TFD (Table File Descriptions), which allows developers to search and display table file descriptions and JDE Data Dictionary information with a SQL command inside of a query window of your chosen IDE. How it works is simple. You just type a select statement and use the TFD() function in the “From Clause”. The TFD() function will return a table result based on the table name passed in, and any additional “Where Clause” constraints.

Examples

A basic query using the TFD() function will result in a standard Table File Description result set as follows:
The statement:
select * from tfd('F4211')
Produce the following results:
TFD_Example_1

A more complex SQL Statement such as this:
Select
distinct [JDE Table], [Table Name], [JDE Field], [Item Description] 
from TFD('%')
where alias = 'UOM4'
order by [JDE Table]

Will yield the following results:
TFD_Example_2
Notice that I used the wildcard character ‘%’ (percent) to show all tables where the Pricing Unit of Measure (UOM4) is used.
You can use any combination of wild cards and select statements with the TFD() function. You can also search on any field in the result set. If, for example you wanted to find all the fields in the JDE EDI tables (‘F47%’) containing the word ‘%Partner%’ in it, you could write a query as follows:
select [JDE Table], [Table Name], [JDE Field], [Item Description] 
from tfd('F47%')
where [Item Description] like '%Partner%'
The result would be:
TFD_Example_3

As you can see from these few examples, the TFD() function can save time in development by making JDE object names and descriptions visible to the SQL, VB, C#, or Java integrated development environments. Developers can save time finding which fields or tables contain the data they need, and begin making sense of the cryptic JDE alias names.

The code

Simply copy the following code and execute it in the IDE of your choice. I used SQL Management Studio to develop and create the function. Once the function has been created in the JDE database, it’ll be ready to use.
Note: you’ll have to change the schema names and database names to match your JDE EnterpriseOne environment.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOCREATE FUNCTION [dbo].[tfd] (@tbl varchar(99))
RETURNS TABLE
AS
-- =============================================
-- Written by Bryant Avey, InterNuntius, Inc.
-- Provided free "As Is" with no warranties or guarantees
-- I just ask that you keep these comments in the function, if you use it.
-- The complete article describing this function can be found at: 
-- http://wp.me/pBPqA-F

-- Description: Retrieve Table and Field Descriptions for any JD Edwards Table
-- Use: execute this function by passing in the table name as a parameter:
-- select * from tfd('F47012')
-- you can also use it to find where fields are used:
-- select * from tfd('%') where alias = 'UOM4'
-- =============================================
RETURN (       
-- Get the table field definition data
        select
        sys.schemas.name "JDE Schema",
        sysobjects.name "JDE Table", simd "Table Name",
        colorder "Field Sequence",
        syscolumns.name "JDE Field",
        f9210.frdtai Alias,
        frclas "Data Class",
        frdtat "Data Type",
        frdtas Size,
        frdtad "Decimals Stored",
        frcdec "Decimals Displayed",
        frowdi "Item Description",
      --frdsca "Dictionary Description",
        length SQLlength,
        prec SQLprecision,
        isnullable SQLnull
        from sysobjects
        join syscolumns on sysobjects.id = syscolumns.id
        join jde812.dd812.f9210 on substring(syscolumns.name,3,99)
                                 = f9210.frdtai
        join jde812.ol812.f9860 on sysobjects.name = siobnm
        join sys.schemas on sysobjects.uid = sys.schemas.schema_id
        where sysobjects.name like @tbl and sysobjects.xtype = 'U'
        )
 



Please see original article at : http://bryantavey.com/2009/08/17/how-to-search-jde-enterpriseone-data-dictionary-from-sql/

2 comments:

  1. There's a striking resemblance with this other blog post from 2009 :
    http://bryantavey.com/2009/08/17/how-to-search-jde-enterpriseone-data-dictionary-from-sql/ ...

    Now who came first ?

    ReplyDelete
  2. Yes it's a copy of this blog. That's why I give the credit to the other blog at the end of the article.

    ReplyDelete