Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  1. -- Test run on DB2 9.5.2 (2009-05-04)
  2.  
  3. DROP TABLE AN_Anchor;
  4.  
  5. CREATE TABLE AN_Anchor (
  6.     AN_ID INT NOT NULL,
  7.     PRIMARY KEY(AN_ID)
  8. );
  9.  
  10. DROP TABLE ANAT1_AnchorAttribute1;
  11.  
  12. CREATE TABLE ANAT1_AnchorAttribute1 (
  13.     AN_ID INT NOT NULL,
  14.     ANAT1_AnchorAttribute1Value CHAR(10) NOT NULL,
  15.     PRIMARY KEY(AN_ID)
  16. );
  17.  
  18. DROP TABLE ANAT2_AnchorAttribute2;
  19.  
  20. CREATE TABLE ANAT2_AnchorAttribute2 (
  21.     AN_ID INT NOT NULL,
  22.     ANAT2_AnchorAttribute2Value CHAR(10) NOT NULL,
  23.     ANAT2_FromDate DATE NOT NULL,
  24.     PRIMARY KEY(AN_ID, ANAT2_FromDate)
  25. );
  26.  
  27. DROP VIEW lAN_Anchor;
  28.  
  29. CREATE VIEW lAN_Anchor
  30. AS
  31. SELECT
  32.     AN.AN_ID,
  33.     (
  34.         SELECT
  35.             ANAT1.ANAT1_AnchorAttribute1Value
  36.         FROM
  37.             ANAT1_AnchorAttribute1 ANAT1
  38.         WHERE
  39.             ANAT1.AN_ID = AN.AN_ID
  40.     ) ANAT1_AnchorAttribute1Value,
  41.     (
  42.         SELECT
  43.             ANAT2.ANAT2_AnchorAttribute2Value
  44.         FROM
  45.             ANAT2_AnchorAttribute2 ANAT2
  46.         WHERE
  47.             ANAT2.AN_ID = AN.AN_ID
  48.         AND
  49.             ANAT2.ANAT2_FromDate = (
  50.                 SELECT
  51.                     MAX(sub.ANAT2_FromDate)
  52.                 FROM
  53.                     ANAT2_AnchorAttribute2 sub
  54.                 WHERE
  55.                     sub.AN_ID = AN.AN_ID
  56.             )
  57.     ) ANAT2_AnchorAttribute2Value
  58. FROM
  59.     AN_Anchor AN;
  60.  
  61. INSERT INTO AN_Anchor (
  62.     AN_ID
  63. )
  64. VALUES
  65. (1),
  66. (2),
  67. (3);
  68.  
  69. RUNSTATS ON TABLE lro2.AN_Anchor
  70. ON KEY COLUMNS AND INDEXES ALL;
  71.  
  72. INSERT INTO ANAT1_AnchorAttribute1 (
  73.     AN_ID,
  74.     ANAT1_AnchorAttribute1Value
  75. )
  76. VALUES
  77. (1, 'Green'),
  78. (2, 'Blue'),
  79. (3, 'Red');
  80.  
  81. RUNSTATS ON TABLE lro2.ANAT1_AnchorAttribute1
  82. ON KEY COLUMNS AND INDEXES ALL;
  83.  
  84. INSERT INTO ANAT2_AnchorAttribute2 (
  85.     AN_ID,
  86.     ANAT2_AnchorAttribute2Value,
  87.     ANAT2_FromDate
  88. )
  89. VALUES
  90. (1, 'Car', '2008-01-01'),
  91. (1, 'Bus', '2008-12-12'),
  92. (2, 'Train', '2008-01-01'),
  93. (2, 'Car', '2008-12-12'),
  94. (3, 'Train', '2008-01-01'),
  95. (3, 'Bus', '2008-12-12');
  96.  
  97. RUNSTATS ON TABLE lro2.ANAT2_AnchorAttribute2
  98. ON KEY COLUMNS AND INDEXES ALL;
  99.  
  100. SELECT * FROM lAN_Anchor;
  101.  
  102. SELECT ANAT1_AnchorAttribute1Value FROM lAN_Anchor;
  103.  
  104. SELECT ANAT2_AnchorAttribute2Value FROM lAN_Anchor;