TS_FIRST_VALUE error

reli

hi,
I run this script:
`
CREATE TABLE tst.inner_table (
ts TIMESTAMP,
bid FLOAT
);
CREATE PROJECTION inner_p (ts, bid) as SELECT * FROM tst.inner_table
ORDER BY ts, bid UNSEGMENTED ALL NODES;
INSERT INTO tst.inner_table VALUES ('2009-01-01 03:00:02', 1);
INSERT INTO tst.inner_table VALUES ('2009-01-01 03:00:04', 2);

SELECT ts,
TS_FIRST_VALUE(bid, 'const') fv_c,
TS_FIRST_VALUE(bid, 'linear') fv_l,
TS_LAST_VALUE(bid, 'const') lv_c
FROM tst.inner_table
TIMESERIES ts AS '3 seconds' OVER(PARTITION BY bid ORDER BY ts);`
I get this error:

SQL Error [5023] [0A000]: [Vertica]VJDBC ERROR: Timeseries output functions are not supported in the ORDER BY of a timeseries OVER clause

Is there a solution to this error? And is it better than INTERPOLATE PREVIOUS VALUE on the same table?

marcothesane

You have to give the TIMESERIES a different name than the name of the column you use in ORDER BY:

SELECT new_ts_name AS ts,
TS_FIRST_VALUE(bid, 'const') fv_c,
TS_FIRST_VALUE(bid, 'linear') fv_l,
TS_LAST_VALUE(bid, 'const') lv_c
FROM tst.inner_table
TIMESERIES new_ts_name AS '3 seconds' OVER(PARTITION BY bid ORDER BY ts);

reli

Nice! Thank you