Mercurial > dive4elements > river
annotate etl/src/main/resources/sql/flys-common.properties @ 8493:07a369e6e277
Correct header for release script.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 04 Dec 2014 11:38:18 +0100 |
parents | e5675e85ac38 |
children |
rev | line source |
---|---|
4753
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
1 select.rivers = \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
2 SELECT r.id AS id, r.name AS name, min(wcv.position) AS min_km, max(wcv.position) AS max_km \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
3 FROM rivers r \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
4 JOIN wsts w ON r.id = w.river_id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
5 JOIN wst_columns wc ON w.id = wc.wst_id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
6 JOIN wst_column_values wcv ON wcv.wst_column_id = wc.id \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
7 WHERE w.kind = 0 \ |
a310aceb2e51
Fetch calculation range (Berechnungsstrecke) for FLYS rivers, too.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4109
diff
changeset
|
8 GROUP BY r.id, r.name |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
9 select.gauges = \ |
6895
e5675e85ac38
Potential fix for flys/1453.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
6894
diff
changeset
|
10 SELECT id, name, official_number, station \ |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
11 FROM gauges \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
12 WHERE river_id = :river_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
13 next.gauge.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
14 SELECT NEXTVAL('GAUGES_ID_SEQ') AS gauge_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
15 insert.gauge = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
16 INSERT INTO gauges (id, name, river_id, station, aeo, official_number, datum) \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
17 VALUES(:id, :name, :river_id, :station, :aeo, :official_number, :datum) |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
18 select.timeintervals = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
19 SELECT id, start_time, stop_time FROM time_intervals |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
20 next.timeinterval.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
21 SELECT NEXTVAL('TIME_INTERVALS_ID_SEQ') AS time_interval_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
22 insert.timeinterval = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
23 INSERT INTO time_intervals (id, start_time, stop_time) \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
24 VALUES (:id, :start_time, :stop_time) |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
25 next.discharge.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
26 SELECT NEXTVAL('DISCHARGE_TABLES_ID_SEQ') AS discharge_table_id |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
27 insert.dischargetable = \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
28 INSERT INTO discharge_tables \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
29 (id, gauge_id, description, bfg_id, kind, time_interval_id) \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
30 VALUES (:id, :gauge_id, :description, :bfg_id, 1, :time_interval_id) |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
31 select.discharge.table.values = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
32 SELECT id, w, q FROM discharge_table_values WHERE table_id = :table_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
33 next.discharge.table.values.id = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
34 SELECT NEXTVAL('DISCHARGE_TABLE_VALUES_ID_SEQ') AS discharge_table_values_id |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
35 insert.discharge.table.value = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
36 INSERT INTO discharge_table_values (id, table_id, w, q) \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
37 VALUES (:id, :table_id, :w, :q) |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
38 delete.discharge.table.value = \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
39 DELETE FROM discharge_table_values WHERE id = :id |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
40 select.gauge.discharge.tables = \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
41 SELECT \ |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
42 dt.id AS id, \ |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
43 dt.description AS description, \ |
5257
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
44 ti.start_time AS start_time, \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
45 ti.stop_time AS stop_time, \ |
46e984a3d576
Break long lines in FLYS statements.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4775
diff
changeset
|
46 dt.bfg_id AS bfg_id \ |
4775
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
47 FROM discharge_tables dt \ |
d9f1202cef78
Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents:
4753
diff
changeset
|
48 LEFT OUTER JOIN time_intervals ti ON dt.time_interval_id = ti.id \ |
5995
3bb6c2a3ef72
River ETL: Limit the discharge tables to load to the historical (kind = 1) ones. This prevents the wrong tables (values) to be deleted.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5930
diff
changeset
|
49 WHERE dt.gauge_id = :gauge_id AND dt.kind = 1 |
5258
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
50 select.gauge.master.discharge.table = \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
51 SELECT \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
52 dt.id AS id, \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
53 dt.bfg_id AS bfg_id \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
54 FROM discharge_tables dt JOIN gauges g ON dt.gauge_id = g.id \ |
5479
11fba4dabe05
AFT/DIPS: Fixed wrond SQL statement.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5258
diff
changeset
|
55 WHERE g.id = :gauge_id AND dt.kind = 0 |
5258
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
56 update.bfg.id.discharge.table = \ |
da1e897c7224
Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5257
diff
changeset
|
57 UPDATE discharge_tables SET bfg_id = :bfg_id WHERE id = :id |
5930
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
58 delete.discharge.table.values = \ |
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
59 DELETE from discharge_table_values where table_id = :id |
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
60 delete.discharge.table = \ |
33e514a0c6b7
Delete discharge tables which are in AFT but not in FLYS. Fixed Oracle specific pom.xml
Sascha L. Teichmann <teichmann@intevation.de>
parents:
5838
diff
changeset
|
61 DELETE from discharge_tables where id = :id and kind = 1 |