Mercurial > dive4elements > gnv-client
view gnv-artifacts/src/test/ressources/queries.properties @ 192:17987acf20d0
Correct misspelled Exception-Message as an Result of fixing issue35
gnv-artifacts/trunk@240 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Tim Englich <tim.englich@intevation.de> |
---|---|
date | Mon, 19 Oct 2009 10:25:54 +0000 |
parents | d436c8b27ae3 |
children | 60c61f323f16 |
line wrap: on
line source
############################################# ############################################# ########## Zeitserie ############## ############################################# ############################################# 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 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 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 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 ( ? ) 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 > ? AND \ tv.TIMEVALUE < ? \ ORDER BY tv.MEASUREMENTID , \ tv.TIMESERIESID , \ t.PARAMETERID , \ tv.TIMEVALUE ############################################# ############################################# ########## Zeitserie Mesh ############## ############################################# ############################################# timeseries_mesh = SELECT OBJECTID KEY, \ m.NAME VALUE \ FROM MEDIAN.MESH m \ WHERE SOURCEID IN (?) \ ORDER BY m.NAME timeseries_meshpoint = SELECT FEATUREID, \ ST_ASTEXT(SHAPE) \ FROM MEDIAN.MESHPOINT \ WHERE MESHID in \ (SELECT DISTINCT MESHID \ FROM MEDIAN.MESH \ WHERE OBJECTID = ?) AND \ KPOSITION = 1 AND \ INTERSECTS(SHAPE,"?") timeseries_meshpoint_depth = select mp.FEATUREID KEY, \ 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \ from MEDIAN.MESHLAYER ml, \ MEDIAN.MESHPOINT mp \ where ml.KPOSITION = mp.KPOSITION and \ ml.MESHID = mp.MESHID and \ mp.FEATUREID in \ ( select FEATUREID \ from MEDIAN.MESHPOINT mp, \ MEDIAN.MESH m \ where m.OBJECTID = ? AND \ mp.MESHID = m.MESHID AND \ IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ order by ml.UPPERZLOCATION desc 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 \ order by p.GERMANNAME 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 AND \ msc.PARAMETERID IN (?) 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 in ( ? ) AND \ msv.TIMEVALUE >= ? AND \ msv.TIMEVALUE <= ? \ order by mp.FEATUREID, \ msv.FEATUREID, \ msv.PARAMETERID, \ msv.TIMEVALUE ############################################# ############################################# ########## Vertikalprofil ############## ############################################# ############################################# verticalprofile_point=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 verticalprofile_parameter=SELECT DISTINCT \ p.PARAMETERID KEY, \ p.GERMANNAME VALUE \ from MEDIAN.PARAMETER p, \ MEDIAN.TIMESERIES ts, \ MEDIAN.TIMESERIESVALUE tsv, \ MEDIAN.MEASUREMENT m \ where ts.PARAMETERID = p.PARAMETERID and \ ts.TIMESERIESID = tsv.TIMESERIESID and \ m.MEASUREMENTID = tsv.MEASUREMENTID and \ m.FEATUREID = ? \ ORDER BY p.GERMANNAME verticalprofile_date=select distinct \ tsv.TIMEVALUE KEY, \ tsv.TIMEVALUE VALUE \ from MEDIAN.TIMESERIES ts , \ MEDIAN.TIMESERIESVALUE tsv, \ MEDIAN.MEASUREMENT m \ where ts.TIMESERIESID = tsv.TIMESERIESID and \ m.MEASUREMENTID = tsv.MEASUREMENTID and \ m.FEATUREID = ? and \ ts.PARAMETERID IN ( ? ) \ order by tsv.TIMEVALUE verticalprofile_chart_data= SELECT m.ZLOCATION XORDINATE, \ tsv.DATAVALUE YORDINATE, \ ts.PARAMETERID GROUP1, \ tsv.TIMEVALUE GROUP2, \ 1 GROUP3 \ from MEDIAN.TIMESERIES ts, \ MEDIAN.TIMESERIESVALUE tsv, \ MEDIAN.MEASUREMENT m \ where ts.TIMESERIESID = tsv.TIMESERIESID and \ m.MEASUREMENTID = tsv.MEASUREMENTID and \ m.FEATUREID = ? and \ ts.PARAMETERID IN ( ? ) AND \ tsv.TIMEVALUE IN (?) \ ORDER BY tsv.TIMEVALUE , \ tsv.TIMESERIESID , \ ts.PARAMETERID , \ tsv.TIMEVALUE ############################################# ############################################# ########## Vertikalprofil Mesh ############## ############################################# ############################################# verticalprofile_mesh = SELECT OBJECTID KEY, \ m.NAME VALUE \ FROM MEDIAN.MESH m \ WHERE SOURCEID IN (?) \ order by m.NAME verticalprofile_mesh_point = SELECT FEATUREID, \ ST_ASTEXT(SHAPE) \ FROM MEDIAN.MESHPOINT \ WHERE MESHID in \ (SELECT DISTINCT MESHID \ FROM MEDIAN.MESH \ WHERE OBJECTID = ?) AND \ KPOSITION = 1 AND \ INTERSECTS(SHAPE,"?") verticalprofile_mesh_parameter=SELECT distinct \ p.PARAMETERID KEY , \ p.GERMANNAME VALUE \ from MEDIAN.PARAMETER p, \ MEDIAN.MESHSCALARVALUE msc, \ MEDIAN.MESH m \ where m.OBJECTID = ? AND \ m.PARTIDMIN = msc.PARTID AND \ msc.PARAMETERID = p.PARAMETERID \ order by p.GERMANNAME verticalprofile_mesh_date= select distinct \ msv.TIMEVALUE KEY, \ msv.TIMEVALUE VALUE \ from MEDIAN.MESHSCALARVALUE msv , \ MEDIAN.MESH m \ where m.OBJECTID = ? AND \ msv.PARTID >= m.PARTIDMIN AND \ msv.PARTID <= m.PARTIDMAX AND \ msv.PARAMETERID in (?) \ order by msv.TIMEVALUE verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \ msv.DATAVALUE YORDINATE, \ msv.PARAMETERID GROUP1, \ msv.TIMEVALUE GROUP2, \ 1 GROUP3 \ from MEDIAN.MESHLAYER ml, \ MEDIAN.MESHPOINT mp, \ MEDIAN.MESH m, \ MEDIAN.MESHSCALARVALUE msv \ where msv.FEATUREID = mp. FEATUREID AND \ ml.KPOSITION = mp.KPOSITION and \ ml.MESHID = mp.MESHID and \ m.MESHID = mp.MESHID AND \ m.PARTIDMIN <= msv.PARTID AND \ m.PARTIDMAX >= msv.PARTID AND \ msv.PARAMETERID in (?) AND \ msv.TIMEVALUE in (?) AND \ m.OBJECTID = ? AND \ mp.FEATUREID in \ ( select FEATUREID \ from MEDIAN.MESHPOINT mp, \ MEDIAN.MESH m \ where m.OBJECTID = ? AND \ mp.MESHID = m.MESHID AND \ IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ order by msv.PARAMETERID, \ msv.TIMEVALUE, \ ml.UPPERZLOCATION ############################################# ############################################# ##### Vertikalprofil InstantaneousPoint ##### ############################################# ############################################# verticalprofile_instantaneouspoint_series = SELECT DISTINCT \ S.SERIESID KEY , \ S.DESCRIPTION VALUE \ FROM MEDIAN.SERIES S, \ MEDIAN.INSTANTANEOUSPOINT I, \ MEDIAN.MEASUREMENT M \ WHERE S.SERIESID = I.SERIESID AND \ I.POINTSPEC = 4 AND \ I.FEATUREID = M.FEATUREID AND \ M.SOURCEID= ? \ ORDER BY S.DESCRIPTION verticalprofile_instantaneouspoint_point = SELECT DISTINCT \ I.FEATUREID KEY, \ to_char(I.TIMEVALUE,'DD.MM.YYYY HH24:MI') || ' - '|| I.SHAPE VALUE, \ I.TIMEVALUE \ FROM MEDIAN.INSTANTANEOUSPOINT I, \ MEDIAN.MEASUREMENT M \ WHERE I.SERIESID = ? AND \ I.POINTSPEC = 4 AND \ I.FEATUREID = M.FEATUREID AND \ M.SOURCEID = ? \ ORDER BY I.TIMEVALUE verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \ P.PARAMETERID KEY , \ P.GERMANNAME VALUE \ FROM MEDIAN.PARAMETER P, \ MEDIAN.INSTANTANEOUSPOINT IP, \ MEDIAN.MEASUREMENT M, \ MEDIAN.MEASUREDSCALARVALUE MSV \ WHERE IP.FEATUREID = M.FEATUREID AND \ M.MEASUREMENTID = MSV.MEASUREMENTID AND \ MSV.PARAMETERID = P.PARAMETERID AND \ IP.FEATUREID = ? \ ORDER BY P.GERMANNAME verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \ MSV.DATAVALUE YORDINATE, \ MSV.PARAMETERID GROUP1, \ IP.TIMEVALUE GROUP2, \ 1 GROUP3 \ FROM MEDIAN.INSTANTANEOUSPOINT IP, \ MEDIAN.MEASUREMENT M, \ MEDIAN.MEASUREDSCALARVALUE MSV \ WHERE IP.FEATUREID = M.FEATUREID AND \ M.MEASUREMENTID = MSV.MEASUREMENTID AND \ IP.FEATUREID = ? AND \ MSV.PARAMETERID in (?) \ ORDER BY IP.TIMEVALUE, \ MSV.PARAMETERID, \ M.ZLOCATION ############################################# ############################################# #### Horizontalprofil InstantaneousPoint #### ############################################# ############################################# horizontalprofile_instantaneouspoint_vehicle = SELECT DISTINCT \ V.VEHICLEID KEY, \ V.NAME VALUE \ FROM MEDIAN.VEHICLE V, \ MEDIAN.CRUISE C, \ MEDIAN.TRACK T, \ MEDIAN.SURVEYINFO S, \ MEDIAN.INSTANTANEOUSPOINT I, \ MEDIAN.MEASUREMENT M \ WHERE V.VEHICLEID = C.VEHICLEID AND \ C.CRUISEID = T.CRUISEID AND \ T.TRACKID = S.TRACKID AND \ S.SURVEYID = I.SURVEYID AND \ I.FEATUREID = M.FEATUREID AND \ M.SOURCEID = ? \ ORDER BY V.NAME horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \ C.CRUISEID KEY , \ C.NAME VALUE \ FROM MEDIAN.CRUISE C, \ MEDIAN.TRACK T, \ MEDIAN.SURVEYINFO S, \ MEDIAN.INSTANTANEOUSPOINT I, \ MEDIAN.MEASUREMENT M \ WHERE C.CRUISEID = T.CRUISEID AND \ T.TRACKID = S.TRACKID AND \ S.SURVEYID = I.SURVEYID AND \ I.FEATUREID = M.FEATUREID AND \ C.VEHICLEID = ? AND \ M.SOURCEID = ? AND \ C.NAME IS NOT NULL \ ORDER BY C.NAME horizontalprofile_instantaneouspoint_track= SELECT \ T.TRACKID KEY , \ to_char(T.STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(T.ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| T.NAME VALUE \ FROM MEDIAN.TRACK T \ WHERE T.CRUISEID = ? \ ORDER BY T.STARTDATE, \ T.ENDDATE, \ T.NAME horizontalprofile_instantaneouspoint_surveyinfo = SELECT \ SURVEYID KEY , \ to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| DESCRIPTION VALUE \ FROM MEDIAN.SURVEYINFO \ WHERE TRACKID = ? \ ORDER BY STARTDATE, \ ENDDATE, \ DESCRIPTION horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \ P.PARAMETERID KEY , \ P.GERMANNAME VALUE \ FROM MEDIAN.PARAMETER P , \ MEDIAN.MEASUREMENT M , \ MEDIAN.INSTANTANEOUSPOINT IP , \ MEDIAN.MEASUREDSCALARVALUE MSV \ WHERE P.PARAMETERID = MSV.PARAMETERID AND \ MSV.MEASUREMENTID = M.MEASUREMENTID AND \ M.FEATUREID = IP.FEATUREID AND \ IP.SURVEYID = ? \ ORDER BY P.GERMANNAME horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \ M.ZLOCATION KEY, \ M.ZLOCATION VALUE \ FROM MEDIAN.MEASUREMENT M , \ MEDIAN.INSTANTANEOUSPOINT IP \ WHERE M.FEATUREID = IP.FEATUREID AND \ IP.SURVEYID = ? \ ORDER BY M.ZLOCATION horizontalprofile_instantaneouspoint_chart_data = SELECT ST_ASTEXT(SHAPE) , \ MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME , \ MSV.DATAVALUE YORDINATE , \ MSV.PARAMETERID GROUP1 , \ ZLOCATION GROUP2 , \ 1 GROUP3 \ FROM MEDIAN.INSTANTANEOUSPOINT, \ MEDIAN.MEASUREMENT M, \ MEDIAN.MEASUREDSCALARVALUE MSV \ WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ M.MEASUREMENTID = MSV.MEASUREMENTID AND \ MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \ M.ZLOCATION IN (?) AND \ MSV.PARAMETERID in (?) \ ORDER BY MSV.PARAMETERID, \ M.ZLOCATION, \ MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE ############################################# ############################################# ########### Horizontalprofil MESH ########### ############################################# ############################################# horizontalprofile_meshpoint_depth = SELECT DISTINCT \ mp.KPOSITION KEY, \ 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \ from MEDIAN.MESHLAYER ml, \ MEDIAN.MESHPOINT mp \ where ml.KPOSITION = mp.KPOSITION and \ ml.MESHID = mp.MESHID and \ mp.FEATUREID in \ ( select FEATUREID \ from MEDIAN.MESHPOINT mp, \ MEDIAN.MESH m \ where m.OBJECTID = ? AND \ mp.MESHID = m.MESHID AND \ IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ order by mp.KPOSITION horizontalprofile_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \ msv.DATAVALUE YORDINATE, \ msv.PARAMETERID GROUP1, \ MEDIAN.MESHPOINT.KPOSITION GROUP2, \ msv.TIMEVALUE GROUP3, \ MEDIAN.MESHPOINT.JPOSITION , \ MEDIAN.MESHPOINT.IPOSITION \ from MEDIAN.MESHLAYER ml, \ MEDIAN.MESHPOINT , \ MEDIAN.MESH m, \ MEDIAN.MESHSCALARVALUE msv \ where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \ ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ m.PARTIDMIN <= msv.PARTID AND \ m.PARTIDMAX >= msv.PARTID AND \ msv.PARAMETERID in (?) AND \ msv.TIMEVALUE in (?) AND \ m.OBJECTID = ? AND \ MEDIAN.MESHPOINT.FEATUREID in \ ( select FEATUREID \ from MEDIAN.MESHPOINT mp, \ MEDIAN.MESH m \ where m.OBJECTID = ? AND \ mp.MESHID = m.MESHID AND \ KPOSITION in ( ? ) and \ ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \ order by msv.TIMEVALUE, \ MEDIAN.MESHPOINT.KPOSITION, \ msv.PARAMETERID, \ MEDIAN.MESHPOINT.JPOSITION , \ MEDIAN.MESHPOINT.IPOSITION