Re: a join between tables and a 'selective' update

"Rafal Pietrak" <irypo_at_interia.pl> wrote in message news:pan.2005.01.18.15.47.49.581555_at_interia.pl...
> Hi,
> I'm not quite sure if this is the best group for this kind of question,
> But I hope so (the theory tag look encouraging :)
>
> I fell into the following problem:
>
> I have numerous 'logfiles', (which in reality are implemented as
> database tables) all close to, but all somewhat different in details as
> those two:
> CREATE TABLE logfile1 (tm timestamp,
> something numeric,
> something1 varchar,
> something2 integer references foreign key...,
> ... etc);
> CREATE TABLE logfile2 (stamp date, when time,
> something varchar,
> something char[4],
> ... etc);
> 2. All those 'logfiles' have some sort of timestamp, which I already CAN
> convert to a common TIMESTAMP value.
> 3. All those 'logfiles' have some additional info, which one way or
> another I CAN convert into two fields (user integer, info varchar).
>
> By now, I do:
> CREATE TEMP TABLE window (tm timestamp,
> user integer, info varchar2);
>
> Then, I extract the necessary information from all 'participating' tables
> by means of:
> SELECT INTO window tm, something as user, something as info FROM ...
> SELECT INTO window to_date(stamp) as tm, ....
> ...
>
> just to be able to make the following query:
> SELECT * FROM window ORDER BY tm;
> ...so that I can see the sequence of events irrespective of the 'logfile'
> the event comes from.
>
> Is there a simpler (but generic SQL) way to achieve the same output from
> given 'any logfile' set? One without an explicit creation of a transient
> WINDOW table?

As already noted, determine a canonical form for the relevant data in your "log files" and take the UNION (or better yet, UNION ALL).

> ANOTHER closely related SQL problem is the ability to update just a few
> records of a table, records, which happen NOT to be distinguishable
> from one another to program making the query.
>
> To explain.
>
> Let's have a store, which hold food. One of the products is milk in cans.
> Every milk can has a unique (but random) serial number (ID) and validity
> date. When making a selection for a 'transporter' to shopping floor I'd
> like to select those that have earliest expiry date (are oldest). For the
> purpose of this problem, pls assume, that the timestamp MAY*NOT appear in
> query output. So, when shopping floor requests 10 cans, I mean something
> like:
>
> SELECT id FROM milk_store ORDER DESC BY expiry LIMIT 10;
>
> (forgive me using the PostgreSQL LIMIT expression - but this is easies way
> to *present*the*problem) I'm actually looking for a generic SQL phrase,
> that would have the desired result (the above want work from PostgreSQL
> either, since the query may not be sorted by 'not displayed' column).
>
> Then of course I'd like to be able to mark in database, which cans went
> out. Meaning something like:
> UPDATE milk_store SET state=sold WHERE id in (SELECT id FROM
> milk_store ORDER DESC BY expiry LIMIT 10);
>
> Any help appreciated - particularly how to achieve above WITHOUT the LIMIT
> clause, which I understand is not quite so generic SQL.... note, that I'm
> searching for an SQL solution, not 'procedural SQL', which at this point
> is not quite so interesting for me.

CREATE TABLE Inventory
(
product_code VARCHAR(10) NOT NULL, -- for example serial_number INT NOT NULL, -- unique to product category expiration_date DATE NOT NULL, -- date item expires sold_date DATE NULL DEFAULT NULL, -- date item is sold PRIMARY KEY (product_code, serial_number) )

• Items in a product category are ranked in increasing-date order
• with ties satisfied in increasing-serial-number order CREATE VIEW RankProductsByDate (product_code, serial_number, expiration_date, product_rank) AS SELECT I1.product_code, I1.serial_number, I1.expiration_date, COUNT(*) - COUNT(CASE WHEN I2.expiration_date = I1.expiration_date AND I2.serial_number > I1.serial_number THEN I2.serial_number END) FROM Inventory AS I1 INNER JOIN Inventory AS I2 ON I2.product_code = I1.product_code AND I2.expiration_date <= I1.expiration_date AND I1.sold_date IS NULL AND I2.sold_date IS NULL GROUP BY I1.product_code, I1.serial_number, I1.expiration_date
• To find next 10 cartons of milk to sell SELECT product_code, serial_number FROM RankProductsByDate WHERE product_rank <= 10 AND product_code = 'MILK'
• To sell those 10 cartons of milk UPDATE Inventory SET sold_date = CURRENT_DATE WHERE product_code = 'MILK' AND EXISTS (SELECT * FROM RankProductsByDate AS R WHERE R.product_code = 'MILK' AND R.product_rank <= 10 AND R.serial_number = Inventory.serial_number)

> Regards,
> -R

--
JAG