Mercurial > dive4elements > river

annotate etl/src/main/resources/sql/aft-common.properties @ 7151:491b645123b6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
issue1493: Also transform NaN X values to empty string.
author Felix Wolfsteller <felix.wolfsteller@intevation.de>
date Wed, 25 Sep 2013 16:28:23 +0200
parents 5aa05a7a34b7
children
rev   line source
5188
2b7f44c80857 Stations are now filtered by DiPS not AFT.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
1 select.gewaesser = \
2b7f44c80857 Stations are now filtered by DiPS not AFT.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
2 SELECT GEWAESSER_NR, NAME FROM SL_GEWAESSER
4754
04377ccef10a AFT: Reject AFT gauges which are not inside the calculation range of the corresponding FLYS river.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4091
diff changeset
3 select.messstelle = \
5188
2b7f44c80857 Stations are now filtered by DiPS not AFT.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
4 SELECT NAME, MESSSTELLE_NR, STATIONIERUNG \
4754
04377ccef10a AFT: Reject AFT gauges which are not inside the calculation range of the corresponding FLYS river.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4091
diff changeset
5 FROM MESSSTELLE \
5188
2b7f44c80857 Stations are now filtered by DiPS not AFT.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
6 WHERE GEWAESSER_NR = :GEWAESSER_NR
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: 4774
diff changeset
7 select.abflusstafel = \
d9f1202cef78 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4774
diff changeset
8 SELECT ABFLUSSTAFEL_NR, \
d9f1202cef78 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4774
diff changeset
9 ABFLUSSTAFEL_BEZ, \
d9f1202cef78 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4774
diff changeset
10 strftime('%s', GUELTIG_VON) * 1000 AS GUELTIG_VON, \
d9f1202cef78 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4774
diff changeset
11 strftime('%s', GUELTIG_BIS) * 1000 AS GUELTIG_BIS, \
d9f1202cef78 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4774
diff changeset
12 PEGELNULLPUNKT, \
d9f1202cef78 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4774
diff changeset
13 BFG_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: 4774
diff changeset
14 FROM ABFLUSSTAFEL \
d9f1202cef78 Do the pairing of FLYS and AFT discharge tables by the field 'ABFLUSSTAFEL.BFG_ID' in
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4774
diff changeset
15 WHERE MESSSTELLE_NR LIKE :number
5188
2b7f44c80857 Stations are now filtered by DiPS not AFT.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
16 select.tafelwert = \
2b7f44c80857 Stations are now filtered by DiPS not AFT.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
17 SELECT TAFELWERT_NR AS id, WASSERSTAND AS w, ABFLUSS AS q FROM TAFELWERT \
2b7f44c80857 Stations are now filtered by DiPS not AFT.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 4775
diff changeset
18 WHERE ABFLUSSTAFEL_NR = :number
5258
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5188
diff changeset
19 select.bfg.id.current = \
5480
7a68967ca72a AFT/DIPS: Small fixes and workarounds for Oracle DB problems.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5258
diff changeset
20 SELECT ABT.BFG_ID AS BFG_ID FROM ABFLUSSTAFEL ABT \
5258
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5188
diff changeset
21 WHERE GUELTIG_VON IN ( \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5188
diff changeset
22 SELECT min(GUELTIG_VON) FROM ABFLUSSTAFEL \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5188
diff changeset
23 WHERE GUELTIG_VON IS NOT NULL AND GUELTIG_BIS IS NULL \
da1e897c7224 Set the BFG_ID for current discharge tables in FLYS.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5188
diff changeset
24 AND MESSSTELLE_NR LIKE :number) \
5480
7a68967ca72a AFT/DIPS: Small fixes and workarounds for Oracle DB problems.
Sascha L. Teichmann <teichmann@intevation.de>
parents: 5258
diff changeset
25 AND MESSSTELLE_NR LIKE :number
http://dive4elements.wald.intevation.org