Mercurial > dive4elements > river
changeset 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 | c97d003fd127 |
| children | a0dbc60f78ee |
| files | backend/doc/schema/oracle.sql backend/doc/schema/postgresql.sql |
| diffstat | 2 files changed, 36 insertions(+), 36 deletions(-) [+] |
line wrap: on
line diff
--- a/backend/doc/schema/oracle.sql Thu May 23 15:11:05 2013 +0200 +++ b/backend/doc/schema/oracle.sql Thu May 23 17:32:20 2013 +0200 @@ -414,25 +414,25 @@ -- 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 + 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
--- 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