diff backend/doc/schema/postgresql.sql @ 6078:176664f84d86

Schema change: view for WSTs which deales with multiple ranges matching station (issues 1315 and 1296)
author Tom Gottfried <tom.gottfried@intevation.de>
date Thu, 23 May 2013 17:32:20 +0200
parents d12f920bbf00
children 6cdb3ee687a9
line wrap: on
line diff
--- a/backend/doc/schema/postgresql.sql	Thu May 23 15:11:05 2013 +0200
+++ b/backend/doc/schema/postgresql.sql	Thu May 23 17:32:20 2013 +0200
@@ -243,25 +243,25 @@
 );
 
 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
+    SELECT 
+           wcv.position AS position,
+           w, 
+           q,
+           wc.position AS column_pos,
+           w.id AS wst_id
+        FROM wsts w
+        JOIN wst_columns wc
+             ON wc.wst_id=w.id
+        JOIN wst_column_q_ranges wcqr
+             ON wcqr.wst_column_id=wc.id
+        JOIN wst_q_ranges wqr
+             ON wcqr.wst_q_range_id=wqr.id
+        JOIN ranges r
+             ON wqr.range_id=r.id
+        JOIN wst_column_values wcv
+             ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b;
     ORDER  BY wcv.position ASC,
-          wc.position DESC;
+        wc.position DESC;
 
 -- view to select the w values of a WST
 CREATE VIEW wst_w_values AS

http://dive4elements.wald.intevation.org