Views for Query/400!

Hey, Howard:

I have a new challenge. I have a table with suppliers and parts, and more than one supplier may provide the same part number, as well as others. So from the supplier's point, it's a one-to-many relationship. I need to determine how to select only those parts provided by more than one supplier, in SQL or Query/400, if possible. I've tried to do this several ways, including joining a file to itself, but I don't end up with the way I'd like to have it presented to me, for easy analysis.

--Nelson

As the governator would say: "No problemo!" What you want to do is create a query with a count. Let's create two example tables and then perform the query to see how it operates. Execute the following SQL statements to create the sample data, using STRSQL or my SQLThing tool:

/*create table for part information*/
CREATE TABLE SQLBOOK.PARTS
(	PARTID CHAR(10) NOT NULL PRIMARY KEY,
        UM CHAR(2) NOT NULL,
        PRICE DECIMAL(10,2),
        DESCRIPTION CHAR(30) NOT NULL
       );
 
/*fill parts with data*/

INSERT INTO SQLBOOK.PARTS VALUES ('AK113','EA',12.42,'10X10X2 FILTER');
INSERT INTO SQLBOOK.PARTS VALUES ('AK114','EA',14.45,'20X20X2 FILTER');
INSERT INTO SQLBOOK.PARTS VALUES ('AK115','EA',15.95,'20X10X2 FILTER');
INSERT INTO SQLBOOK.PARTS VALUES ('PP912','EA',1.99,'KTX GREASE');
INSERT INTO SQLBOOK.PARTS VALUES ('PP913','EA',6.54,'COMPRESSOR OIL');

/*create table for parts vendor relationship*/

CREATE TABLE SQLBOOK.PART_VEND
(	PARTID CHAR(10) NOT NULL,
	VENDOR CHAR(10) NOT NULL,
	V_PRICE DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (PARTID,VENDOR)
       );

/*fill the file with test data */

INSERT INTO SQLBOOK.PART_VEND VALUES ('AK113','ACME',6.54);
INSERT INTO SQLBOOK.PART_VEND VALUES ('AK114','ACME',6.23);
INSERT INTO SQLBOOK.PART_VEND VALUES ('AK115','ACME',9.33);
INSERT INTO SQLBOOK.PART_VEND VALUES ('PP912','ACME',0.51);
INSERT INTO SQLBOOK.PART_VEND VALUES ('PP913','ACME',0.91);
INSERT INTO SQLBOOK.PART_VEND VALUES ('AK113','BROWN',8.14);
INSERT INTO SQLBOOK.PART_VEND VALUES ('AK114','BROWN',4.42);
INSERT INTO SQLBOOK.PART_VEND VALUES ('AK113','DEPAUL',4.41);
INSERT INTO SQLBOOK.PART_VEND VALUES ('PP913','DEPAUL',1.12);

Now that we have some test data, let's do a simple query to return the vendors that supply more than one part:

SELECT PARTID, COUNT(*) AS VENDOR_COUNT
	FROM SQLBOOK.PART_VEND
	GROUP BY PARTID
	HAVING COUNT(*)>1;

Because the query groups by the PARTID column, the COUNT function represents the number of times a give PARTID value is in the file. By adding the HAVING clause, we are restricting the result set to items that have a count of greater than 1 (that is, all parts that more than one vendor supplies). The results of this query are shown in the following table.

Notice that AK113 is available from three different vendors, while AK114 and PP913 are available from two vendors. Parts AK115 and PP912 are not in the result set, because they are available from only one vendor.

To use this information in a report, you can construct several SQL statements that will use the results of this query. Since there is almost always more than one way to do a query, I will offer several versions of the same statement. Here is one way to use the statement to produce a listing, using a join to a nested table expression, (the nested table expression is colored in blue to make it easier to identify):

SELECT A.PARTID, DESCRIPTION, UM, PRICE
FROM SQLBOOK.PARTS A INNER JOIN
	(SELECT PARTID,COUNT(*) AS VENDOR_COUNT
		FROM SQLBOOK.PART_VEND
		GROUP BY PARTID
		HAVING COUNT(*)>1) AS X
	ON (A.PARTID = X.PARTID)

The above produces the following output:

Here's another way to ask the same question, using a common table expression (the common table expression is colored in blue to make it easier to identify):

WITH X AS
(SELECT PARTID,COUNT(*) AS VENDOR_COUNT
		FROM SQLBOOK.PART_VEND
		GROUP BY PARTID
		HAVING COUNT(*)>1	)
SELECT A.PARTID, DESCRIPTION, UM, PRICE
	FROM SQLBOOK.PARTS A INNER JOIN X ON (A.PARTID = X.PARTID)

How about using "exists":

SELECT PARTID, DESCRIPTION, UM, PRICE
FROM SQLBOOK.PARTS A
WHERE EXISTS (SELECT PARTID,COUNT(*) AS VENDOR_COUNT
		FROM SQLBOOK.PART_VEND B
		WHERE A.PARTID = B.PARTID
		GROUP BY PARTID
		HAVING COUNT(*)>1)

All three of these statements produce identical output, but the third version uses a correlated subquery, which can be quite expensive. A correlated subquery is a subquery that contains a reference to an outer query (in the example, the subquery contains a reference to A.PARTID, which comes from the outer select). A correlated subquery usually requires that the subquery be fired for each execution of the statement (so, for each record in PARTS, we run the subquery to determine if that part has more than one vendor.

Now, some of you might write in and say that the first statement is also a subquery and needs to be fired for each row. This is not true, as the first statement is referred to as a nested table expression. The iSeries will generate the result set and then perform the join, just like using the common table expression. The first example and second example have identical execution plans.

Using This Technique in Query/400

How do you translate this to Query/400? You can use a view to make the query results available to Query. First, issue the following statement to create a view:

CREATE VIEW SQLBOOK.MT1VENDOR AS 	
       SELECT PARTID,COUNT(*) AS VEND_COUNT
		FROM SQLBOOK.PART_VEND
		GROUP BY PARTID
		HAVING COUNT(*)>1;

You now have a virtual table called MT1VENDOR in the SQLBOOK library. Remember, views do not really take up any space; they just appear to be logical files to the system, and the records are retrieved when the file is accessed. You can now start Query/400 and select this file, join it to the parts file, and create a report. When the Query/400 report runs, Query/400 will access the view, causing the SQL statement to run and instantiate the table for the duration of report processing. The report will list only parts that have more than one supplier.

Views are a neat way to incorporate the power of SQL into the old standby Query/400.

Howard F. Arner, Jr., is the author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities, and Import Expert 400, a program for quickly importing data from almost any data source to the iSeries. You can purchase a copy of Howard's book or learn more about his products at www.sqlthing.com. Send your SQL questions to harner@sqlthing.com.

Sponsored By INFORM DECISIONS

ELIMINATE THE COSTS OF PRE-PRINTED FORMS, LABOR AND POSTAGE WITH inFORM Decisions iDocs™ 'Suite' iSeries based e-Forms, e-Checks, e-Mail, e-FAX, and Document Retrieval from the Web are available as individual modules or as a complete e-Document processing 'Suite'. Click to Download the Complete Suite or Individual Modules today www.inFORMDecisions.com or call (800) 858-5544

---

Editors: Howard Arner, Joe Hertvik, Ted Holt, David Morris

Managing Editor: Shannon Pastore

Publisher and Advertising Director: Jenny Thomas

Advertising Sales Representative: Kim Reed

Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

---