• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

database structure - listing

is there any documentation which contains a complete table listing with feild names and descriptions and relationships ?
thanks
JDE - 8.12 SQL
 
jparikh

Firstly, welcome to JDEList.

Secondly the information you request is available from the JDE database. Some time ago I created some Oracle SQL queries that extract the information you require. They work on our system which is Xe using an Oracle database. You may need to adjust them to suit your config. I have attached them. I hope they help.
 

Attachments

Whilst it is possible to list the table structures you should bear in mind that a typical environment will use over 2500 tables so you'll need a lot of paper in that printer.

Really you need to get familiar with looking at the structures on screen, either from the underlying database tools or through OMW.
 
Hello, you have the queries, to put together a menu with excel? as it comes in P01rs01?

We would be very grateful

regards
Alejandro.
 
Alejandro,

I do not know what you mean by:

[ QUOTE ]
Hello, you have the queries, to put together a menu with excel? as it comes in P01rs01?


[/ QUOTE ]

The SQL queries I posted in this thread were for table structures, not menus.

I have no idea what "P01rs01" is, though it is possible I may have forgotten.
 
Hello - Does JDE have mapping of fields from user screen to JDE table? Also, I was wondering if there is a way to extract data dictionary via SQL.
Thank you in advance for your help.
 
1. Q: Does JDE have mapping of fields from user screen to JDE table?
A: Yes - inside the programs / code

2. Q: Is there is a way to extract data dictionary via SQL
A: Yes. Here's one way (9.1 Oracle PL/SQL)
--------------------------------------
ACCEPT cTablename CHAR DEFAULT * PROMPT 'Enter the Table Name to generate a view for (FXXXXX): '
ACCEPT cOwner CHAR DEFAULT PRODDTA PROMPT 'Enter the Production Environment Table Owner: '
select dtc.column_name, replace(NVL(d.frowdi,dtc.column_name),' ','') JDE_NAME,
d.frcdec Decimal_Places, d.frowtp JDE_Data_Type, d2.frdsca Description
from dba_tab_columns dtc,
DV910.f98711 c, DD910.f9210 d, DD910.f9203 d2
where dtc.owner = '&cOwner'
and dtc.table_name = '&cTablename'
and dtc.column_name=replace(RTRIM(UPPER(c.tdsqlc)),'@','_1')
and c.tdobnm = '&cTablename'
and RTRIM(SUBSTR(c.tdsqlc,3)) = RTRIM(d.frdtai)
and d.frdtai = d2.frdtai and d2.frsyr = ' ' and d2.frscrn = ' '
order by dtc.owner, dtc.table_name, column_id;
--------------------------------------
Instead of querying the database for the table structure you can get that from PD910.F98711, then join DD910.F9210 (on OBND to FRDTAI) and DD910.F9203 (on OBND to FRDTAI and FRSCRN = ' ' and FRSYR = ' '). It will execute much faster that way but I already had the above handy.
 
Top