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

The code is valid SQL and that doesn't look like an Oracle error message. (All Oracle error messages include a string like "ORA-XYZ" at the front of it.) I'd guess it's a problem with an old version of Aspen that was created before analytic functions were introduced, and the program doesn't know how to parse that statement. — Jon Heller, Apr 29 '19 at 02:57

score 2 · Answer 1 · answered Apr 29 '19 at 14:22

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)

Thanks a lot for your answers ! To be honest I didn't know this diffrence between sgbd and historian sql. I only guessed that Aspen chose or had to have a modified sql... I'll try your code tomorrow when back to the office Thanks a lot again ! — Makol76, Apr 29 '19 at 21:02

get value and previous value in Aspen SQLPLUS query?

1 Answers1