get value and previous value in Aspen SQLPLUS query?

Question

I am trying to get a value and the value just before using SQLPLUS in Aspen V8.8 query.

I found on Oracle library the function "LAG" that seemed perfect to me. But I get errors when using this function

below is my code :

SELECT IP_TREND_VALUE, IP_TREND_TIME, LAG(IP_TREND_VALUE,1,0) OVER (ORDER BY IP_TREND_TIME)  as valeur_prec from IP_DIDef_1 WHERE name='my_value' and IP_TREND_TIME BETWEEN '15-APR-19 00:00:00' and '28-APR-19 00:00:00' ORDER BY IP_TREND_TIME

with this code, I get an error "expecting reserved word FROM". Could you please tell me where is my mistake, or how I could do things to get things differenly ?

Thanks a lot

regards

Answer 1

keep in mind that a data historian is far from a SGBD database. Even if it is available via an ODBC driver, the SqlPlus syntax is not standard Sql, and even more different than Oracle or SqlServer Sql.
That being said, my guess (I suppose you are interested in the recorded data, not interpolated ones), would be:

LOCAL dTs1 TIMESTAMP;
LOCAL dTs2 TIMESTAMP;

dTs1 = (
    SELECT MAX(IP_TREND_TIME)
    FROM "[Tag name]"
    WHERE IP_TREND_TIME BETWEEN '15-APR-19 00:00:00' AND '28-APR-19 00:00:00'
    AND ABS(IP_TREND_VALUE - [value you search]) < [precision you are ok with]
);

--WRITE dTs1;

SET MAX_ROWS = 1;
dTs2 = (
    SELECT IP_TREND_TIME
    FROM "[Tag name]"
    WHERE IP_TREND_TIME >= '15-APR-19 00:00:00' AND IP_TREND_TIME < dTs1
);

--WRITE dts2;

SET MAX_ROWS = 2;
SELECT IP_TREND_TIME, IP_TREND_VALUE
FROM "[Tag name]"
WHERE IP_TREND_TIME IN (dTs1, dTs2)