EnterpriseOne Table List /Column List

New article:

EnterpriseOne Table List With Column Information

http://jeffstevenson.karamazovgroup.com/2011/03/enterpriseone-table-list-with-column.html

I sent Bill the same SQL last week. Oh well.

I specifically stated that I hadn't linked to the UDC table, though the values were in H98|DT. My SQL was based on having built the cross reference - and was as follows. This allows you to get the same info out of Standalone - without having central object specs....

<font class="small">Code:</font><hr /><pre>
select
F980021.sjobnm,
F9860.SIPFX||F980021.sjdtan,
f9202.frdscr,
F980021.sjkysq,
f9210.frowtp,
f9210.frdtas,
f9210.frerul,
f9210.frero1,
f9210.frero2
from jdevl900.f980021 F980021
inner join jdeol900.f9860 f9860 on f980021.sjobnm = f9860.siobnm
inner join jdedd900.f9202 f9202 on f980021.sjdtan = f9202.frdtai
inner join jdedd900.f9210 f9210 on f980021.sjdtan = f9210.frdtai
where
f980021.sjfmt <> 'INDEX'
order by f980021.SJOBNM, F980021.sjkysq
</pre><hr />

[ QUOTE ]
I sent Bill the same SQL last week. Oh well.

I specifically stated that I hadn't linked to the UDC table, though the values were in H98|DT. My SQL was based on having built the cross reference - and was as follows. This allows you to get the same info out of Standalone - without having central object specs....

<font class="small">Code:</font><hr /><pre>
select
F980021.sjobnm,
F9860.SIPFX||F980021.sjdtan,
f9202.frdscr,
F980021.sjkysq,
f9210.frowtp,
f9210.frdtas,
f9210.frerul,
f9210.frero1,
f9210.frero2
from jdevl900.f980021 F980021
inner join jdeol900.f9860 f9860 on f980021.sjobnm = f9860.siobnm
inner join jdedd900.f9202 f9202 on f980021.sjdtan = f9202.frdtai
inner join jdedd900.f9210 f9210 on f980021.sjdtan = f9210.frdtai
where
f980021.sjfmt <> 'INDEX'
order by f980021.SJOBNM, F980021.sjkysq
</pre><hr />

[/ QUOTE ]


Nifty. Mine assumed that XREF was not done. I'm curious why you didn't use the UDC values?

I'm thinking that this would be pretty nifty if we hit the Systables - so we could actually generate everything from the way the Database looks at things in JDE (not just E1).

I'll try to have something formalized for the Tips/Traps presentation at Collaborate - for all three main databases....

(db)

[ QUOTE ]
I'm thinking that this would be pretty nifty if we hit the Systables - so we could actually generate everything from the way the Database looks at things in JDE (not just E1).

I'll try to have something formalized for the Tips/Traps presentation at Collaborate - for all three main databases....

(db)

[/ QUOTE ]

My first attempt used INFORMATION_SCHEMA, sys.columns, etc. I found that while I could get very good information about the columns regarding data types and length, I could not get column or table descriptions.

[ QUOTE ]

Nifty. Mine assumed that XREF was not done. I'm curious why you didn't use the UDC values?

[/ QUOTE ]

Because you have to leave SOMETHING for someone else to think about.

I'm a little peeved about this whole affair - I emailed Bill with my SQL AND the entire Spreadsheet populated EXACTLY as he wanted, and three days later I still don't even get a "thankyou". Took me a couple of hours to create that SQL and spreadsheet. Was even MORE peeved when he spammed linked-in asking for the same information over and over again.

Giving out free information is really starting to bug me.

[ QUOTE ]
[ QUOTE ]

Nifty. Mine assumed that XREF was not done. I'm curious why you didn't use the UDC values?

[/ QUOTE ]


Giving out free information is really starting to bug me.

[/ QUOTE ]

Admit it - you did it for the intellectual challenge.

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]

Nifty. Mine assumed that XREF was not done. I'm curious why you didn't use the UDC values?

[/ QUOTE ]


Giving out free information is really starting to bug me.

[/ QUOTE ]

Admit it - you did it for the intellectual challenge.

[/ QUOTE ]

Well, the next Distribution project I get involved in, he won't be anywhere on my list.

Between the two solutions, though, I'm sure that the UDC's can be populated correctly. I just can't be bothered to waste any more time on that right now....

[ QUOTE ]
[ QUOTE ]
[ QUOTE ]
[ QUOTE ]

Nifty. Mine assumed that XREF was not done. I'm curious why you didn't use the UDC values?

[/ QUOTE ]


Giving out free information is really starting to bug me.

[/ QUOTE ]

Admit it - you did it for the intellectual challenge.

[/ QUOTE ]

Well, the next Distribution project I get involved in, he won't be anywhere on my list.

Between the two solutions, though, I'm sure that the UDC's can be populated correctly. I just can't be bothered to waste any more time on that right now....

[/ QUOTE ]

My script hits the UDC tables and returns the column descriptions. It was actually the part that caused me the most problems until I figured out that F0005.DRKY has leading spaces that made it not join with F9210.FROWTP. That's why I had to add LTRIM in order to execute the join. Grrrr..

I'll throw my hat into the ring too.

I posted a SQL that produced table and column information (Post 98121 - old 109002; Download Attachment to Post 98121 - old 109002).

I also remember an Access database application some time ago as well, that extracted the same information from the JDE database and stored it in an access database and then reported on it from an interactive screen.

[ QUOTE ]
I'll throw my hat into the ring too.

I posted a SQL that produced table and column information (Post 109002; Download Attachment to Post 109002).

I also remember an Access database application some time ago as well, that extracted the same information from the JDE database and stored it in an access database and then reported on it from an interactive screen.

[/ QUOTE ]

So how long did it take you to realize that DRKY has leading spaces or am I the only one who can stare at a set of results for two hours before noticing cute little blanks in front of the value?

Jeff,

[ QUOTE ]
So how long did it take you to realize that DRKY has leading spaces or am I the only one who can stare at a set of results for two hours before noticing cute little blanks in front of the value?

[/ QUOTE ]

I can't remember. I was working with JDE World from 1995 till 1998 and JDE OW/E1 ever since. I think I discovered the left space packing caused by the size specified in the F0004 (DTCDL) (if less than 10) early in the peace with world. But I'm not sure when or how long it took - after all, we are talking 13 to 16 years ago.