Hi,
I'm doing a simple join between a WITH clause that produces some dates and another table -
and I'm getting the following error:
[2018-04-15 15:21:27] [0A000][500310] [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;
[2018-04-15 15:21:27] java.lang.RuntimeException: com.amazon.support.exceptions.ErrorException: [Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.;
[2018-04-15 15:21:27] at com.amazon.redshift.client.messages.inbound.ErrorResponse.toErrorException(Unknown Source)
[2018-04-15 15:21:27] at com.amazon.redshift.client.PGMessagingContext.handleErrorResponse(Unknown Source)
I've added a small script to reproduce the problem.
What am I doing wrong?
Thanks in advance !
-- drop table if exists temp_users
create table temp_users (
id INT,
name varchar,
created_at date
);
insert INTO temp_users
(id, name, created_at)
VALUES
( 1, 'Anna', now()::date)
, ( 2, 'Charlie', now()::date - interval '1 month')
, ( 3, 'Dave', now()::date - interval '2 month')
, ( 4, 'Eddie', now()::date - interval '3 month')
, ( 5, 'Fiona', now()::date - interval '4 month') ;
select * from temp_users;
with generated_dates as (
SELECT date_trunc('mon', current_date) - (n || ' months')::interval as generated_date
from generate_series (1, 3) n
order by generated_date desc
)
select
u.name as metric,
generated_dates.generated_date as time
FROM generated_dates
JOIN temp_users as u ON date_trunc('mon', u.created_at) = generated_dates.generated_date
Edited by: Eligo on Apr 15, 2018 6:28 AM