October 21, 2011
(Forward to the Next Post in the Series)
Sometimes it is interesting to take a step back from some of the more difficult to construct SQL statements, and just put together something simple. The following request recently arrived through an ERP mailing list:
“I want to find the last ship date for all the open part numbers in Customer Order Entry. The last ship date is not necessarily from the Open order in Customer Order Entry…”
The original poster (OP) provided more information, and even provided a description of what was already tried as a solution. It is not exceptionally clear what the OP wanted, but because I have several years of experience working with this particular ERP system, I had a feeling that the OP might be headed in the wrong direction. This ERP system uses tables that are well normalized, which would make it a bit more difficult to locate the most recent ship date for a part number that is recorded with one customer order’s tables’ rows when another customer order’s tables’ rows are reported. Unless, of course, we remember that there is also a transaction table that records every inventory movement of part numbers through the system – we just need to know what those rows in the transaction table look like. In this case, the rows have the following characteristics:
- The CUST_ORDER_ID column value must not be NULL
- The TYPE column must have a value of ‘O’
- The CLASS column must have a value of ‘I’
With that knowledge, we could then find the most recent date that any part number shipped with a SQL statement similar to the following:
SELECT PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE FROM INVENTORY_TRANS WHERE CUST_ORDER_ID IS NOT NULL AND TYPE='O' AND CLASS='I' GROUP BY PART_ID;
In the above, the AS keyword is optional on an Oracle Database platform, but it is not optional on some other database platforms.
With an Oracle Database backend, inline views may be used to permit the above SQL statement to be used to retrieve additional information for a parent SQL statement. If we wrap the above SQL statement in ( ) and place it in the FROM clause of the SQL statement, Oracle will treat the data returned by the above SQL statement quite similar to how it would handle a regular table (the Oracle query optimizer might decide to rewrite the combined SQL statement into an equivalent form that no longer contains an inline view). There is always a chance that a part number that is on order may have never shipped to a customer, so we will need to outer join (in this case using a left outer join) to the newly created inline view:
SELECT COL.CUST_ORDER_ID, COL.PART_ID, LS.LAST_TRANSACTION_DATE FROM CUSTOMER_ORDER CO, CUST_ORDER_LINE COL, (SELECT PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE FROM INVENTORY_TRANS WHERE CUST_ORDER_ID IS NOT NULL AND TYPE='O' AND CLASS='I' GROUP BY PART_ID) LS WHERE CO.ID=COL.CUST_ORDER_ID AND CO.STATUS IN ('U','F','R') AND COL.LINE_STATUS='A' AND COL.ORDER_QTY>COL.TOTAL_SHIPPED_QTY AND COL.PART_ID=LS.PART_ID(+);
In the above, I joined the CUSTOMER_ORDER and CUST_ORDER_LINE tables to the inline view that I aliased as LS. A third table, CUST_LINE_DEL, that optionally contains the delivery schedule for some of the rows in the CUST_ORDER_LINE table, could have also been outer joined to the CUST_ORDER_LINE table.
Running SQL Server, or just feeling ANSI? If so, the above may be rewritten as follows:
SELECT COL.CUST_ORDER_ID, COL.PART_ID, LS.LAST_TRANSACTION_DATE FROM CUSTOMER_ORDER CO JOIN CUST_ORDER_LINE COL ON CO.ID=COL.CUST_ORDER_ID LEFT OUTER JOIN (SELECT PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE FROM INVENTORY_TRANS WHERE CUST_ORDER_ID IS NOT NULL AND TYPE='O' AND CLASS='I' GROUP BY PART_ID) LS ON COL.PART_ID=LS.PART_ID WHERE CO.STATUS IN ('U','F','R') AND COL.LINE_STATUS='A' AND COL.ORDER_QTY>COL.TOTAL_SHIPPED_QTY;
Unfortunately, the OP is actually using a SQLBase database backend that does not support inline views. I remember the feeling before I discovered that Oracle Database supported inline views… in that case I would do something like the following:
- Create a statically defined view.
- Join to that statically defined view just as if the view were a table.
CREATE VIEW CUST_ORDER_PART_LS AS SELECT PART_ID, MAX(TRANSACTION_DATE) AS LAST_TRANSACTION_DATE FROM INVENTORY_TRANS WHERE CUST_ORDER_ID IS NOT NULL AND TYPE='O' AND CLASS='I' GROUP BY PART_ID; SELECT COL.CUST_ORDER_ID, COL.PART_ID, LS.LAST_TRANSACTION_DATE FROM CUSTOMER_ORDER CO, CUST_ORDER_LINE COL, CUST_ORDER_PART_LS LS WHERE CO.ID=COL.CUST_ORDER_ID AND CO.STATUS IN ('U','F','R') AND COL.LINE_STATUS='A' AND COL.ORDER_QTY>COL.TOTAL_SHIPPED_QTY AND COL.PART_ID=LS.PART_ID(+);
I guess that it is a good exercise once in a while to practice simple SQL.
—
A second example from the ERP mailing list showed a SQL Server solution for a particular problem. The particular problem that the SQL Server solution set out to solve is as follows:
“Someone posed an interesting question to me – How can you do a mass insert of Operations? Well most of us know how to manage that without too much trouble but this one came with a couple of caveats – The Master’s first operation (lowest numbered) has to have a particular ResourceID and then a different but specific ResourceID must be on a subsequent Operation. It is after the second operation where the insert must be placed.
Sounds like fun – So I figured that it could be done in a single SQL statement – well after a 4-way join of the Operation table to itself I got it to work how I expected…”
Interesting, I think that there are times in the past that I have used multiple self-joins to a table in order to solve similar SQL problems. However, there is an easier way using analytic functions. The following SQL statement attempts to indicate: the sequence of the operation within the work order sub ID, the sequence number that follows sequentially, the second sequence number that follows sequentially, and the resource ID of the next operation in sequential order by the operation number:
SELECT WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, RESOURCE_ID, ROW_NUMBER() OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS RN, LEAD(RESOURCE_ID,1) OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS NEXT_RESOURCE_ID, LEAD(SEQUENCE_NO,1) OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS NEXT_SEQ_NO, LEAD(SEQUENCE_NO,2) OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS NEXT_NEXT_SEQ_NO FROM OPERATION WHERE WORKORDER_TYPE='M';
Next, we only care about those rows when our resource ID of interest (69 in my example) is specified in the first operation on the work order sub ID and the second operation on that work order sub ID specifies the other resource ID that is of interest (FW in my example). If we are to avoid primary key violations, we should select a new sequence number that is half way between the next-next operation and the next operation:
SELECT WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, NEXT_SEQ_NO, NEXT_NEXT_SEQ_NO, NEXT_SEQ_NO + ROUND((COALESCE(NEXT_NEXT_SEQ_NO,NEXT_SEQ_NO+10) - NEXT_SEQ_NO)/2) NEW_SEQUENCE_NO, 'NEW RESOURCE' RESOURCE_ID FROM (SELECT WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, RESOURCE_ID, ROW_NUMBER() OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS RN, LEAD(RESOURCE_ID,1) OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS NEXT_RESOURCE_ID, LEAD(SEQUENCE_NO,1) OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS NEXT_SEQ_NO, LEAD(SEQUENCE_NO,2) OVER (PARTITION BY WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID ORDER BY RESOURCE_ID) AS NEXT_NEXT_SEQ_NO FROM OPERATION WHERE WORKORDER_TYPE='M') WHERE RN=1 AND RESOURCE_ID='69' AND NEXT_RESOURCE_ID='FW'; WORKORDER_ WOR WOR WOR SEQUENCE_NO NEXT_SEQ_NO NEXT_NEXT_SEQ_NO NEW_SEQUENCE_NO RESOURCE_ID ---------- --- --- --- ----------- ----------- ---------------- --------------- ------------ 231610 0 0 0 10 777 888 833 NEW RESOURCE 237680 0 0 0 10 777 888 833 NEW RESOURCE 32018917X 0 0 0 10 777 888 833 NEW RESOURCE 3201B8920 0 0 0 10 777 888 833 NEW RESOURCE 3201C8765 0 0 0 10 777 888 833 NEW RESOURCE 3201G8639 0 0 0 10 777 888 833 NEW RESOURCE 3201G9003 0 0 0 10 777 888 833 NEW RESOURCE 3201J8772 0 0 0 10 777 888 833 NEW RESOURCE 3201J8850 0 0 0 10 777 888 833 NEW RESOURCE 3201K8669 0 0 0 10 777 888 833 NEW RESOURCE 3201M8281 0 0 0 10 777 888 833 NEW RESOURCE 3201M8489 0 0 0 10 777 888 833 NEW RESOURCE 3201N8516 0 0 0 10 777 888 833 NEW RESOURCE 3201N8776 0 0 0 10 777 888 833 NEW RESOURCE 3201Q8545 0 0 0 10 777 888 833 NEW RESOURCE 3201T8964 0 0 0 10 777 888 833 NEW RESOURCE 3201T8964X 0 0 0 10 20 30 25 NEW RESOURCE 3201V8524 0 0 0 10 777 888 833 NEW RESOURCE 3201V8966 0 0 0 10 777 888 833 NEW RESOURCE 3201V8966X 0 0 0 10 20 30 25 NEW RESOURCE 3201W8967 0 0 0 10 777 888 833 NEW RESOURCE 3201W8967X 0 0 0 10 20 30 25 NEW RESOURCE 3201Y8423 0 0 0 10 777 888 833 NEW RESOURCE 3201Z8996 0 0 0 10 777 888 833 NEW RESOURCE 24 rows selected.
With the above, we are well on our way to perfoming a mass insert into this table. If I recall correctly, the above must first be inserted into another table (a temp table, preferrably) and then the rows may be inserted into the OPERATION table (the table that is the row source for the analytic functions used in the SQL statement).
[…] of leading Oracle blogger Charles Hooper strikes again. Sometimes it is interesting to take a step back from some of the more difficult to […]