Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle.sql @ 3335:c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
flys-backend/trunk@4834 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Fri, 29 Jun 2012 11:24:50 +0000 |
| parents | 9c2424073be0 |
| children | e19a503e4150 |
comparison
equal
deleted
inserted
replaced
| 3334:2ae732e2c65c | 3335:c3e049961685 |
|---|---|
| 410 FROM wst_column_q_ranges wcqr | 410 FROM wst_column_q_ranges wcqr |
| 411 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id | 411 JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id |
| 412 JOIN ranges r ON wqr.range_id = r.id | 412 JOIN ranges r ON wqr.range_id = r.id |
| 413 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id | 413 JOIN wst_columns wc ON wcqr.wst_column_id = wc.id |
| 414 ORDER BY wc.position, wcqr.wst_column_id, r.a; | 414 ORDER BY wc.position, wcqr.wst_column_id, r.a; |
| 415 | |
| 416 -- Views to make the 'Amtlichen Linien' easier to access. | |
| 417 | |
| 418 CREATE VIEW official_lines | |
| 419 AS | |
| 420 SELECT w.river_id AS river_id, | |
| 421 w.id AS wst_id, | |
| 422 wc.id AS wst_column_id, | |
| 423 wc.name AS name | |
| 424 FROM wsts w | |
| 425 JOIN wst_columns wc | |
| 426 ON wc.wst_id = w.id | |
| 427 WHERE w.kind = 3; | |
| 428 | |
| 429 CREATE VIEW q_main_values | |
| 430 AS | |
| 431 SELECT riv.id AS river_id, | |
| 432 g.id AS gauge_id, | |
| 433 g.name AS gauge_name, | |
| 434 r.a AS a, | |
| 435 r.b AS b, | |
| 436 REGEXP_REPLACE( | |
| 437 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name, | |
| 438 CAST(mv.value AS NUMERIC(38, 2)) AS value | |
| 439 FROM main_values mv | |
| 440 JOIN named_main_values nmv | |
| 441 ON mv.named_value_id = nmv.id | |
| 442 JOIN main_value_types mvt | |
| 443 ON nmv.type_id = mvt.id | |
| 444 JOIN gauges g | |
| 445 ON mv.gauge_id = g.id | |
| 446 JOIN ranges r | |
| 447 ON g.range_id = r.id | |
| 448 JOIN rivers riv | |
| 449 ON g.river_id = riv.id | |
| 450 WHERE mvt.name = 'Q' | |
| 451 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); | |
| 452 | |
| 453 CREATE VIEW official_q_values | |
| 454 AS | |
| 455 SELECT ol.river_id AS river_id, | |
| 456 wst_id, | |
| 457 wst_column_id, | |
| 458 gauge_id, | |
| 459 gauge_name, | |
| 460 a, | |
| 461 b, | |
| 462 ol.name, | |
| 463 value | |
| 464 FROM official_lines ol | |
| 465 JOIN q_main_values qmv | |
| 466 ON ol.river_id = qmv.river_id | |
| 467 AND ol.name = qmv.name; |