-- Test run on Teradata 12 (2009-05-02)
DROP TABLE ANAT2_AnchorAttribute2;
DROP TABLE ANAT1_AnchorAttribute1;
DROP TABLE AN_Anchor;
CREATE TABLE AN_Anchor (
AN_ID INT NOT NULL,
CONSTRAINT pk_AN_Anchor PRIMARY KEY(AN_ID)
);
CREATE TABLE ANAT1_AnchorAttribute1 (
AN_ID INT NOT NULL,
ANAT1_AnchorAttribute1Value CHAR(10) NOT NULL,
CONSTRAINT pk_ANAT1_AnchorAttribute1
PRIMARY KEY(AN_ID),
CONSTRAINT fk_ANAT1_AnchorAttribute1
FOREIGN KEY(AN_ID) REFERENCES AN_Anchor(AN_ID)
);
CREATE TABLE ANAT2_AnchorAttribute2 (
AN_ID INT NOT NULL,
ANAT2_AnchorAttribute2Value CHAR(10) NOT NULL,
ANAT2_FromDate DATE format 'YYYY-MM-DD' NOT NULL,
CONSTRAINT pk_ANAT2_AnchorAttribute2
PRIMARY KEY(AN_ID, ANAT2_FromDate),
CONSTRAINT fk_ANAT2_AnchorAttribute2
FOREIGN KEY(AN_ID) REFERENCES AN_Anchor(AN_ID)
);
DROP VIEW lAN_Anchor;
CREATE VIEW lAN_Anchor
AS
SELECT
AN.AN_ID,
ANAT1.ANAT1_AnchorAttribute1Value,
ANAT2.ANAT2_AnchorAttribute2Value,
ANAT2.ANAT2_FromDate
FROM
AN_Anchor AN
LEFT JOIN
ANAT1_AnchorAttribute1 ANAT1
ON
ANAT1.AN_ID = AN.AN_ID
LEFT JOIN
ANAT2_AnchorAttribute2 ANAT2
ON
ANAT2.AN_ID = AN.AN_ID
AND
ANAT2.ANAT2_FromDate = (
SELECT
MAX(sub.ANAT2_FromDate)
FROM
ANAT2_AnchorAttribute2 sub
WHERE
sub.AN_ID = AN.AN_ID
);
INSERT INTO AN_Anchor (
AN_ID
)
VALUES
(1);
INSERT INTO AN_Anchor (
AN_ID
)
VALUES
(2);
INSERT INTO AN_Anchor (
AN_ID
)
VALUES
(3);
INSERT INTO ANAT1_AnchorAttribute1 (
AN_ID,
ANAT1_AnchorAttribute1Value
)
VALUES
(1, 'Green');
INSERT INTO ANAT1_AnchorAttribute1 (
AN_ID,
ANAT1_AnchorAttribute1Value
)
VALUES
(2, 'Blue');
INSERT INTO ANAT1_AnchorAttribute1 (
AN_ID,
ANAT1_AnchorAttribute1Value
)
VALUES
(3, 'Red');
INSERT INTO ANAT2_AnchorAttribute2 (
AN_ID,
ANAT2_AnchorAttribute2Value,
ANAT2_FromDate
)
VALUES
(1, 'Car', '2008-01-01');
INSERT INTO ANAT2_AnchorAttribute2 (
AN_ID,
ANAT2_AnchorAttribute2Value,
ANAT2_FromDate
)
VALUES
(1, 'Bus', '2008-11-12');
INSERT INTO ANAT2_AnchorAttribute2 (
AN_ID,
ANAT2_AnchorAttribute2Value,
ANAT2_FromDate
)
VALUES
(2, 'Train', '2008-01-01');
INSERT INTO ANAT2_AnchorAttribute2 (
AN_ID,
ANAT2_AnchorAttribute2Value,
ANAT2_FromDate
)
VALUES
(2, 'Car', '2008-10-12');
INSERT INTO ANAT2_AnchorAttribute2 (
AN_ID,
ANAT2_AnchorAttribute2Value,
ANAT2_FromDate
)
VALUES
(3, 'Train', '2007-01-01');
SELECT *
FROM lAN_Anchor;
EXPLAIN SELECT *
FROM lAN_Anchor;
SELECT ANAT1_AnchorAttribute1Value
FROM lAN_Anchor;
EXPLAIN SELECT ANAT1_AnchorAttribute1Value
FROM lAN_Anchor;
SELECT ANAT2_AnchorAttribute2Value
FROM lAN_Anchor;
EXPLAIN SELECT ANAT2_AnchorAttribute2Value
FROM lAN_Anchor;