Ready to get started?
Have a question on BusinessServices and how to develop or integrate with 3rd Party or want to see a live demo ? We're always happy to help you .
Complex SQL Joins in JD Edwards Business Services
Business Services with Complex Joins
From : ICRUXTECH
This article describes the challenges we face while integrating with JDE Database using Business Services and an alternative approach that we can to go beyond these limitations.
Overview :
Consider the standard JDE approach for Database Integration using Business Services. JDE provides us tools to form a Database call which lets us perform different types of database requests and get results to expose it to the outside world.Do you recall using complex Business views with more than 5 tables in a Database Calls ?
1. Operations:
JDE Database Calls only have few options on the type of operations that can be performed on a JDE Table or Business View:
As you can see, we can only perform Select, Insert, Update or a Delete type of Database Operation using Database Calls. What if we want to do complex SQL Queries ???
2. Single Table Call:
JDE BSSV Database Calls can be performed directly on only a single JDE table. We are not allowed to select more than one table to perform any of the above operations:
For operations with multiple tables, a appropriate existing JDE Business View is used that includes all the required tables. Lets look at the restrictions in using Business Views .
3. JDE Business Views:
JDE has a restriction with views for simple joins you can use 5 tables and for complex joins we can use 3 tables.
Solution
In order to provide a solution to one of our clients who had a unique requirement of collecting data from around seven to eight different JDE Tables along with a combination of different types of Outer Joins as well as Unions, we created complex SQL queries to meet the requirement. These queries are created on the fly and executed on JDE Database using a custom created data source.
Queries also included different input parameters which were dynamically getting added as selection clause to respective JDE Tables.
A sample code snippet is as follows:
For more information on how to use BSSV and what are its components refer the below Oracle document
https://docs.oracle.com/cd/E17984_01/doc.898/e14713/business_services.htm
Why Us?
Services
RESOURCES
Newsletter
Join our mailing list to receive updates from our team
