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; |