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
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.
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
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
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