# HG changeset patch
# User Bjoern Schilberg <bjoern@intevation.de>
# Date 1319017579 0
# Node ID cf8dcfaeb756577cc32b3ffee55560ec42897331
# Parent  40093215061b5a07a8ef9d22abcd41e8d30f92ef
Added missing views (wst_value_table,wst_w_values,wst_q_values).



flys-backend/trunk@3024 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 40093215061b -r cf8dcfaeb756 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Tue Oct 18 10:25:00 2011 +0000
+++ b/flys-backend/ChangeLog	Wed Oct 19 09:46:19 2011 +0000
@@ -1,3 +1,8 @@
+2011-10-19  Bjoern Schilberg <bjoern.schilberg@intevation.de>
+
+	* doc/schema/oracle.sql:
+	  Added missing views (wst_value_table,wst_w_values,wst_q_values).
+
 2011-10-18  Bjoern Schilberg <bjoern.schilberg@intevation.de>
 
 	* doc/schema/oracle.sql:
diff -r 40093215061b -r cf8dcfaeb756 flys-backend/doc/schema/oracle.sql
--- a/flys-backend/doc/schema/oracle.sql	Tue Oct 18 10:25:00 2011 +0000
+++ b/flys-backend/doc/schema/oracle.sql	Wed Oct 19 09:46:19 2011 +0000
@@ -364,3 +364,50 @@
 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;
+
+-- VIEWS
+
+CREATE VIEW wst_value_table AS
+    SELECT wcv.position AS position,
+           w,
+           (SELECT q
+            FROM   wst_column_q_ranges wcqr
+                   JOIN wst_q_ranges wqr
+                     ON wcqr.wst_q_range_id = wqr.id
+                   JOIN ranges r
+                     ON r.id = wqr.range_id
+            WHERE  wcqr.wst_column_id = wc.id
+                   AND wcv.position BETWEEN r.a AND r.b) AS q,
+           wc.position                                   AS column_pos,
+           w.id                                          AS wst_id
+    FROM   wst_column_values wcv
+           JOIN wst_columns wc
+             ON wcv.wst_column_id = wc.id
+           JOIN wsts w
+             ON wc.wst_id = w.id
+    ORDER  BY wcv.position ASC,
+          wc.position DESC;
+
+-- view to select the w values of a WST
+CREATE VIEW wst_w_values  AS
+    SELECT wcv.position   AS km, 
+           wcv.w          AS w,  
+           wc.position    AS column_pos, 
+           w.id           AS wst_id
+        FROM wst_column_values wcv
+        JOIN wst_columns wc ON wcv.wst_column_id = wc.id
+        JOIN wsts w         ON wc.wst_id = w.id
+    ORDER BY wcv.position, wc.position;
+
+-- view to select the q values of a WST
+CREATE VIEW wst_q_values AS
+    SELECT wc.position AS column_pos,
+           wqr.q       AS q, 
+           r.a         AS a, 
+           r.b         AS b,
+           wc.wst_id   AS wst_id
+    FROM wst_column_q_ranges wcqr
+    JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
+    JOIN ranges r         ON wqr.range_id        = r.id
+    JOIN wst_columns wc   ON wcqr.wst_column_id  = wc.id
+    ORDER BY wc.position, wcqr.wst_column_id, r.a;