Mercurial > dive4elements > gnv-client
view gnv-artifacts/src/test/ressources/queries.properties @ 88:1b12021905b9
Some CodeCleanup done.
ExceptionDocument will now be returned
gnv-artifacts/trunk@125 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Tim Englich <tim.englich@intevation.de> |
---|---|
date | Thu, 24 Sep 2009 10:45:24 +0000 |
parents | ce398a7a99fc |
children | 994a39b084d6 |
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 > 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 ############################################# ############################################# ########## Zeitserie Mesh ############## ############################################# ############################################# timeseries_mesh = SELECT OBJECTID KEY, \ m.NAME VALUE \ FROM MEDIAN.MESH m \ order by m.NAME 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 timeseries_meshpoint_depth = select mp.FEATUREID KEY, \ -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 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 \ msv.TIMEVALUE >= to_date('?', 'YYYY.MM.DD HH24:MI:SS') AND \ msv.TIMEVALUE <= to_date('?', 'YYYY.MM.DD HH24:MI:SS') \ order by 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 \ order by m.NAME verticalprofile_mesh_point = SELECT FEATUREID KEY, \ SHAPE VALUE \ FROM MEDIAN.MESHPOINT mp , \ MEDIAN.MESH m \ WHERE m.OBJECTID = ? AND \ m.MESHID = mp.MESHID AND \ mp.KPOSITION = 1 AND \ rownum < 10 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 \ 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 S.SERIESID KEY, \ S.DESCRIPTION VALUE \ FROM MEDIAN.SERIES S verticalprofile_instantaneouspoint_point = SELECT IP.FEATUREID KEY, \ IP.SHAPE VALUE \ FROM MEDIAN.INSTANTANEOUSPOINT IP \ WHERE IP.POINTSPEC = 4 AND \ IP.SERIESID = ? 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