ZJAY ZJAY - 26 days ago 7
SQL Question

Syntax error using the WITH clause

I am trying to setup a CTE table with a series of quarterly dates.

The query returns [42601] ERROR: syntax error at or near "values" Position: 38

with q(qqyy, firstday, lastday) as (
values
('Q4_10', '09-30-2010', '12-31-2010'),
('Q1_11', '12-31-2010', '03-31-2011'),
('Q2_11', '03-31-2011', '06/30/2011'),
('Q3_11', '06/30/2011', '09/30/2011'),
('Q4_11', '09/30/2011', '12/31/2011'),
('Q1_12', '12/31/2011', '03/31/2012'),
('Q2_12', '03/31/2012', '06/30/2012'),
('Q3_12', '06/30/2012', '09/30/2012'),
('Q4_12', '09/30/2012', '12/31/2012'),
('Q1_13', '12/31/2012', '03/31/2013'),
('Q2_13', '03/31/2013', '06/30/2013'),
('Q3_13', '06/30/2013', '09/30/2013'),
('Q4_13', '09/30/2013', '12/31/2013'),
('Q1_14', '12/31/2013', '03/31/2014'),
('Q2_14', '03/31/2014', '06/30/2014'),
('Q3_14', '06/30/2014', '09/30/2014'),
('Q4_14', '09/30/2014', '12/31/2014'),
('Q1_15', '12/31/2014', '03/31/2015'),
('Q2_15', '03/31/2015', '06/30/2015'),
('Q3_15', '06/30/2015', '09/30/2015'),
('Q4_15', '09/30/2015', '12/31/2015'),
('Q1_16', '12/31/2015', '03/31/2016'),
('Q2_16', '03/31/2016', '06/30/2016'),
('Q3_16', '06/30/2016', '09/30/2016'),
('Q4_16', '09/30/2016', '12/31/2016')
)

SELECT q.qqyy, cobrand_id, sum(calc)
into temp_08.cmg_calc
from temp_08.cmg s
join q on
s.transaction_date >= q.firstday
and s.transaction_date <= q.lastday
GROUP BY q.qqyy, cobrand_id;


It appears that the above query is getting stuck on "values" due to Redshift using an older version of postgresql (http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html). But for some reason the below query that also uses "values" works fine. Any idea how I can get the above query to work using redshift?

create table temp_08.cmgquarters (
quarter_col text
, date_from date
, date_to date
);


insert into temp_08.cmgquarters
values
('Q4_10', '09-30-2010', '12-31-2010'),
('Q1_11', '12-31-2010', '03-31-2011'),
('Q2_11', '03-31-2011', '06/30/2011'),
('Q3_11', '06/30/2011', '09/30/2011'),
('Q4_11', '09/30/2011', '12/31/2011'),
('Q1_12', '12/31/2011', '03/31/2012'),
('Q2_12', '03/31/2012', '06/30/2012'),
('Q3_12', '06/30/2012', '09/30/2012'),
('Q4_12', '09/30/2012', '12/31/2012'),
('Q1_13', '12/31/2012', '03/31/2013'),
('Q2_13', '03/31/2013', '06/30/2013'),
('Q3_13', '06/30/2013', '09/30/2013'),
('Q4_13', '09/30/2013', '12/31/2013'),
('Q1_14', '12/31/2013', '03/31/2014'),
('Q2_14', '03/31/2014', '06/30/2014'),
('Q3_14', '06/30/2014', '09/30/2014'),
('Q4_14', '09/30/2014', '12/31/2014'),
('Q1_15', '12/31/2014', '03/31/2015'),
('Q2_15', '03/31/2015', '06/30/2015'),
('Q3_15', '06/30/2015', '09/30/2015'),
('Q4_15', '09/30/2015', '12/31/2015'),
('Q1_16', '12/31/2015', '03/31/2016'),
('Q2_16', '03/31/2016', '06/30/2016'),
('Q3_16', '06/30/2016', '09/30/2016'),
('Q4_16', '09/30/2016', '12/31/2016');

Answer

With Redshift not supporting the values() as a "table replacement" you need to re-write that as a union:

with q(qqyy, firstday, lastday) as (
  select 'Q4_10', '09-30-2010', '12-31-2010' union all
  select 'Q1_11', '12-31-2010', '03-31-2011' union all
  ....
)
SELECT ...;

you should however user proper DATE literals:

with q(qqyy, firstday, lastday) as (
  select 'Q4_10', DATE '2010-09-30', DATE '2010-12-31' union all
  select 'Q1_11', DATE '2010-12-31', DATE '2011-03-31' union all
  ....
)
SELECT ...;
Comments