Advanced ways to select data with SQL from OpenEdge

Advanced ways to select data with SQL from OpenEdge

We covered Select data with SQL from OpenEdge in a previous post. In this post we will review how JOINs, EXISTSs etc. are available to us when extracting data from OpenEdge with SQL. Let’s assume that you have a client such as DBeaver set up. Connect to your OpenEdge database using ODBC. In my examples, I am using Sports2000 database. We will run through multiple queries. ABL users can compare equivalent FOR-EACH statements.

Select data from openedge

Individual queries

Let’s review different departments.

Great! Now, let’s review data for just department 100. This is Consulting department

I wonder how many employees there are in Consulting department.

If I didn’t remember the department code and remembered just the department name, how would I get the number of employees in that department?

It is possible that deptname can have duplicate records. Therefore, we use the IN keyword instead of = equality comparison. Notice the query below that uses the IN keyword.

In the above query, we also used the EXISTS keyword to get count of only those employees that have a matching department code whose name is Consulting.

Grouping data to gather statistics

There are times when we have to get maximum, minimum, average, count and other statistics. There are grouping functions available in OpenEdge that can be used with SQL. Let’s take some examples.

Let’s list out all department codes and the number of employees in each department.

This is great. However, it is possible that there may be departments in which there are no employees. How shall we display that information? Here’s how:

Notice that we are asking for all records from department and matching employee records using LEFT JOIN. Then, we ask that information get grouped by deptcode of department (we’ve given an alias of d to department table). Then, we ask that empnum get counted for each of the deptcode.

Let’s show counts for only those departments that have 10 or more employees.

How many orders do we have that are shipped, partially shipped or ordered?

Alright, it appears that around 3026 items are ordered, 1 is partially shipped and 926 are shipped.

Grouping, joining and limiting data

Let’s find the top 10 customers spending the most regardless of their order status. We can review this data from a couple of angles. Let’s see, first, which customer number is the biggest spender.

It’s completely possible that a big customer like Walmart has many customer numbers – one for each of their location. Let’s see if the data is more meaningful when grouped by customer’s name.

Aha! That makes sense. Champs Sports has ordered $1.4 MM worth of goods.

Dissecting data for correctness

Now, let’s see how many customer numbers Champs Sports has.

You’ll notice that none of the top 10 customer numbers are that of Champs Sports. That means, it might be valuable to see which combination of customer name and number comes up on top.

Excellent. Now we know which customer number and name combination has ordered the most.

There are numerous combinations of JOINs, DISTINCTs, etc. we can write with SQL and OpenEdge. This is just a glimpse of how you could do advanced SELECTs on OpenEdge databases.

Be sure to see other topics listed on Modern web applications and API with OpenEdge, SQL, ODBC and PHP