Mercurial > dive4elements > gnv-client

comparison gnv-artifacts/src/test/ressources/queries.properties @ 87:ce398a7a99fc

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SQL-Queries formatted, Some Bugs fixed gnv-artifacts/trunk@124 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Thu, 24 Sep 2009 10:05:07 +0000
parents dbd141c6bb97
children 994a39b084d6
comparison
equal deleted inserted replaced
86:5d4f5d26bb7a 87:ce398a7a99fc
2 ############################################# 2 #############################################
3 ########## Zeitserie ############## 3 ########## Zeitserie ##############
4 ############################################# 4 #############################################
5 ############################################# 5 #############################################
6 6
7 timeseries_timeseriespoint=SELECT DISTINCT tsp.FEATUREID KEY, tsp.NAME VALUE FROM MEDIAN.TIMESERIESPOINT tsp, MEDIAN.MEASUREMENT mmt WHERE tsp.FEATUREID = mmt.FEATUREID AND mmt.SOURCEID = ? order by tsp.name 7 timeseries_timeseriespoint=SELECT DISTINCT \
8 timeseries_parameter=SELECT DISTINCT p.PARAMETERID KEY, p.GERMANNAME VALUE from MEDIAN.PARAMETER p where p.PARAMETERID in (select distinct ts.PARAMETERID from MEDIAN.TIMESERIES ts where ts.TIMESERIESID in (select distinct tsv.TIMESERIESID from MEDIAN.TIMESERIESVALUE tsv where tsv.MEASUREMENTID in (select m.MEASUREMENTID from MEDIAN.MEASUREMENT m, MEDIAN.TIMESERIESPOINT tsp where m.FEATUREID = tsp.FEATUREID and tsp.FEATUREID IN ( ? )))) ORDER BY p.GERMANNAME 8 tsp.FEATUREID KEY, \
9 timeseries_depth_height=SELECT DISTINCT m.MEASUREMENTID KEY, m.ZLOCATION VALUE from MEDIAN.MEASUREMENT m where m.MEASUREMENTID in (SELECT DISTINCT t_v.MEASUREMENTID from MEDIAN.TIMESERIESVALUE t_v where t_v.TIMESERIESID in (SELECT DISTINCT t.TIMESERIESID from MEDIAN.TIMESERIES t where t.PARAMETERID in (SELECT DISTINCT p.PARAMETERID from MEDIAN.PARAMETER p where m.FEATUREID IN ( ? ) and p.PARAMETERID IN (?))))ORDER BY m.ZLOCATION DESC 9 tsp.NAME VALUE \
10 timeseries_interval=select min(tv.TIMEVALUE) MIN, max(tv.TIMEVALUE) MAX from MEDIAN.TIMESERIES t , MEDIAN.TIMESERIESVALUE tv where tv.TIMESERIESID = t.TIMESERIESID AND t.PARAMETERID IN ( ? ) AND tv.MEASUREMENTID IN ( ? ) 10 FROM MEDIAN.TIMESERIESPOINT tsp, \
11 timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, tv.DATAVALUE YORDINATE, t.PARAMETERID GROUP1, tv.MEASUREMENTID GROUP2, tv.TIMESERIESID GROUP3 FROM MEDIAN.TIMESERIESVALUE tv, MEDIAN.TIMESERIES t WHERE tv.TIMESERIESID = t.TIMESERIESID AND t.PARAMETERID IN ( ? ) AND tv.MEASUREMENTID IN ( ? ) AND tv.TIMEVALUE > TO_DATE (?, 'YYYY.MM.DD HH24:MI:SS') AND tv.TIMEVALUE < TO_DATE (?, 'YYYY.MM.DD HH24:MI:SS') ORDER BY tv.MEASUREMENTID ,tv.TIMESERIESID ,t.PARAMETERID ,tv.TIMEVALUE 11 MEDIAN.MEASUREMENT mmt \
12 WHERE tsp.FEATUREID = mmt.FEATUREID AND \
13 mmt.SOURCEID = ? \
14 order by tsp.name
15
16 timeseries_parameter=SELECT DISTINCT \
17 p.PARAMETERID KEY, \
18 p.GERMANNAME VALUE \
19 from MEDIAN.PARAMETER p \
20 where p.PARAMETERID in \
21 (select distinct \
22 ts.PARAMETERID \
23 from MEDIAN.TIMESERIES ts \
24 where ts.TIMESERIESID in \
25 (select distinct \
26 tsv.TIMESERIESID \
27 from MEDIAN.TIMESERIESVALUE tsv \
28 where tsv.MEASUREMENTID in \
29 (select m.MEASUREMENTID \
30 from MEDIAN.MEASUREMENT m, \
31 MEDIAN.TIMESERIESPOINT tsp \
32 where m.FEATUREID = tsp.FEATUREID and \
33 tsp.FEATUREID IN ( ? )\
34 )\
35 )\
36 ) \
37 ORDER BY p.GERMANNAME
38
39 timeseries_depth_height=SELECT DISTINCT \
40 m.MEASUREMENTID KEY, \
41 m.ZLOCATION VALUE \
42 from MEDIAN.MEASUREMENT m \
43 where m.MEASUREMENTID in \
44 (SELECT DISTINCT \
45 t_v.MEASUREMENTID \
46 from MEDIAN.TIMESERIESVALUE t_v \
47 where t_v.TIMESERIESID in \
48 (SELECT DISTINCT \
49 t.TIMESERIESID \
50 from MEDIAN.TIMESERIES t \
51 where t.PARAMETERID in \
52 (SELECT DISTINCT \
53 p.PARAMETERID \
54 from MEDIAN.PARAMETER p \
55 where m.FEATUREID IN ( ? ) and \
56 p.PARAMETERID IN (?)\
57 )\
58 )\
59 )\
60 ORDER BY m.ZLOCATION DESC
61
62 timeseries_interval=select min(tv.TIMEVALUE) MIN, \
63 max(tv.TIMEVALUE) MAX \
64 from MEDIAN.TIMESERIES t , \
65 MEDIAN.TIMESERIESVALUE tv \
66 where tv.TIMESERIESID = t.TIMESERIESID AND \
67 t.PARAMETERID IN ( ? ) AND \
68 tv.MEASUREMENTID IN ( ? )
69
70 timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \
71 tv.DATAVALUE YORDINATE, \
72 t.PARAMETERID GROUP1, \
73 tv.MEASUREMENTID GROUP2, \
74 tv.TIMESERIESID GROUP3 \
75 FROM MEDIAN.TIMESERIESVALUE tv, \
76 MEDIAN.TIMESERIES t \
77 WHERE tv.TIMESERIESID = t.TIMESERIESID AND \
78 t.PARAMETERID IN ( ? ) AND \
79 tv.MEASUREMENTID IN ( ? ) AND \
80 tv.TIMEVALUE > TO_DATE ('?', 'YYYY.MM.DD HH24:MI:SS') AND \
81 tv.TIMEVALUE < TO_DATE ('?', 'YYYY.MM.DD HH24:MI:SS') \
82 ORDER BY tv.MEASUREMENTID , \
83 tv.TIMESERIESID , \
84 t.PARAMETERID , \
85 tv.TIMEVALUE
12 86
13 ############################################# 87 #############################################
14 ############################################# 88 #############################################
15 ########## Zeitserie Mesh ############## 89 ########## Zeitserie Mesh ##############
16 ############################################# 90 #############################################
17 ############################################# 91 #############################################
18 92
19 timeseries_mesh = SELECT OBJECTID KEY, m.NAME VALUE FROM MEDIAN.MESH m order by m.NAME 93 timeseries_mesh = SELECT OBJECTID KEY, \
20 timeseries_meshpoint = SELECT FEATUREID KEY, SHAPE VALUE FROM MEDIAN.MESHPOINT mp , MEDIAN.MESH m WHERE m.OBJECTID = ? AND mp.MESHID = m.MESHID AND KPOSITION = 1 AND rownum < 10 94 m.NAME VALUE \
95 FROM MEDIAN.MESH m \
96 order by m.NAME
97 timeseries_meshpoint = SELECT FEATUREID KEY, \
98 SHAPE VALUE \
99 FROM MEDIAN.MESHPOINT mp , \
100 MEDIAN.MESH m \
101 WHERE m.OBJECTID = ? AND \
102 mp.MESHID = m.MESHID AND \
103 KPOSITION = 1 AND \
104 rownum < 10
105
21 timeseries_meshpoint_depth = select mp.FEATUREID KEY, \ 106 timeseries_meshpoint_depth = select mp.FEATUREID KEY, \
22 -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \ 107 -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \
23 from MEDIAN.MESHLAYER ml, \ 108 from MEDIAN.MESHLAYER ml, \
24 MEDIAN.MESHPOINT mp \ 109 MEDIAN.MESHPOINT mp \
25 where ml.KPOSITION = mp.KPOSITION and \ 110 where ml.KPOSITION = mp.KPOSITION and \
31 where m.OBJECTID = ? AND \ 116 where m.OBJECTID = ? AND \
32 mp.MESHID = m.MESHID AND \ 117 mp.MESHID = m.MESHID AND \
33 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ 118 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
34 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ 119 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
35 order by ml.UPPERZLOCATION desc 120 order by ml.UPPERZLOCATION desc
36 timeseries_mesh_parameter=SELECT distinct p.PARAMETERID KEY , p.GERMANNAME VALUE from MEDIAN.PARAMETER p, MEDIAN.MESHSCALARVALUE msc, MEDIAN.MESH m where m.OBJECTID = ? AND msc.PARTID = m.PARTIDMIN AND msc.PARAMETERID = p.PARAMETERID 121
37 timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ min(TIMEVALUE) MIN, max(TIMEVALUE) MAX from MEDIAN.MESHSCALARVALUE msc , MEDIAN.MESH m where m.OBJECTID = ? AND msc.PARTID >= m.PARTIDMIN AND msc.PARTID <= m.PARTIDMAX 122 timeseries_mesh_parameter=SELECT distinct \
38 timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ msv.TIMEVALUE XORDINATE, msv.DATAVALUE YORDINATE, msv.PARAMETERID GROUP1, msv.FEATUREID GROUP2, mp.FEATUREID GROUP3 from MEDIAN.MESHSCALARVALUE msv , MEDIAN.MESHPOINT mp, MEDIAN.MESH m where (m.OBJECTID = ? AND msv.PARTID >= m.PARTIDMIN AND msv.PARTID <= m.PARTIDMAX ) AND msv.FEATUREID in ( ? ) and msv.PARAMETERID in ( ? ) AND mp.FEATUREID = ? and TIMEVALUE >= to_date(?, 'YYYY.MM.DD HH24:MI:SS') and TIMEVALUE <= to_date(?, 'YYYY.MM.DD HH24:MI:SS') order by msv.FEATUREID, msv.PARAMETERID, msv.TIMEVALUE 123 p.PARAMETERID KEY , \
124 p.GERMANNAME VALUE \
125 from MEDIAN.PARAMETER p, \
126 MEDIAN.MESHSCALARVALUE msc, \
127 MEDIAN.MESH m \
128 where m.OBJECTID = ? AND \
129 msc.PARTID = m.PARTIDMIN AND \
130 msc.PARAMETERID = p.PARAMETERID \
131 order by p.GERMANNAME
132 timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \
133 min(TIMEVALUE) MIN, \
134 max(TIMEVALUE) MAX \
135 from MEDIAN.MESHSCALARVALUE msc , \
136 MEDIAN.MESH m \
137 where m.OBJECTID = ? AND \
138 msc.PARTID >= m.PARTIDMIN AND \
139 msc.PARTID <= m.PARTIDMAX
140
141 timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \
142 msv.TIMEVALUE XORDINATE, \
143 msv.DATAVALUE YORDINATE, \
144 msv.PARAMETERID GROUP1, \
145 msv.FEATUREID GROUP2, \
146 mp.FEATUREID GROUP3 \
147 from MEDIAN.MESHSCALARVALUE msv , \
148 MEDIAN.MESHPOINT mp, \
149 MEDIAN.MESH m \
150 where (m.OBJECTID = ? AND \
151 msv.PARTID >= m.PARTIDMIN AND \
152 msv.PARTID <= m.PARTIDMAX ) AND \
153 msv.FEATUREID in ( ? ) AND \
154 msv.PARAMETERID in ( ? ) AND \
155 mp.FEATUREID = ? AND \
156 msv.TIMEVALUE >= to_date('?', 'YYYY.MM.DD HH24:MI:SS') AND \
157 msv.TIMEVALUE <= to_date('?', 'YYYY.MM.DD HH24:MI:SS') \
158 order by msv.FEATUREID, \
159 msv.PARAMETERID, \
160 msv.TIMEVALUE
39 161
40 ############################################# 162 #############################################
41 ############################################# 163 #############################################
42 ########## Vertikalprofil ############## 164 ########## Vertikalprofil ##############
43 ############################################# 165 #############################################
http://dive4elements.wald.intevation.org