Prev: db2 Shared CGTT(Global Temp Table) Data ploblem?
Next: CLPPLus
From: --CELKO-- on 25 Jun 2010 13:47
Here is a problem that has me stuck. This is a skeleton of an
auction. We start with a simple table of bids for the items. The key
is the item's identifier and sequential bid number within each time.
To give the table a key when an item is put on auction, we put in a
dummy bid and amount.

CREATE TABLE Auctions
(item_id CHAR(8) NOT NULL,
bid_seq INTEGER DEFAULT 0 NOT NULL
CHECK (bid_seq >= 0),
PRIMARY KEY (item_id, bid_seq),
bid_amt DECIMAL (8,2) DEFAULT 0.00 NOT NULL
CHECK (bid_amt >= 0.00));

INSERT INTO Auctions (item_id)
VALUES ('Item A'), ('Item B'), ('Item C');

Assume a single bidder so that the Actions tables does not need a
bidder_id column as part of the key. He puts in a list of his bids on
the items on auction.

CREATE TABLE Bid_List
(item_id CHAR(8) NOT NULL PRIMARY KEY,
bid_amt DECIMAL (8,2) NOT NULL
CHECK (bid_amt >= 0.00));

INSERT INTO Bid_List (item_id, bid_amt)
VALUES ('Item A', 14.55), ('Item C', 13.50);

Notice that he cannot make two bids on the same item thanks to the key
and cannot bid a negative amount (he can do charity work for free,
however). There is no need to use IDENTITY or @@IDENTITY (row at a
time numbering) to get multiple items sequentially numbered.

INSERT INTO Auctions (item_id, bid_seq, bid_amt)
SELECT DISTINCT Bid_List.item_id, MAX(Auctions.bid_seq) OVER
(PARTITION BY Auctions.item_id)+1, Bid_List.bid_amt
FROM Auctions, Bid_List
WHERE Auctions.item_id = Bid_List.item_id;

item_id bid_seq bid_amt
=========================
Item A 0 0.00
Item A 1 14.55
Item B 0 0.00
Item B 1 13.50
Item C 0 0.00

Obviously it is easy to have VIEWs to show the items with and without
a bid, the highest current bid, etc. When some of the items have real
bids, the dummy bids can be dropped to save space and make other
computations in the real system much easier. To clean out the dummy
bids once we get a real bid can be done with way:

DELETE FROM Auctions
WHERE bid_seq = 0
AND EXISTS
(SELECT *
FROM Auctions AS A1
WHERE Auctions.item_id = A1.item_id
AND bid_seq > 0);

item_id bid_seq bid_amt
=========================
Item A 1 14.55
Item B 1 13.50
Item C 0 0.00

There might be a way to put all of this code into a single MERGE
statement. I cannot figure it out. Anyone see a solution?
From: Tonkuma on 25 Jun 2010 20:58
If you insert into Auctions by the following statement,
dummy rows(with bid_amt = 0) and delete statement for the rows will be
not necessary.

INSERT INTO Auctions
(item_id , bid_seq , bid_amt)
SELECT item_id
, COALESCE( (SELECT MAX(A.bid_seq)
FROM Auctions A
WHERE A.item_id = B.item_id)
, 0
) + 1
, bid_amt
FROM Bid_List B
;

From: Tonkuma on 27 Jun 2010 14:22
>> To give the table a key when an item is put on auction, we put in a dummy bid and amount.<<
I thought that it might be natural to make a table for items on
auction, according to normalization theory.

Like this:
CREATE TABLE Items
( item_id CHAR(8) NOT NULL PRIMARY KEY
);

INSERT INTO Items
VALUES 'Item A' , 'Item B' , 'Item C';

ALTER TABLE Auctions
ADD CONSTRAINT auctions_ref_items
FOREIGN KEY(item_id) REFERENCES Items;

From: --CELKO-- on 28 Jun 2010 12:37
This is a very bony skeleton. The bids also have creation and
expiration dates, there is an unknown number of bidders, bids and
offers can be retracted, etc.

There are actually no physical items; what I am calling an auction is
a request for services (“I need to move a one-room apartment from
Austin to New York City. Who wants to make an offer?”). Since there is
no inventory, we did not go with an Items table and have a dummy place
holder in the design. The item is created by being offered. Having
them all in one table makes some computations easier (service fees,
even if no bid is made; time before a bid is made; step size in bid
sequence; etc.)

The MERGE statement defined in the SQL:2003 Standard permitted at most
one WHEN MATCHED and at most one WHEN NOT MATCHED clause. You are also
limited to UPDATE and INSERT actions (no DELETE) and an INNER JOIN in
the ON clause.

Implementations of MERGE are all over the place. INFORMIX, DB2, Oracle
SQL Anywhere and MS-SQL Server 2008 are all a little different. Oh,
you cannot use a CTE with a MERGE in Standard SQL and other SQL
products, so the source has to be a derived table.

I don't have the draft of the SQL:2006 Standard with me right now; I
hope it is stronger now. The SOURCE and TARGET extensions look look
good, even if they hide a FULL OUTER JOIN under the covers.

I was thinking that the first bid would update the dummy from (bid_seq
= 0) to (bid_seq = 1), and then other bids would be inserted, so I
would have portable code.
From: Lennart Jonsson on 28 Jun 2010 13:02
On 2010-06-28 18:37, --CELKO-- wrote:
[...]
> I don't have the draft of the SQL:2006 Standard with me right now; I
> hope it is stronger now. The SOURCE and TARGET extensions look look
> good, even if they hide a FULL OUTER JOIN under the covers.

The 2006-2008 Draft is availible here:

http://www.wiscorp.com/SQLStandards.html


/Lennart
 |  Next  |  Last
Pages: 1 2 3
Prev: db2 Shared CGTT(Global Temp Table) Data ploblem?
Next: CLPPLus