Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle.sql @ 2355:cf8dcfaeb756
Added missing views (wst_value_table,wst_w_values,wst_q_values).
flys-backend/trunk@3024 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Bjoern Schilberg <bjoern@intevation.de> |
|---|---|
| date | Wed, 19 Oct 2011 09:46:19 +0000 |
| parents | 40093215061b |
| children | 418a0918863b |
comparison
equal
deleted
inserted
replaced
| 2354:40093215061b | 2355:cf8dcfaeb756 |
|---|---|
| 362 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns; | 362 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns; |
| 363 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; | 363 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals; |
| 364 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts; | 364 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts; |
| 365 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES; | 365 ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES; |
| 366 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers; | 366 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers; |
| 367 | |
| 368 -- VIEWS | |
| 369 | |
| 370 CREATE VIEW wst_value_table AS | |
| 371 SELECT wcv.position AS position, | |
| 372 w, | |
| 373 (SELECT q | |
| 374 FROM wst_column_q_ranges wcqr | |
| 375 JOIN wst_q_ranges wqr | |
| 376 ON wcqr.wst_q_range_id = wqr.id | |
| 377 JOIN ranges r | |
| 378 ON r.id = wqr.range_id | |
| 379 WHERE wcqr.wst_column_id = wc.id | |
| 380 AND wcv.position BETWEEN r.a AND r.b) AS q, | |
| 381 wc.position AS column_pos, | |
| 382 w.id AS wst_id | |
| 383 FROM wst_column_values wcv | |
| 384 JOIN wst_columns wc | |
| 385 ON wcv.wst_column_id = wc.id | |
| 386 JOIN wsts w | |
| 387 ON wc.wst_id = w.id | |
| 388 ORDER BY wcv.position ASC, | |
| 389 wc.position DESC; | |
| 390 | |
| 391 -- view to select the w values of a WST | |
| 392 CREATE VIEW wst_w_values AS | |
| 393 SELECT wcv.position AS km, | |
| 394 wcv.w AS w, | |
| 395 wc.position AS column_pos, | |
| 396 w.id AS wst_id | |
| 397 FROM wst_column_values wcv | |
| 398 JOIN wst_columns wc ON wcv.wst_column_id = wc.id | |
| 399 JOIN wsts w ON wc.wst_id = w.id | |
| 400 ORDER BY wcv.position, wc.position; | |
| 401 | |
| 402 -- view to select the q values of a WST | |
| 403 CREATE VIEW wst_q_values AS | |
| 404 SELECT wc.position AS column_pos, | |
| 405 wqr.q AS q, | |
| 406 r.a AS a, | |
| 407 r.b AS b, | |
| 408 wc.wst_id AS wst_id | |
| 409 FROM wst_column_q_ranges wcqr | |
| 410 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id | |
| 411 JOIN ranges r ON wqr.range_id = r.id | |
| 412 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id | |
| 413 ORDER BY wc.position, wcqr.wst_column_id, r.a; |