Mercurial > dive4elements > river
comparison flys-backend/doc/schema/oracle.sql @ 3338:e19a503e4150
Partial fix for flys/issue697: Increased the decimal places of Ws, Qs and Kms to 5.
flys-backend/trunk@4923 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Wed, 11 Jul 2012 08:09:32 +0000 |
| parents | c3e049961685 |
| children | 790c12c55abb |
comparison
equal
deleted
inserted
replaced
| 3337:69f06b83b3ec | 3338:e19a503e4150 |
|---|---|
| 286 -- WST_COLUMN_VALUES | 286 -- WST_COLUMN_VALUES |
| 287 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; | 287 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; |
| 288 | 288 |
| 289 CREATE TABLE wst_column_values ( | 289 CREATE TABLE wst_column_values ( |
| 290 id NUMBER(38,0) NOT NULL, | 290 id NUMBER(38,0) NOT NULL, |
| 291 position NUMBER(38,2), | 291 position NUMBER(38,5), |
| 292 w NUMBER(38,2), | 292 w NUMBER(38,5), |
| 293 wst_column_id NUMBER(38,0), | 293 wst_column_id NUMBER(38,0), |
| 294 PRIMARY KEY (id) | 294 PRIMARY KEY (id) |
| 295 ); | 295 ); |
| 296 | 296 |
| 297 | 297 |
| 312 -- WST_Q_RANGES | 312 -- WST_Q_RANGES |
| 313 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; | 313 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; |
| 314 | 314 |
| 315 CREATE TABLE wst_q_ranges ( | 315 CREATE TABLE wst_q_ranges ( |
| 316 id NUMBER(38,0) NOT NULL, | 316 id NUMBER(38,0) NOT NULL, |
| 317 q NUMBER(38,2), | 317 q NUMBER(38,5), |
| 318 range_id NUMBER(38,0), | 318 range_id NUMBER(38,0), |
| 319 PRIMARY KEY (id) | 319 PRIMARY KEY (id) |
| 320 ); | 320 ); |
| 321 | 321 |
| 322 | 322 |
| 433 g.name AS gauge_name, | 433 g.name AS gauge_name, |
| 434 r.a AS a, | 434 r.a AS a, |
| 435 r.b AS b, | 435 r.b AS b, |
| 436 REGEXP_REPLACE( | 436 REGEXP_REPLACE( |
| 437 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name, | 437 nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name, |
| 438 CAST(mv.value AS NUMERIC(38, 2)) AS value | 438 CAST(mv.value AS NUMERIC(38, 5)) AS value |
| 439 FROM main_values mv | 439 FROM main_values mv |
| 440 JOIN named_main_values nmv | 440 JOIN named_main_values nmv |
| 441 ON mv.named_value_id = nmv.id | 441 ON mv.named_value_id = nmv.id |
| 442 JOIN main_value_types mvt | 442 JOIN main_value_types mvt |
| 443 ON nmv.type_id = mvt.id | 443 ON nmv.type_id = mvt.id |
| 446 JOIN ranges r | 446 JOIN ranges r |
| 447 ON g.range_id = r.id | 447 ON g.range_id = r.id |
| 448 JOIN rivers riv | 448 JOIN rivers riv |
| 449 ON g.river_id = riv.id | 449 ON g.river_id = riv.id |
| 450 WHERE mvt.name = 'Q' | 450 WHERE mvt.name = 'Q' |
| 451 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,2)); | 451 ORDER BY g.id, CAST(mv.value AS NUMERIC(38,5)); |
| 452 | 452 |
| 453 CREATE VIEW official_q_values | 453 CREATE VIEW official_q_values |
| 454 AS | 454 AS |
| 455 SELECT ol.river_id AS river_id, | 455 SELECT ol.river_id AS river_id, |
| 456 wst_id, | 456 wst_id, |