Simple SQL with and without Inline Views

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:

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

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: