tim@83: ############################################# tim@83: ############################################# tim@83: ########## Zeitserie ############## tim@83: ############################################# tim@83: ############################################# tim@83: tim@87: timeseries_timeseriespoint=SELECT DISTINCT \ tim@87: tsp.FEATUREID KEY, \ tim@87: tsp.NAME VALUE \ tim@87: FROM MEDIAN.TIMESERIESPOINT tsp, \ tim@87: MEDIAN.MEASUREMENT mmt \ tim@87: WHERE tsp.FEATUREID = mmt.FEATUREID AND \ tim@87: mmt.SOURCEID = ? \ tim@87: order by tsp.name tim@87: tim@87: timeseries_parameter=SELECT DISTINCT \ tim@87: p.PARAMETERID KEY, \ tim@87: p.GERMANNAME VALUE \ tim@87: from MEDIAN.PARAMETER p \ tim@87: where p.PARAMETERID in \ tim@87: (select distinct \ tim@87: ts.PARAMETERID \ tim@87: from MEDIAN.TIMESERIES ts \ tim@87: where ts.TIMESERIESID in \ tim@87: (select distinct \ tim@87: tsv.TIMESERIESID \ tim@87: from MEDIAN.TIMESERIESVALUE tsv \ tim@87: where tsv.MEASUREMENTID in \ tim@87: (select m.MEASUREMENTID \ tim@87: from MEDIAN.MEASUREMENT m, \ tim@87: MEDIAN.TIMESERIESPOINT tsp \ tim@87: where m.FEATUREID = tsp.FEATUREID and \ tim@87: tsp.FEATUREID IN ( ? )\ tim@87: )\ tim@87: )\ tim@87: ) \ tim@87: ORDER BY p.GERMANNAME tim@87: tim@87: timeseries_depth_height=SELECT DISTINCT \ tim@87: m.MEASUREMENTID KEY, \ tim@87: m.ZLOCATION VALUE \ tim@87: from MEDIAN.MEASUREMENT m \ tim@87: where m.MEASUREMENTID in \ tim@87: (SELECT DISTINCT \ tim@87: t_v.MEASUREMENTID \ tim@87: from MEDIAN.TIMESERIESVALUE t_v \ tim@87: where t_v.TIMESERIESID in \ tim@87: (SELECT DISTINCT \ tim@87: t.TIMESERIESID \ tim@87: from MEDIAN.TIMESERIES t \ tim@87: where t.PARAMETERID in \ tim@87: (SELECT DISTINCT \ tim@87: p.PARAMETERID \ tim@87: from MEDIAN.PARAMETER p \ tim@87: where m.FEATUREID IN ( ? ) and \ tim@87: p.PARAMETERID IN (?)\ tim@87: )\ tim@87: )\ tim@87: )\ tim@87: ORDER BY m.ZLOCATION DESC tim@87: tim@87: timeseries_interval=select min(tv.TIMEVALUE) MIN, \ tim@87: max(tv.TIMEVALUE) MAX \ tim@87: from MEDIAN.TIMESERIES t , \ tim@87: MEDIAN.TIMESERIESVALUE tv \ tim@87: where tv.TIMESERIESID = t.TIMESERIESID AND \ tim@87: t.PARAMETERID IN ( ? ) AND \ tim@87: tv.MEASUREMENTID IN ( ? ) tim@87: tim@87: timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \ tim@87: tv.DATAVALUE YORDINATE, \ tim@87: t.PARAMETERID GROUP1, \ tim@87: tv.MEASUREMENTID GROUP2, \ tim@87: tv.TIMESERIESID GROUP3 \ tim@87: FROM MEDIAN.TIMESERIESVALUE tv, \ tim@87: MEDIAN.TIMESERIES t \ tim@87: WHERE tv.TIMESERIESID = t.TIMESERIESID AND \ tim@87: t.PARAMETERID IN ( ? ) AND \ tim@87: tv.MEASUREMENTID IN ( ? ) AND \ tim@102: tv.TIMEVALUE > ? AND \ tim@102: tv.TIMEVALUE < ? \ tim@87: ORDER BY tv.MEASUREMENTID , \ tim@87: tv.TIMESERIESID , \ tim@87: t.PARAMETERID , \ tim@87: tv.TIMEVALUE tim@81: tim@83: ############################################# tim@83: ############################################# tim@83: ########## Zeitserie Mesh ############## tim@83: ############################################# tim@83: ############################################# tim@81: tim@87: timeseries_mesh = SELECT OBJECTID KEY, \ tim@87: m.NAME VALUE \ tim@87: FROM MEDIAN.MESH m \ tim@87: order by m.NAME tim@87: timeseries_meshpoint = SELECT FEATUREID KEY, \ tim@87: SHAPE VALUE \ tim@87: FROM MEDIAN.MESHPOINT mp , \ tim@87: MEDIAN.MESH m \ tim@87: WHERE m.OBJECTID = ? AND \ tim@87: mp.MESHID = m.MESHID AND \ tim@87: KPOSITION = 1 AND \ tim@87: rownum < 10 tim@87: tim@83: timeseries_meshpoint_depth = select mp.FEATUREID KEY, \ tim@83: -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \ tim@83: from MEDIAN.MESHLAYER ml, \ tim@83: MEDIAN.MESHPOINT mp \ tim@83: where ml.KPOSITION = mp.KPOSITION and \ tim@83: ml.MESHID = mp.MESHID and \ tim@83: mp.FEATUREID in \ tim@83: ( select FEATUREID \ tim@83: from MEDIAN.MESHPOINT mp, \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: mp.MESHID = m.MESHID AND \ tim@83: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ tim@83: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@83: order by ml.UPPERZLOCATION desc tim@87: tim@87: timeseries_mesh_parameter=SELECT distinct \ tim@87: p.PARAMETERID KEY , \ tim@87: p.GERMANNAME VALUE \ tim@87: from MEDIAN.PARAMETER p, \ tim@87: MEDIAN.MESHSCALARVALUE msc, \ tim@87: MEDIAN.MESH m \ tim@87: where m.OBJECTID = ? AND \ tim@87: msc.PARTID = m.PARTIDMIN AND \ tim@87: msc.PARAMETERID = p.PARAMETERID \ tim@87: order by p.GERMANNAME tim@87: timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \ tim@87: min(TIMEVALUE) MIN, \ tim@87: max(TIMEVALUE) MAX \ tim@87: from MEDIAN.MESHSCALARVALUE msc , \ tim@87: MEDIAN.MESH m \ tim@87: where m.OBJECTID = ? AND \ tim@87: msc.PARTID >= m.PARTIDMIN AND \ tim@87: msc.PARTID <= m.PARTIDMAX tim@87: tim@87: timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \ tim@87: msv.TIMEVALUE XORDINATE, \ tim@87: msv.DATAVALUE YORDINATE, \ tim@87: msv.PARAMETERID GROUP1, \ tim@87: msv.FEATUREID GROUP2, \ tim@87: mp.FEATUREID GROUP3 \ tim@87: from MEDIAN.MESHSCALARVALUE msv , \ tim@87: MEDIAN.MESHPOINT mp, \ tim@87: MEDIAN.MESH m \ tim@87: where (m.OBJECTID = ? AND \ tim@87: msv.PARTID >= m.PARTIDMIN AND \ tim@87: msv.PARTID <= m.PARTIDMAX ) AND \ tim@87: msv.FEATUREID in ( ? ) AND \ tim@87: msv.PARAMETERID in ( ? ) AND \ tim@108: mp.FEATUREID in ( ? ) AND \ tim@102: msv.TIMEVALUE >= ? AND \ tim@102: msv.TIMEVALUE <= ? \ tim@108: order by mp.FEATUREID, \ tim@108: msv.FEATUREID, \ tim@87: msv.PARAMETERID, \ tim@87: msv.TIMEVALUE tim@81: tim@83: ############################################# tim@83: ############################################# tim@83: ########## Vertikalprofil ############## tim@83: ############################################# tim@83: ############################################# tim@82: tim@82: verticalprofile_point=SELECT DISTINCT \ tim@82: tsp.FEATUREID KEY, \ tim@82: tsp.NAME VALUE \ tim@82: FROM MEDIAN.TIMESERIESPOINT tsp, \ tim@82: MEDIAN.MEASUREMENT mmt \ tim@82: WHERE tsp.FEATUREID = mmt.FEATUREID AND \ tim@82: mmt.SOURCEID = ? \ tim@82: order by tsp.name tim@82: tim@82: verticalprofile_parameter=SELECT DISTINCT \ tim@82: p.PARAMETERID KEY, \ tim@82: p.GERMANNAME VALUE \ tim@82: from MEDIAN.PARAMETER p, \ tim@82: MEDIAN.TIMESERIES ts, \ tim@82: MEDIAN.TIMESERIESVALUE tsv, \ tim@82: MEDIAN.MEASUREMENT m \ tim@82: where ts.PARAMETERID = p.PARAMETERID and \ tim@82: ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@82: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@82: m.FEATUREID = ? \ tim@82: ORDER BY p.GERMANNAME tim@82: tim@82: verticalprofile_date=select distinct \ tim@82: tsv.TIMEVALUE KEY, \ tim@82: tsv.TIMEVALUE VALUE \ tim@82: from MEDIAN.TIMESERIES ts , \ tim@82: MEDIAN.TIMESERIESVALUE tsv, \ tim@82: MEDIAN.MEASUREMENT m \ tim@82: where ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@82: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@82: m.FEATUREID = ? and \ tim@82: ts.PARAMETERID IN ( ? ) \ tim@82: order by tsv.TIMEVALUE tim@82: tim@82: verticalprofile_chart_data= SELECT m.ZLOCATION XORDINATE, \ tim@82: tsv.DATAVALUE YORDINATE, \ tim@82: ts.PARAMETERID GROUP1, \ tim@82: tsv.TIMEVALUE GROUP2, \ tim@82: 1 GROUP3 \ tim@82: from MEDIAN.TIMESERIES ts, \ tim@82: MEDIAN.TIMESERIESVALUE tsv, \ tim@82: MEDIAN.MEASUREMENT m \ tim@82: where ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@82: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@82: m.FEATUREID = ? and \ tim@82: ts.PARAMETERID IN ( ? ) AND \ tim@83: tsv.TIMEVALUE IN (?) \ tim@82: ORDER BY tsv.TIMEVALUE , \ tim@82: tsv.TIMESERIESID , \ tim@82: ts.PARAMETERID , \ tim@82: tsv.TIMEVALUE tim@83: tim@83: ############################################# tim@83: ############################################# tim@83: ########## Vertikalprofil Mesh ############## tim@83: ############################################# tim@83: ############################################# tim@83: tim@83: verticalprofile_mesh = SELECT OBJECTID KEY, \ tim@83: m.NAME VALUE \ tim@83: FROM MEDIAN.MESH m \ tim@83: order by m.NAME tim@83: tim@83: verticalprofile_mesh_point = SELECT FEATUREID KEY, \ tim@83: SHAPE VALUE \ tim@83: FROM MEDIAN.MESHPOINT mp , \ tim@83: MEDIAN.MESH m \ tim@83: WHERE m.OBJECTID = ? AND \ tim@83: m.MESHID = mp.MESHID AND \ tim@83: mp.KPOSITION = 1 AND \ tim@83: rownum < 10 tim@83: tim@83: verticalprofile_mesh_parameter=SELECT distinct \ tim@83: p.PARAMETERID KEY , \ tim@83: p.GERMANNAME VALUE \ tim@83: from MEDIAN.PARAMETER p, \ tim@83: MEDIAN.MESHSCALARVALUE msc, \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: m.PARTIDMIN = msc.PARTID AND \ tim@83: msc.PARAMETERID = p.PARAMETERID \ tim@83: order by p.GERMANNAME tim@83: tim@83: verticalprofile_mesh_date= select distinct \ tim@83: msv.TIMEVALUE KEY, \ tim@83: msv.TIMEVALUE VALUE \ tim@83: from MEDIAN.MESHSCALARVALUE msv , \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: msv.PARTID >= m.PARTIDMIN AND \ tim@83: msv.PARTID <= m.PARTIDMAX \ tim@83: order by msv.TIMEVALUE tim@83: tim@83: verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \ tim@83: msv.DATAVALUE YORDINATE, \ tim@83: msv.PARAMETERID GROUP1, \ tim@83: msv.TIMEVALUE GROUP2, \ tim@83: 1 GROUP3 \ tim@83: from MEDIAN.MESHLAYER ml, \ tim@83: MEDIAN.MESHPOINT mp, \ tim@83: MEDIAN.MESH m, \ tim@83: MEDIAN.MESHSCALARVALUE msv \ tim@83: where msv.FEATUREID = mp. FEATUREID AND \ tim@83: ml.KPOSITION = mp.KPOSITION and \ tim@83: ml.MESHID = mp.MESHID and \ tim@83: m.MESHID = mp.MESHID AND \ tim@83: m.PARTIDMIN <= msv.PARTID AND \ tim@83: m.PARTIDMAX >= msv.PARTID AND \ tim@83: msv.PARAMETERID in (?) AND \ tim@83: msv.TIMEVALUE in (?) AND \ tim@83: m.OBJECTID = ? AND \ tim@83: mp.FEATUREID in \ tim@83: ( select FEATUREID \ tim@83: from MEDIAN.MESHPOINT mp, \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: mp.MESHID = m.MESHID AND \ tim@83: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ tim@83: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@83: order by msv.PARAMETERID, \ tim@83: msv.TIMEVALUE, \ tim@83: ml.UPPERZLOCATION tim@83: tim@85: ############################################# tim@85: ############################################# tim@85: ##### Vertikalprofil InstantaneousPoint ##### tim@85: ############################################# tim@85: ############################################# tim@85: tim@85: verticalprofile_instantaneouspoint_series = SELECT S.SERIESID KEY, \ tim@85: S.DESCRIPTION VALUE \ tim@85: FROM MEDIAN.SERIES S tim@85: tim@85: verticalprofile_instantaneouspoint_point = SELECT IP.FEATUREID KEY, \ tim@85: IP.SHAPE VALUE \ tim@85: FROM MEDIAN.INSTANTANEOUSPOINT IP \ tim@85: WHERE IP.POINTSPEC = 4 AND \ tim@85: IP.SERIESID = ? tim@85: tim@85: verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \ tim@85: P.PARAMETERID KEY , \ tim@85: P.GERMANNAME VALUE \ tim@85: FROM MEDIAN.PARAMETER P, \ tim@85: MEDIAN.INSTANTANEOUSPOINT IP, \ tim@85: MEDIAN.MEASUREMENT M, \ tim@85: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@85: WHERE IP.FEATUREID = M.FEATUREID AND \ tim@85: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@85: MSV.PARAMETERID = P.PARAMETERID AND \ tim@85: IP.FEATUREID = ? \ tim@85: ORDER BY P.GERMANNAME tim@85: tim@85: verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \ tim@85: MSV.DATAVALUE YORDINATE, \ tim@85: MSV.PARAMETERID GROUP1, \ tim@85: IP.TIMEVALUE GROUP2, \ tim@85: 1 GROUP3 \ tim@85: FROM MEDIAN.INSTANTANEOUSPOINT IP, \ tim@85: MEDIAN.MEASUREMENT M, \ tim@85: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@85: WHERE IP.FEATUREID = M.FEATUREID AND \ tim@85: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@85: IP.FEATUREID = ? AND \ tim@85: MSV.PARAMETERID in (?) \ tim@85: ORDER BY IP.TIMEVALUE, \ tim@85: MSV.PARAMETERID, \ tim@114: M.ZLOCATION tim@114: tim@114: ############################################# tim@114: ############################################# tim@114: #### Horizontalprofil InstantaneousPoint #### tim@114: ############################################# tim@114: ############################################# tim@114: tim@114: horizontalprofile_instantaneouspoint_cruise = SELECT \ tim@114: CRUISEID KEY , \ tim@114: NAME VALUE \ tim@114: FROM MEDIAN.CRUISE \ tim@114: WHERE NAME IS NOT NULL \ tim@114: ORDER BY NAME tim@114: horizontalprofile_instantaneouspoint_track= SELECT \ tim@114: TRACKID KEY , \ tim@114: NAME VALUE \ tim@114: FROM MEDIAN.TRACK \ tim@114: WHERE CRUISEID = ? \ tim@114: ORDER BY NAME tim@114: horizontalprofile_instantaneouspoint_surveyinfo = SELECT \ tim@114: SURVEYID KEY , \ tim@114: DESCRIPTION VALUE \ tim@114: FROM MEDIAN.SURVEYINFO \ tim@114: WHERE TRACKID = ? \ tim@114: ORDER BY DESCRIPTION tim@114: horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \ tim@114: P.PARAMETERID KEY , \ tim@114: P.GERMANNAME VALUE \ tim@114: FROM MEDIAN.PARAMETER P , \ tim@114: MEDIAN.MEASUREMENT M , \ tim@114: MEDIAN.INSTANTANEOUSPOINT IP , \ tim@114: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@114: WHERE P.PARAMETERID = MSV.PARAMETERID AND \ tim@114: MSV.MEASUREMENTID = M.MEASUREMENTID AND \ tim@114: M.FEATUREID = IP.FEATUREID AND \ tim@114: IP.SURVEYID = ? \ tim@114: ORDER BY P.GERMANNAME tim@114: horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \ tim@114: M.ZLOCATION KEY, \ tim@114: M.ZLOCATION VALUE \ tim@114: FROM MEDIAN.MEASUREMENT M , \ tim@114: MEDIAN.INSTANTANEOUSPOINT IP \ tim@114: WHERE M.FEATUREID = IP.FEATUREID AND \ tim@114: IP.SURVEYID = ? \ tim@114: ORDER BY M.ZLOCATION tim@114: horizontalprofile_instantaneouspoint_chart_data = SELECT M.XLOCATION XORDINATE_XCOORD , \ tim@114: M.YLOCATION XORDINATE_YCOORD , \ tim@114: MSV.DATAVALUE YORDINATE , \ tim@114: MSV.PARAMETERID GROUP1 , \ tim@114: IP.TIMEVALUE GROUP2 , \ tim@114: 1 GROUP3 \ tim@114: FROM MEDIAN.INSTANTANEOUSPOINT IP, \ tim@114: MEDIAN.MEASUREMENT M, \ tim@114: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@114: WHERE IP.FEATUREID = M.FEATUREID AND \ tim@114: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@114: IP.SURVEYID = ? AND \ tim@114: M.ZLOCATION IN (?) AND \ tim@114: MSV.PARAMETERID in (?) \ tim@114: ORDER BY IP.TIMEVALUE, \ tim@114: MSV.PARAMETERID, \ tim@114: M.ZLOCATION