«

»

Jul 13 2010

Using Complex Database Views on EnterpriseOne

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

A Database View is a subset of the database sorted and displayed in a particular way. For example, in an Invoice database, perhaps you only wish to display the Vendors stored in the database.
Database View Diagram
EnterpriseOne Views are similar in nature, where it sits on top on JD Edwards Tables, to retrieve data, or a subset of data.

With Business Views, we can join tables using Left outer, Right outer, Union, Intersect joins. One can usually join up to 5 tables in a Business View if using Simple joins. If one uses a Complex join, then number of tables is limited to 3.

What if you need to use more than 5 tables? What if you need to do some complex joins? And What if you need a column which would give you a Count of records for the group?
For e.g. if you have a need to display the number of items on Open orders and quantities shipped, with the order details, you may have to do the following SQL

SELECT sddoco, sdkcoo, sddcto, count(*), sum(sduopn)
FROM F4211
WHERE sdnxtr <=540
GROUP BY sddoco,sdkcoo,sddcto

To accomplish this in current JD Edwards EnterpriseOne Reporting, you will need to do a little more programming.

We can do this easily via Database Views. We can trick the EnterpriseOne software to use the Database views as E1 tables.

Read Rest of this article at http://www.deepeshmd.com/enterpriseone/using-complex-database-views-on-enterpriseone/

Know more at MOS: [ID 640352.1]

Published under the license.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)
(Visited 433 times, 1 visits today)

About the author

Deepesh M Divakaran

Deepesh MD is more of a Gizmo Geek, and a techie.
A very experienced Technical Consultant on Oracle JD Edwards EnterpriseOne, and OneWorld. As a consultant he has worked for wide range of industries, from Manufacturing, Media, Oil & gas etc. and has worked on re-engineering modules, and areas of customization.
With a touch of class and understanding towards what ever he does, he is often regarded by his colleagues as the one stop answer for all Technical/Software related queries.

Leave a Reply