Re: How would you approach this?

"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]
> > > > > Pick file layout description:
> > > > > File: INVOICES
> > > > > ID: Invoice Number
> > > > > 001: Product codes (multivalued)
> > > > > 002: Quantity ordered (multivalued)
> > > > > 003: Requested Ship-by Dates (multivalued)
> > > > > 004: Quantity despatched - for each partial shipment (subvalued)
> > > > > 005: Despatched Date for each partial shipment (subvalued)
[snip]
> > > > > 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
> > > Each line (001-005) is what we call an 'attribute', See the layout
> > > described above.

[snip]
> > > 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)
> > > and we call that a 'subvalue mark' - to separate the multiple
> > > sub-values within each 'multivalue'.
[snip]
> > > > > :list invoices '12345' product size colour total orderqty reqshipdate
> > > > > total shipqty actshipdate
> > > > >
> >
> > Page 1 invoices 18:12:14 23
> > Oct 2003
> >
> > 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? Received on Sat Nov 08 2003 - 05:41:25 CET