Mercurial > dive4elements > river
comparison backend/doc/schema/oracle.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 |
comparison
equal
deleted
inserted
replaced
| 6076:c97d003fd127 | 6078:176664f84d86 |
|---|---|
| 412 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; | 412 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds; |
| 413 | 413 |
| 414 -- VIEWS | 414 -- VIEWS |
| 415 | 415 |
| 416 CREATE VIEW wst_value_table AS | 416 CREATE VIEW wst_value_table AS |
| 417 SELECT wcv.position AS position, | 417 SELECT |
| 418 w, | 418 wcv.position AS position, |
| 419 (SELECT q | 419 w, |
| 420 FROM wst_column_q_ranges wcqr | 420 q, |
| 421 JOIN wst_q_ranges wqr | 421 wc.position AS column_pos, |
| 422 ON wcqr.wst_q_range_id = wqr.id | 422 w.id AS wst_id |
| 423 JOIN ranges r | 423 FROM wsts w |
| 424 ON r.id = wqr.range_id | 424 JOIN wst_columns wc |
| 425 WHERE wcqr.wst_column_id = wc.id | 425 ON wc.wst_id=w.id |
| 426 AND wcv.position BETWEEN r.a AND r.b) AS q, | 426 JOIN wst_column_q_ranges wcqr |
| 427 wc.position AS column_pos, | 427 ON wcqr.wst_column_id=wc.id |
| 428 w.id AS wst_id | 428 JOIN wst_q_ranges wqr |
| 429 FROM wst_column_values wcv | 429 ON wcqr.wst_q_range_id=wqr.id |
| 430 JOIN wst_columns wc | 430 JOIN ranges r |
| 431 ON wcv.wst_column_id = wc.id | 431 ON wqr.range_id=r.id |
| 432 JOIN wsts w | 432 JOIN wst_column_values wcv |
| 433 ON wc.wst_id = w.id | 433 ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b; |
| 434 ORDER BY wcv.position ASC, | 434 ORDER BY wcv.position ASC, |
| 435 wc.position DESC; | 435 wc.position DESC; |
| 436 | 436 |
| 437 -- view to select the w values of a WST | 437 -- view to select the w values of a WST |
| 438 CREATE VIEW wst_w_values AS | 438 CREATE VIEW wst_w_values AS |
| 439 SELECT wcv.position AS km, | 439 SELECT wcv.position AS km, |
| 440 wcv.w AS w, | 440 wcv.w AS w, |