# HG changeset patch
# User Tim Englich <tim.englich@intevation.de>
# Date 1253786707 0
# Node ID ce398a7a99fc91bf0a00d7a887d6e17e362bf70a
# Parent  5d4f5d26bb7a6bd9b810517a4f705e3ccde46cb0
SQL-Queries formatted, Some Bugs fixed

gnv-artifacts/trunk@124 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 5d4f5d26bb7a -r ce398a7a99fc gnv-artifacts/Changelog
--- a/gnv-artifacts/Changelog	Thu Sep 24 09:39:27 2009 +0000
+++ b/gnv-artifacts/Changelog	Thu Sep 24 10:05:07 2009 +0000
@@ -1,3 +1,9 @@
+2009-09-24  Tim Englich  <tim.englich@intevation.de>
+
+    * src/test/ressources/queries.properties Edited:
+      Queries formatted so that they can be analysed and extended easier.
+      Some Bugs removed 
+      
 2009-09-24  Tim Englich  <tim.englich@intevation.de>
 
     * src/main/java/de/intevation/gnv/chart/VerticalProfileChartFactory.java  Edited, 
diff -r 5d4f5d26bb7a -r ce398a7a99fc gnv-artifacts/src/test/ressources/queries.properties
--- 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
 
 #############################################
 #############################################