Views for Query400!

Howard,

 I have a new challenge. I've tried several ways of doing this including joining a file to itself, but I don't end up with what I'm looking for quite the way I'd like to have it presented to me for easy, relatively speaking, analysis. I have a table with suppliers and parts, 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. ~Nelson

 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);

Ok. 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 only items that have a count of greater than 1. (i.e. all parts which more than one vendor supplies). The results of this query are shown in the following table.

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

Now, to use this information in a report we 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 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:

Another way to ask the same question using a common table expression (common table expression is colored 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 when a subquery 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 is fired for each execution of the statement, i.e. 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 both have identical execution plans.

Using this technique in Query400

Ok, now 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 Query400 report is run, Query400 will access the view causing the SQL statement to be 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 stand by, Query400. I hope this information helps!