Mercurial > dive4elements > gnv-client
diff gnv-artifacts/src/test/ressources/queries.properties @ 87:ce398a7a99fc
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 |
line wrap: on
line diff
--- a/gnv-artifacts/src/test/ressources/queries.properties Thu Sep 24 09:39:27 2009 +0000 +++ b/gnv-artifacts/src/test/ressources/queries.properties Thu Sep 24 10:05:07 2009 +0000 @@ -4,11 +4,85 @@ ############################################# ############################################# -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 +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 ############################################# ############################################# @@ -16,8 +90,19 @@ ############################################# ############################################# -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_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, \ @@ -33,9 +118,46 @@ 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 -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 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 + +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 ############################################# #############################################