SQL for Data Warehouse view

Continuing with my exploration of Data Warehousing for my project, I'm thinking of generating (and storing?) views according to days (or months): basically snapshots of data at the specified time... but wait, BLOODY HELL!! MySQL 4.x does not support VIEWS or even STORED PROCEDURES!!! I don't know how much of an effort it'd be for my project to upgrade to MySQL 5.0.

Anyways, this was the SQL I intended for my view, given &time (I tested it out and it worked at first go!):

SELECT q1.* FROM 
 
(SELECT * FROM people 
WHERE jira_key NOT IN 
(SELECT jira_key FROM people 
WHERE deleted=1 AND time_stamp <= &time) 
AND time_stamp <= &time) q1,
 
(SELECT jira_key, MAX(time_stamp) AS maxtime FROM 
(SELECT * FROM people 
WHERE jira_key NOT IN 
(SELECT jira_key FROM people 
WHERE deleted=1 AND time_stamp <= &time) 
AND time_stamp <= &time) q2
GROUP BY jira_key) q3
 
WHERE q1.jira_key=q3.jira_key
AND q1.time_stamp=q3.maxtime

Tags: technology:database, technology:coding Last modified 07:16 Sun, 25 Mar 2007 by AmanKing. Accessed 219 times Children What Links Here Share Except where expressly noted, this work is licensed under a Creative Commons License.