I have a table a
in Vertica containing the item_id
attached to a code
at time from_date
create table a (
code varchar(16),
item_id int,
from_date timestamptz
);
and a table b
containing a code
for a given date t
create table b (
code varchar(16),
t date
);
I need to retrieve the a.item_id
from the b.code
at the date b.t
. The data looks like that :
insert into a values ('abc', 1, '2013-01-01 10:00:00 EST'::timestamptz);
insert into a values ('abc', 2, '2013-01-05 11:00:00 EST'::timestamptz);
insert into a values ('abc', 3, '2013-01-09 9:00:00 EST'::timestamptz);
insert into b values ('abc', '2013-01-06'::date);
I tried to use Vertica's INTERPOLATE in a LEFT JOIN in order to retrieve the previous (code, item_id)
pair at time t
with
select
b.code, b.t, a.item_id
from b
left join a on a.code = b.code and b.t INTERPOLATE PREVIOUS VALUE a.t;
This is giving me a type error Interpolated predicates can accept arguments of the same type only
.
So I tried to convert b.t
to a timestamptz
select
b.code, b.t, a.item_id
from b
left join a on a.code = b.code
and (b.t::timestamptz) INTERPOLATE PREVIOUS VALUE a.t;
This is giving me a syntax error Syntax error at or near "INTERPOLATE"
.
Without INTERPOLATE, the SQL expression works but doesn't join on the previous row.
Is it possible to convert my date to a timestamptz in a JOIN with INTERPOLATE?
If not, is there any other approach that would allow me to retrieve the current item_id
at time t
?
I found a hacky workaround by tricking the interpreter and do the date to timestamp conversion in a SELECT subquery:
select
b.code, b.t, a.item_id
from b
left join (select a.t::date, a.code, a.item from a) a on a.code = b.code
and b.t INTERPOLATE PREVIOUS VALUE a.t;