Question about SQL query

From: Ing. Martin Gamperl <mgampi_at_ibm.net>
Date: 1998/05/16
Message-ID: <355DC15E.CAE9A53@ibm.net>#1/1

(please use fixed font for correct formating...)

Hello;

We have one table containing measured values which are obtained each minute from a data aquisition system. To reduce the amount of data (number of records) a threshold and slope determines if data is written to the
table or not. Detailed information about the data stored in this table can be queried from a sub-table using the ID field, but for this problem

it is not relevant (will be described later). Because of data reduction its not guaranteed that all values will be written to the table after a timer elapses. The structure of the table looks like

            ID       NUMBER
            TSTAMP   DATE
            VALUE    NUMBER

Data looks like this:

       ID   TSTAMP        VALUE
       ------------------------
        9   15.04.,13:00  123.9
       31   15.04.,13:00   11.2
       39   15.04.,13:00  630.9
       44   15.04.,13:00   72.6
       31   15.04.,13:01   13.6
       39   15.04.,13:01  635.6
       44   15.04.,13:01   78.9
       31   15.04.,13:02   11.3
       .......

For displaying the data in a line graph I need a query result which looks like the next table where one record for each distinct timestamp should be generated. The query is restricted by selecting a range of timestamps and IDs. Values which were not saved at the corresponding timestamp appear as NULL values in my query. But I want the query to replace the NULL values with the first non NULL value of same ID which has a older timestamp than the current record (I'll show this in the second example of the result table below).

       TIMESTAMP      ID=9   ID2=31
       ----------------------------
       15.04.,13:00  123.9     11.2
       15.04.,13:01   NULL     13.6
       15.04.,13:02   NULL     11.3


       TIMESTAMP      ID=9   ID2=31
       ----------------------------
       15.04.,13:00  123.9     11.2
       15.04.,13:01  123.9     13.6
       15.04.,13:02  123.9     11.3
                     ^^^^^
                     This NULL value has been replaced by the value with

                     timestamp 15.04.,13:00

If the first value of one ID is already NULL than the value should be obtained from a field of a second table which describes the individual IDs in detail (table TA_DESCR, field LASTKNOWNVAL).

The SQL-Statement I use to obtain the table in example 1 is:

select ts, id9, id31, id39, id44
from (select distinct ts

      from t),


     (select ts F1, value id9

      from t
      where id = 9),


     (select ts F2, value id31

      from t
      where id = 31),


     (select ts F3, value id39

      from t
      where id = 39),


     (select ts F4, value id44

      from t
      where id = 44)
where ts = F1(+)
and   ts = F2(+)

and ts = F3(+)
and ts = F4(+)
/

Is it possible to achieve this result table witout using PL/SQL? I hope someone can help!

Thanx in advance

M. Gamperl

--
***************************************************
*  Ing. Martin Gamperl
*  MGS Messgeraetesteuerung - Softwareentwicklung
*  Klosterwiesgasse 31/IV
*  A-8010 GRAZ
*  Tel.    : +43-316-83 58 26
*  Fax     : +43-316-83 58 26
*  Mobil   : +43-664-40 03 032
*  EMail   : mgampi_at_ibm.net
***************************************************

Received on Sat May 16 1998 - 00:00:00 CDT