Hi again Lennart.
I found this example on our board on 12-22-2003 14:30
is this doable in DB2 OS/390 V8.2
The detail
create table ASSETCLASS
(
ASSETCLASS char(2) not null,
DESCRIPTION char(50),
DEPRECIATIONMETHOD char(35) not null,
USEFULLIFE smallint not null
);
INSERT
INTO AssetClass (AssetClass, Description,DepreciationMethod,
UsefulLife )
VALUES ('CS', 'Computer Software', 'Straight Line', 2 );
create table ASSET
(
ASSETID char(6) not null,
ASSETCLASS char(2) not null,
DESCRIPTION char(50) not null,
COST numeric(8,2) not null,
DATEOFPURCHASE date not null,
RESIDUALVALUE numeric(8,2) not null,
check (Cost > ResidualValue)
);
INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('CS0003', 'CS', 'MS Office 2000', 2400.00, '2001-11-01',
0.00);
And now the one with the multi-table check constraint
create table DEPRECIATION
(
ASSETID char(6) not null,
DEPRECIATIONDATE date not null,
DEPRECIABLEAMOUNT numeric(8,2) not null,
check (NOT EXISTS (select b.assetID
from depreciation as a, asset as b, assetClass as c
where a.AssetID = b.AssetID and
b.assetClass = c.AssetClass and
a.depreciationDate > dateadd(year, c.usefulLife,
b.dateOfPurchase) ))
);
INSERT
INTO Depreciation (AssetId, DepreciationDate, DepreciableAmount)
VALUES ('CS0003', '2004-02-28', 600.00);
Allowed but subsequent inserts fail????? It seems as if my dbms
evaluates the check on the existing table before inserting the new
record???
3 WEEKS LATER:
01-07-2004 01:31
OK i think i now understand how to relate the record/fields to be inserted
with existing data in the db.
So here goes again
create table ASSETCLASS
(
ASSETCLASS char(2) not null,
DESCRIPTION char(50),
DEPRECIATIONMETHOD char(35) not null,
USEFULLIFE smallint not null
);
INSERT
INTO AssetClass (AssetClass, Description,DepreciationMethod,
UsefulLife )
VALUES ('CS', 'Computer Software', 'Straight Line', 2 );
INSERT
INTO AssetClass (AssetClass, Description,DepreciationMethod, UsefulLife )
VALUES ('MV', 'Motor Vehicles', 'Sum of Digits', 5 );
create table ASSET
(
ASSETID char(6) not null,
ASSETCLASS char(2) not null,
DESCRIPTION char(50) not null,
COST numeric(8,2) not null,
DATEOFPURCHASE date not null,
RESIDUALVALUE numeric(8,2) not null,
check (Cost > ResidualValue)
);
INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('CS0003', 'CS', 'MS Office 2000', 2400.00, '2001-11-01',
0.00);
INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('MV0005', 'MV', 'LDV HGG702 GP', 30000.00, '1998-06-01', 7500.00);
And now the one with the multi-table check constraint
create table DEPRECIATION
(
ASSETID char(6) not null,
DEPRECIATIONDATE date not null,
DEPRECIABLEAMOUNT numeric(8,2) not null,
check (NOT EXISTS (select a.assetID
from asset as a, assetClass as b
where AssetID = a.AssetID and
a.assetClass = b.AssetClass and
depreciationDate >= dateadd(year, b.usefulLife, a.dateOfPurchase) ))
);
INSERT
INTO Depreciation (AssetId, DepreciationDate, DepreciableAmount)
VALUES ('CS0003', '2003-05-31', 600.00);
works but
INSERT
INTO Depreciation (AssetId, DepreciationDate, DepreciableAmount)
VALUES ('CS0003', '2003-06-07', 600.00);
fails - the usefullLife of MV are used and not that of CS ????
select a.assetID
from asset as a, assetClass as b
where 'CS0003' = a.AssetID and
a.assetClass = b.AssetClass and
<put date here> = dateadd(year, b.usefulLife, a.dateOfPurchase)
only returns assetID for dates >= 2003-11-01 which is what one would
expect,
however when used as search condition in check constraint things go wrong.
lenygold wrote:
>Thank's again Lennart.
>I will test this constrain.
>>[...]
>>> ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
>[quoted text clipped - 12 lines]
>>
>>/Lennart
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums.aspx/ibm-db2/200807/1
|
