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.

Individual queries

Let’s review different departments.

SELECT * FROM pub.department;

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

SELECT * FROM pub.department WHERE deptcode = '100';

I wonder how many employees there are in Consulting department.

SELECT COUNT(*) FROM pub.employee where deptcode = '100';

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?

SELECT COUNT(*)
FROM pub.employee 
WHERE deptcode = (SELECT deptcode FROM pub.department WHERE deptname = 'Consulting');

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.

SELECT COUNT(*)
FROM pub.employee 
WHERE deptcode IN (SELECT deptcode FROM pub.department WHERE deptname = 'Consulting');

SELECT COUNT(*)
FROM pub.employee e
WHERE EXISTS (SELECT 1 FROM pub.department WHERE deptname = 'Consulting' AND e.deptcode = deptcode);

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.

SELECT deptcode, count(*)
FROM pub.employee
GROUP BY deptcode;

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:

SELECT d.deptcode, count(empnum)
FROM pub.department d
LEFT JOIN pub.employee e ON e.deptcode = d.deptcode
GROUP BY d.deptcode;

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.

SELECT d.deptcode, count(empnum)
FROM pub.department d
LEFT JOIN pub.employee e ON e.deptcode = d.deptcode
GROUP BY d.deptcode
HAVING count(empnum) >= 10;

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

SELECT orderstatus, COUNT(*)
FROM ORDER
GROUP BY orderstatus;

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.

SELECT o.custnum, sum(extendedprice) AS spending
FROM orderline ol
INNER JOIN ORDER o ON o.ordernum = ol.ordernum
INNER JOIN customer c ON c.custnum = o.custnum
GROUP BY o.custnum
ORDER BY spending DESC
FETCH FIRST 10 ROWS only;

CustNum |SPENDING  |
--------|----------|
1271    |542523.66 |
1335    |520555.23 |
1592    |495662.59 |
1940    |452229.23 |
1607    |389110.62 |
1150    |385087.83 |
1281    |364330.49 |
1421    |349234.83 |
1959    |349197.85 |
1655    |339794.01 |

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.

SELECT c.name, sum(extendedprice) AS spending
FROM orderline ol
INNER JOIN ORDER o ON o.ordernum = ol.ordernum
INNER JOIN customer c ON c.custnum = o.custnum
GROUP BY c.name
ORDER BY spending DESC
FETCH FIRST 10 ROWS only;

Name                  |SPENDING   |
----------------------|-----------|
Champs Sports         |1435715.11 |
Koenig Sporting Goods |553158.92  |
Fort                  |542523.66  |
Fitness Tech          |520555.23  |
All Star Sport Ctr    |495662.59  |
Big 5 Sporting Goods  |485124.04  |
Sports Connection     |480155.94  |
Piedmont Outdoors     |452229.23  |
Play It Again Sports  |451409.64  |
Athletic Attic        |445857.92  |

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.

SELECT custnum, city, state
FROM customer
WHERE name = 'champs sports';

CustNum |City            |State |
--------|----------------|------|
1107    |Bellevue        |WA    |
1267    |Asheville       |NC    |
1349    |Arlington       |VA    |
1435    |Anderson        |SC    |
1546    |Las Vegas       |NV    |
1572    |Albuquerque     |NM    |
1597    |Asbury Park     |NJ    |
1679    |Billings        |MT    |
1808    |Alexandria      |LA    |
1850    |Indianapolis    |IN    |
1871    |Dundee          |IL    |
1951    |Fort Lauderdale |FL    |
2003    |Farmington      |CT    |
2049    |Tucson          |AZ    |
2050    |Tucson          |AZ    |

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.

SELECT o.custnum, c.name, sum(extendedprice) AS spending
FROM orderline ol
INNER JOIN ORDER o ON o.ordernum = ol.ordernum
INNER JOIN customer c ON c.custnum = o.custnum
GROUP BY o.custnum, c.name
ORDER BY spending DESC
FETCH FIRST 10 ROWS only;

CustNum |Name                         |SPENDING  |
--------|-----------------------------|----------|
1271    |Fort                         |542523.66 |
1335    |Fitness Tech                 |520555.23 |
1592    |All Star Sport Ctr           |495662.59 |
1940    |Piedmont Outdoors            |452229.23 |
1607    |Ben's Ski & Sports Ctr       |389110.62 |
1150    |Lain's Sport Ctr & Motel     |385087.83 |
1281    |Sports Connection            |364330.49 |
1421    |Hendrix's Sporting Goods     |349234.83 |
1959    |Duck Creek Gun & Surplus Ctr |349197.85 |
1655    |Goshen Supply Co             |339794.01 |

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