October 26, 2011
(Back to the Previous Post in the Series)
In the previous article of this series we examined a couple of moderately simple data retrieval requirements, and used inline views and/or analytic functions to solve those data retrieval requirements. I recently saw another opportunity to help a person on the ERP mailing list with a SQL statement. The original poster (OP) provided a screen capture that contained information similar to the following:
CUST_ORDER_ID PART_ID TYPE CLASS WAREHOUSE_ID LOCATION_ID QTY_ON_HAND TRANSACTI --------------- --------------- ---- ----- ------------ ------------ ----------- --------- ZQHZG 2J-2908 O I FIB AB7A10 30 21-JUN-10 ZQHZZ 2J-2909 O I FIB AB7A10 1 21-JUN-10 ZIHKR 2J0836 O I HIBB A77Y3 1092 30-NOV-07 ZQKZH 2J0836 O I HIBB A77Y3 1092 10-JUN-08 ZQIHZ 2J0836 O I HIBB A77Y3 1092 23-NOV-09 ZKOHI 2J4120 O I PIPABTONA AB7A10 2 16-NOV-95 ZKOHQ 2J4129 O I PIPABTONA AB7A10 2 16-NOV-95 ZKGQG 2J9785 O I HIBB AB7A10 2 25-SEP-95 ZKGQG 2J9785 O I HIBB AB7A10 2 25-SEP-95 ZKGQG 2J9785 O I HIBB AB7A10 2 26-OCT-95 ZKGQG 2J9785 O I HIBB AB7A10 2 26-OCT-95 ZKGQG 2J9785 O I HIBB AB7A10 2 12-DEC-95 ZKGQG 2J9785 O I HIBB AB7A10 2 12-DEC-95 ZKGQG 2J9785 O I HIBB AB7A10 2 27-FEB-96 ZKGQG 2J9785 O I HIBB AB7A10 2 04-APR-96 ZKGQG 2J9785 O I HIBB AB7A10 2 04-APR-96 ZKGQG 2J9785 O I HIBB AB7A10 2 31-MAY-96 ZKGQG 2J9785 O I HIBB AB7A10 2 31-MAY-96 ZKGQG 2J9785 O I HIBB AB7A10 2 06-SEP-96 ZKGQG 2J9785 O I HIBB AB7A10 2 30-SEP-96 ZKGQG 2J9785 O I HIBB AB7A10 2 30-SEP-96 ZKGQG 2J9785 O I HIBB AB7A10 2 30-SEP-96 ZKGQG 2J9785 O I HIBB AB7A10 2 12-JAN-98 ZKGQG 2J9785 O I HIBB AB7A10 2 12-FEB-98 ZKGQG 2J9785 O I HIBB AB7A10 2 04-MAR-98 ZKGQG 2J9785 O I HIBB AB7A10 2 05-MAY-98 ZKGQG 2J9785 O I HIBB AB7A10 2 05-MAY-98 ZKGQG 2J9785 O I HIBB AB7A10 2 18-AUG-98 ZKGQG 2J9785 O I HIBB AB7A10 2 18-AUG-98 ZKGQG 2J9785 O I HIBB AB7A10 2 29-SEP-98 ZKGQG 2J9785 O I HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 O I HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 O I HIBB AB7A10 2 02-DEC-98 ZIRZQ 3J8547 O I HIBB A77Y2 252 18-OCT-07 ZZUQG 4G0007 O I HIBB AB7A10 2 08-NOV-99 ZZUQG 4G0007 O I HIBB AB7A10 2 30-NOV-99 ...
Initially, the OP requested that the most recent TRANSACTION_DATE (the last column in the above output) for the PART_ID value to be shown on each row of the output, rather than the TRANSACTION_DATE that is directly associated with a specific row. However, there are two problems:
- The OP did not mention what tables were used as the data source for the included screen capture.
- The OP did not mention whether a recent release of Oracle Database or some other brand of database was in use.
For times like these, it helps a lot to be familiar with the data model used in the database. The INVENTORY_TRANS table in this particular ERP system has columns that match several of the columns that were included in the OP’s screen capture: CUST_ORDER_ID, PART_ID, TYPE, CLASS, WAREHOUSE_ID, LOCATION_ID, and TRANSACTION_DATE. The question remains, where did the QTY_ON_HAND column come from – that column is not in the INVENTORY_TRANS table. We could find out, at least on Oracle Database, with a SQL statement similar to the following:
SELECT DTC.TABLE_NAME, DT.TABLE_NAME FROM DBA_TAB_COLUMNS DTC, DBA_TABLES DT WHERE DTC.COLUMN_NAME='QTY_ON_HAND' AND DTC.OWNER='SCHEMA_OWNER_HERE' AND DTC.OWNER=DT.OWNER(+) AND DTC.TABLE_NAME=DT.TABLE_NAME(+) ORDER BY DTC.TABLE_NAME; TABLE_NAME TABLE_NAME ------------------------------ ---------- CR_EC_LINE_PART CR_OM_CO_LINE_PART CR_PART_PO_LINE CR_WO_PART PART PART PART_USER_DEFINED_VIEW
Note that in the above, I had to join the DBA_TAB_COLUMNS dictionary view with the DBA_TABLES dictionary view (I could have used DBA_OBJECTS instead) to determine that 5 of the 6 rows returned from the DBA_TAB_COLUMNS view are in fact not tables, but views. Thus, the QTY_ON_HAND column in the OP’s screen capture must have originated from the PART table (unless, of course, the column in the screen capture is actually an aliased calculation).
Now that the data sources are known for the query, we can take a best guess approach at reproducing the output that the OP provided in the screen capture:
SELECT IT.CUST_ORDER_ID, IT.PART_ID, TYPE, CLASS, IT.WAREHOUSE_ID, IT.LOCATION_ID, P.QTY_ON_HAND, IT.TRANSACTION_DATE FROM INVENTORY_TRANS IT, PART P WHERE IT.CUST_ORDER_ID IS NOT NULL AND IT.TYPE='O' AND IT.CLASS='I' AND IT.PART_ID=P.ID AND P.QTY_ON_HAND>0 ORDER BY IT.PART_ID, IT.TRANSACTION_DATE;
Next, there are a couple of ways to have the most recent transaction date appear on each row of the output (partially dependent on the database version). First, let’s build a query that returns the most recent transaction date where the TYPE is O and the CLASS is I and the CUST_ORDER_ID column does not contain a NULL:
SELECT PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE FROM INVENTORY_TRANS WHERE TYPE='O' AND CLASS='I' AND CUST_ORDER_ID IS NOT NULL GROUP BY PART_ID ORDER BY PART_ID; PART_ID LAST_TRAN --------------- --------- 0000009 29-DEC-00 005035008005 29-MAY-98 005035008006 29-MAY-98 00576649-0080 20-OCT-11 00576649-0088 08-JUN-11 00576649-8005 14-JAN-11 007580398 17-JAN-03 0099229940 01-NOV-95 0099229990 01-NOV-95 0108556 28-APR-00 01550867KM 15-NOV-05 01552316KM 02-OCT-00 01552346KM 30-APR-03 01552369KM 30-APR-01 01558943M 07-JAN-10 01561230KM 30-OCT-01 01563001M 08-MAR-10 01563882M 01-APR-10 01566790KM 08-SEP-08 01569945M 01-APR-10 01583508TO 11-FEB-05 01780151TO 19-JUL-07 ...
Next, we will just plug the above inline view into the FROM clause of our original SQL statement and add an additional predicate into the WHERE clause to tell the query optimizer how to associate the inline view with the other tables in the SQL statement:
SELECT IT.CUST_ORDER_ID, IT.PART_ID, IT.QTY, IT.WAREHOUSE_ID, IT.LOCATION_ID, P.QTY_ON_HAND, MIT.LAST_TRANSACTION_DATE FROM INVENTORY_TRANS IT, PART P, (SELECT PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE FROM INVENTORY_TRANS WHERE TYPE='O' AND CLASS='I' AND CUST_ORDER_ID IS NOT NULL GROUP BY PART_ID) MIT WHERE IT.CUST_ORDER_ID IS NOT NULL AND IT.TYPE='O' AND IT.CLASS='I' AND IT.PART_ID=P.ID AND P.QTY_ON_HAND>0 AND IT.PART_ID=MIT.PART_ID ORDER BY IT.PART_ID, IT.CUST_ORDER_ID, IT.TRANSACTION_DATE; CUST_ORDER_ID PART_ID QTY WAREHOUSE_ID LOCATION_ID QTY_ON_HAND LAST_TRAN --------------- ------------ ---------- ------------ ------------ ----------- --------- ZQHZG 2J-2908 1 FIB AB7A10 30 21-JUN-10 ZQHZZ 2J-2909 1 FIB AB7A10 1 21-JUN-10 ZIHKR 2J0836 48 HIBB A77Y3 1092 23-NOV-09 ZQIHZ 2J0836 72 HIBB A77Y3 1092 23-NOV-09 ZQKZH 2J0836 111 HIBB A77Y3 1092 23-NOV-09 ZKOHI 2J4120 2 PIPABTONA AB7A10 2 16-NOV-95 ZKOHQ 2J4129 2 PIPABTONA AB7A10 2 16-NOV-95 ZKGQG 2J9785 9 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 1 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 7 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 2 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 6 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 4 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 10 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 8 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 4 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 10 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 4 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 12 HIBB AB7A10 2 02-DEC-98 ...
Since I am using Oracle Database 11.2.0.2, I can take advantage of analytic functions to further simplify the SQL statement:
SELECT IT.CUST_ORDER_ID, IT.PART_ID, IT.QTY, IT.WAREHOUSE_ID, IT.LOCATION_ID, P.QTY_ON_HAND, MAX(IT.TRANSACTION_DATE) OVER (PARTITION BY IT.PART_ID) AS LAST_TRANSACTION_DATE FROM INVENTORY_TRANS IT, PART P WHERE IT.CUST_ORDER_ID IS NOT NULL AND IT.TYPE='O' AND IT.CLASS='I' AND IT.PART_ID=P.ID AND P.QTY_ON_HAND>0 ORDER BY IT.PART_ID, IT.CUST_ORDER_ID, IT.TRANSACTION_DATE; CUST_ORDER_ID PART_ID QTY WAREHOUSE_ID LOCATION_ID QTY_ON_HAND LAST_TRAN --------------- ------------ ---------- ------------ ------------ ----------- --------- ZQHZG 2J-2908 1 FIB AB7A10 30 21-JUN-10 ZQHZZ 2J-2909 1 FIB AB7A10 1 21-JUN-10 ZIHKR 2J0836 48 HIBB A77Y3 1092 23-NOV-09 ZQKZH 2J0836 111 HIBB A77Y3 1092 23-NOV-09 ZQIHZ 2J0836 72 HIBB A77Y3 1092 23-NOV-09 ZKOHI 2J4120 2 PIPABTONA AB7A10 2 16-NOV-95 ZKOHQ 2J4129 2 PIPABTONA AB7A10 2 16-NOV-95 ZKGQG 2J9785 9 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 1 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 7 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 2 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 6 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 4 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 10 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 8 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 4 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 10 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 4 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 12 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 2 HIBB AB7A10 2 02-DEC-98 ZKGQG 2J9785 11 HIBB AB7A10 2 02-DEC-98 ...
—–
Let’s assume that the OP’s intended result changes – the OP just wants to know which PART_IDs have not had any transactions in the last 89 day. So, the OP is interested in those PART_IDs with a last transaction that was either 90 or more days ago, or there has never been a transaction for the PART_ID. Oh, scrap… start over.
First, we will identify the list of PART_IDs that have had transactions in the last 89 days (CLASS values of ‘A’ are adjustments, not true inventory transactions, so rows with those CLASS values will be excluded). Once we have that list, we can simply subtract the list from the entire list of PART_IDs:
SELECT DISTINCT PART_ID FROM INVENTORY_TRANS WHERE TRANSACTION_DATE > TRUNC(SYSDATE-90) AND PART_ID IS NOT NULL AND CLASS<>'A'; PART_ID --------- 2J-2908 9F4810 867-8649 90772357L 90772341L 90772297L 8475047 8145432 90772326L 9340730 90772357 90772347 782-9484 ...
The OP is interested in seeing the actual warehouse location containing the PART_IDs, so we need to use the PART_LOCATION table, not the PART table as we did before. We then need to create a left outer join between the above list and the PART_LOCATION table – essentially, we will find cases where there is a row in the PART_LOCATION table without a corresponding part ID in the above list. ANSI syntax will be used here for the outer join, because it is still unknown whether the OP is running an Oracle Database backend or a SQL Server backend:
SELECT PL.PART_ID, PL.WAREHOUSE_ID, PL.LOCATION_ID, IT.PART_ID, PL.QTY FROM PART_LOCATION PL LEFT OUTER JOIN (SELECT DISTINCT PART_ID FROM INVENTORY_TRANS WHERE TRANSACTION_DATE > TRUNC(SYSDATE-90) AND PART_ID IS NOT NULL AND CLASS<>'A') IT ON PL.PART_ID=IT.PART_ID AND IT.PART_ID IS NULL WHERE PL.QTY<>0 ORDER BY PL.PART_ID, PL.WAREHOUSE_ID, PL.LOCATION_ID; PART_ID WAREHOUSE_ID LOCATION_ID PART_ID QTY ---------------------- ------------ ------------ ------------ ---------- #2 HIBB AB7A10 4 #46954 HIBB TOOL CRAB 15 #4HWG LHRK GROUND WIRK HIBB TOOL CRAB 250 #4THHN HIBB AB7A10 2460 #5 HIBB AB7A10 3 #6 HIBB TOOL CRAB 4 #649 HIBB TOOL CRAB 2 #655 HIBB TOOL CRAB 4 #709 HIBB TOOL CRAB 2 #889 HIBB TOOL CRAB 1 #89 KJTKNSION HIBB TOOL CRAB 3 #90 HIBB AB7A10 4 #92 KJTKNSION HIBB TOOL CRAB 2 *G-WM370H-MT HIBB AB7A10 2 .235-H522 BURN AB7A10 .5 .235RD-9K0045 BURN AB7A10 .7 .260RDJ.963-HSTMH57 BURN AB7A10 2.5625 .35-H596-845 PIPABTONA AB7A10 4.039 ...
In the above, notice that there is a seeminly odd ON clause: “PL.PART_ID=IT.PART_ID AND IT.PART_ID IS NULL” – that simply means that when resolving the left outer join, a value must be found on the left side, but a corresponding value should not be found on the right side of the join. This is the “minus” syntax that I selected to use here (the SQL MINUS syntax could not be used because the WAREHOUSE_ID, LOCATION_ID, and QTY column values returned by the query of the INVENTORY_TRANS table would have to exactly match the values found in the PART_LOCATION table… and that is more difficult to accomplish).
If I was certain that the OP was running with an Oracle Database backend, I would have suggested the following syntax, rather than the ANSI syntax as shown above:
SELECT PL.PART_ID, PL.WAREHOUSE_ID, PL.LOCATION_ID, IT.PART_ID, PL.QTY FROM PART_LOCATION PL, (SELECT DISTINCT PART_ID FROM INVENTORY_TRANS WHERE TRANSACTION_DATE > TRUNC(SYSDATE-90) AND PART_ID IS NOT NULL AND CLASS<>'A') IT WHERE PL.PART_ID=IT.PART_ID(+) AND IT.PART_ID IS NULL AND PL.QTY<>0 ORDER BY PL.PART_ID, PL.WAREHOUSE_ID, PL.LOCATION_ID;
Why the NULL values in the fourth column (the second PART_ID column) of the output? That NULL simply means that the query is working exactly as intended. That fourth column would be showing only those PART_IDs that have had transactions in the last 89 days (and NULL if no transactions in the last 89 days) if the restriction “AND IT.PART_ID IS NULL” was NOT included in the ON clause – but such rows should not be included in the output if the output is to be used as intended.
Charles Hooper's Oracle Notes 
Leave a comment