Re: How would you approach this?
Date: Mon, 10 Nov 2003 04:05:16 GMT
Message-ID: <08Erb.59483$Gq.12223700_at_twister.nyc.rr.com>
"Mike Preece" <michael_at_preece.net> wrote in message news:1b0b566c.0311072041.84db96f_at_posting.google.com...
> "John Gilson" <jag_at_acm.org> wrote in message news:<SMdnb.49419$pT1.25586_at_twister.nyc.rr.com>... > [snip]
> > > The example includes a typical approach that might be taken to meet
> > > the requirements using Pick/MV.
> [snip]> > > > > > 004: Quantity despatched - for each partial shipment (subvalued)
> > > > > > Pick file layout description:
> > > > > > File: INVOICES
> > > > > > ID: Invoice Number
> > > > > > 001: Product codes (multivalued)
> > > > > > 002: Quantity ordered (multivalued)
> > > > > > 003: Requested Ship-by Dates (multivalued)
> > > > > > 005: Despatched Date for each partial shipment (subvalued)
> [snip]> > > > Each line (001-005) is what we call an 'attribute', See the layout
> > > > > > Actual Pick file contents:
> > > > > > INVOICES
> > > > > > ID: 12345
> > > > > > 001: ABC*123*RED]ABC*456*RED]DEF*123*BLUE]DEF*789*WHITE
> > > > > > 002: 100]400]200]300
> > > > > > 003: 13085]13090]13090]13087
> > > > > > 004: 50\50]230]200]100\120
> > > > > > 005: 13079\13080]13080]13079]13078\13080
> > > > described above.
> [snip]> > > > and we call that a 'subvalue mark' - to separate the multiple
> > > > The ']' represents a char(253) which
> > > > is a Pick system delimiter called a 'value mark'. They separate each
> > > > of the multiple values within each attribute. The '\' is a char(252)
> > > > sub-values within each 'multivalue'.
> [snip]> > > > > > total shipqty actshipdate
> > > > > > :list invoices '12345' product size colour total orderqty reqshipdate
> > > > > >
> > >> > > Oct 2003
> > > Page 1 invoices 18:12:14 23
> > >
> > > invoices.. product size colour Quantity Ship By.... Quantity..
> > > Despatched On
> > > Ordered Despatched
> > >
> > > 12345 ABC 123 RED 100 28 Oct 2003 50 22
> > > Oct 2003
> > > 50 23
> > > Oct 2003
> > > ABC 456 RED 400 02 Nov 2003 230 23
> > > Oct 2003
> > > DEF 123 BLUE 200 02 Nov 2003 200 22
> > > Oct 2003
> > > DEF 789 WHITE 300 30 Oct 2003 100 21
> > > Oct 2003
> > > 20 23
> > > Oct 2003
> > > *** 1000 650
> > > > Here's a relational solution written in Standard SQL (SQL/92). > [snip] > > CREATE TABLE Customers > > ( > > customer_id INT NOT NULL PRIMARY KEY, > > customer_first_name VARCHAR(20) NOT NULL, > > customer_last_name VARCHAR(20) NOT NULL > > -- other customer details > > ) > > > > CREATE TABLE ProductSizes > > ( > > product_size INT NOT NULL PRIMARY KEY > > ) > > > > CREATE TABLE ProductColors > > ( > > product_color VARCHAR(10) NOT NULL PRIMARY KEY > > CHECK (CHAR_LENGTH(product_color) <> 0) > > ) > > > > CREATE TABLE Products > > ( > > product_number VARCHAR(10) NOT NULL, -- Misnomer but maintained as > > -- company-specific jargon > > product_size INT NOT NULL REFERENCES ProductSizes (product_size), > > product_color VARCHAR(10) NOT NULL > > REFERENCES ProductColors (product_color), > > PRIMARY KEY (product_number, product_size, product_color) > > ) > > > > CREATE TABLE Orders > > ( > > order_id INT NOT NULL PRIMARY KEY, > > customer_id INT NOT NULL REFERENCES Customers (customer_id), > > order_placed_date TIMESTAMP NOT NULL > > CHECK (order_placed_date <= CURRENT_TIMESTAMP) > > ) > > > > CREATE TABLE OrderItems > > ( > > order_id INT NOT NULL REFERENCES Orders (order_id), > > product_number VARCHAR(10) NOT NULL, > > product_size INT NOT NULL, > > product_color VARCHAR(10) NOT NULL, > > quantity INT NOT NULL CHECK (quantity > 0), > > -- maintains constraint of ship date being >= order date > > order_ship_by_date TIMESTAMP NOT NULL > > CHECK (order_ship_by_date >= > > (SELECT order_placed_date > > FROM Orders AS O > > WHERE O.order_id = order_id)), > > FOREIGN KEY (product_number, product_size, product_color) > > REFERENCES Products (product_number, product_size, product_color), > > PRIMARY KEY (order_id, product_number, product_size, product_color) > > ) > > > > CREATE TABLE OrderShipments > > ( > > shipment_id INT NOT NULL PRIMARY KEY, > > order_id INT NOT NULL, > > product_number VARCHAR(10) NOT NULL, > > product_size INT NOT NULL, > > product_color VARCHAR(10) NOT NULL, > > -- maintains constraint of ship date being between now and the latest > > -- date this order item can be shipped > > ship_date TIMESTAMP NOT NULL > > CHECK (ship_date BETWEEN > > CURRENT_TIMESTAMP AND > > (SELECT order_ship_by_date > > FROM OrderItems AS I > > WHERE I.order_id = order_id AND > > I.product_number = product_number AND > > I.product_size = product_size AND > > I.product_color = product_color)), > > -- maintains constraint that quantity shipped is > 0 and <= the amount > > -- of this order item outstanding, i.e., that hasn't yet been shipped > > quantity INT NOT NULL > > CHECK (quantity > 0 AND > > quantity <= > > (SELECT I.quantity - > > COALESCE(SUM(S.quantity), 0) > > FROM OrderItems AS I > > LEFT OUTER JOIN > > Shipments AS S > > ON I.order_id = order_id AND > > I.product_number = product_number AND > > I.product_size = product_size AND > > I.product_color = product_color AND > > S.order_id = order_id AND > > S.product_number = product_number AND > > S.product_size = product_size AND > > S.product_color = product_color AND > > S.ship_date < ship_date > > GROUP BY I.quantity)), > > FOREIGN KEY (order_id, product_number, product_size, product_color) > > REFERENCES OrderItems (order_id, product_number, product_size, product_color), > > UNIQUE (order_id, product_number, product_size, product_color, ship_date) > > ) > > > > Data on all shipments can be found with the following query: > > > > SELECT O.customer_id, > > O.order_placed_date, > > O.order_id, > > I.product_number, > > I.product_size, > > I.product_color, > > I.quantity AS quantity_ordered, > > I.order_ship_by_date, > > S.quantity AS quantity_shipped, > > S.ship_date > > FROM OrderShipments AS S > > INNER JOIN > > OrderItems AS I > > ON S.order_id = I.order_id AND > > S.product_number = I.product_number AND > > S.product_size = I.product_size AND > > S.product_color = I.product_color > > INNER JOIN > > Orders AS O > > ON O.order_id = I.order_id > > ORDER BY O.customer_id, O.order_placed_date, O.order_id, > > I.product_number, I.product_size, I.product_color, > > S.ship_date > > > > Regards, > > jag
>
> Could the SQL solution be implemented using RVAs?
I'm most familiar with MS SQL Server's Transact-SQL which does not allow multi-valued attributes. However, Oracle does by using nested table types, i.e., an attribute whose type is a table. My understanding is that while a top-level table's attribute type can be a table type, in Oracle that nested table type can't itself contain a table type attribute, i.e., table nesting isn't allowed beyond one level.
In considering a RVA solution, one could look to make OrderShipments a nested table within OrderItems, that is, each order item has a nested table of shipments (Oracle-specific code has been mixed in with the previously given SQL/92 code).
- Create a new object type. An object type has attributes of various
- types, analogous to columns of a table. CREATE TYPE ShippedQuantity AS OBJECT ( shipment_id INTEGER, ship_date DATE, quantity INTEGER )
- Create the nested table type CREATE TYPE Shipments AS TABLE OF ShippedQuantity
- Create parent table with a nested table type attribute CREATE TABLE OrderItems ( order_id INT NOT NULL REFERENCES Orders (order_id), product_number VARCHAR(10) NOT NULL, product_size INT NOT NULL, product_color VARCHAR(10) NOT NULL, quantity INT NOT NULL CHECK (quantity > 0),
- maintains constraint of ship date being >= order date order_ship_by_date TIMESTAMP NOT NULL CHECK (order_ship_by_date >= (SELECT order_placed_date FROM Orders AS O WHERE O.order_id = order_id)), shipped_orders Shipments, -- table of order shipments FOREIGN KEY (product_number, product_size, product_color) REFERENCES Products (product_number, product_size, product_color), PRIMARY KEY (order_id, product_number, product_size, product_color) ) NESTED TABLE shipped_orders STORE AS NestedShippedOrders
The NESTED TABLE clause specifies a table name, NestedShippedOrders, to contain multiple ShippedQuantity objects that can appear in the shipped_orders column of the OrderItems table. Two tables will be created by this one CREATE TABLE statement, table OrderItems as the top-level table and table NestedShippedOrders for the shipped_orders table data.
Data on all shipments could be retrieved by the following query:
SELECT O.customer_id,
O.order_placed_date,
O.order_id,
I.product_number,
I.product_size,
I.product_color,
I.quantity AS quantity_ordered,
I.order_ship_by_date,
I.shipped_orders -- lists all shipped orders for this item as one
-- scalar nested table value
FROM Orders AS O
INNER JOIN
OrderItems AS I
ON O.order_id = I.order_id
ORDER BY O.customer_id, O.order_placed_date, O.order_id,
I.product_number, I.product_size, I.product_color
The asymmetry of representation might be perspicuous in the presence of a query that is of a compatible nature, as above, however, perhaps less so when it's not, e.g., consider wanting to sum all shipment quantities for all orders for each shipping date. In the first approach (non-RVA), the query would be (let's assume, for simplicity, that a ship_date is just a date and not a date and time)
SELECT ship_date, SUM(quantity) AS total_shipped
FROM OrderShipments
GROUP BY ship_date
With the RVA approach, it turns out that trying to retrieve ship_date and quantity from *all* the shipped_orders of *all* OrderItems is not as simple of a task. For a given row in OrderItems, when we refer to shipped_orders directly we are referring to a *scalar* nested table column value, e.g.,
SELECT shipped_orders
FROM OrderItems
displays one line for each OrderItem, with the shipped_orders listed as one scalar nested table value. If we wish to operate on this value as a table instead, we need to explicitly cast it to a table. For example, to retrieve all OrderItems that haven't been fully shipped one would write
SELECT I.order_id, I.product_number, I.product_size, I.product_color
FROM OrderItems AS I
WHERE I.quantity > (SELECT SUM(S.quantity)
FROM TABLE(I.shipped_orders) AS S)
The TABLE() form provides the cast to a table of ShippedQuantity as aggregation is done on tables. Without such a conversion, I.shipped_orders is a scalar nested table value waiting to be coaxed into a table. In this case it could be done implicitly but it's not. Regardless, this is how we would operate on the rows of a *single* nested table at a time.
When trying to aggregate all shipped orders for each date with the RVA approach, we need to select from *all* nested tables, one for each OrderItem, and aggregate on all their rows at once, rather than retrieving the rows of one nested table at a time as was illustrated above. We end up needing to retrieve values from a table of tables, and not just a single nested table or from a table where each nested table is a scalar value. To address this, Oracle allows for unnesting of a table of nested tables via table products. So, finally, using the RVA approach, we can determine the sum of all shipped quantities across all orders for each shipping date as
SELECT S.ship_date, SUM(S.quantity) AS total_shipped FROM OrderItems AS I, TABLE(I.shipped_orders) AS S GROUP BY S.ship_date
First, we've had to involve the OrderItems table in the query as reference to it is necessary as the only way to access its order shipments. Second, and more fundamental, we've had to modify the relational product operation. The normal relational product operation matches each row in the first table with each row in the second table. In the table product used above to unnest a table of tables, each row in the first table is matched with each row of *its own* nested table column value, that is, each row of OrderItems is matched with each row of its Shipments nested table.
Regards,
jag
Received on Mon Nov 10 2003 - 05:05:16 CET