Syntax error using the WITH clause

Question

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');

@a_horse_with_no_name - when I run the script in your link, it seems to work fine. But when I run the script in my IDE, I get the same error: [42601] ERROR: syntax error at or near "values" Position: 41. For what it's worth, I'm using the DataGrip IDE from JetBrains which uses PostgreSQL. Any thoughts? — ZJAY
– ZJAY, Commented Nov 1, 2016 at 22:31
Maybe your IDE tries to be too smart and doesn't know about common table expressions. Does it work with psql? — user330315
– user330315, Commented Nov 1, 2016 at 22:34
You are not using Postgres, you are using Redshift (which is based on a really old and outdated version of Postgres). According to the Redshift manual the values() clause as a constant table is not supported — user330315
– user330315, Commented Nov 7, 2016 at 14:10

score 1 · Accepted Answer · 2016-11-07 15:57:13Z

1

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 ...;

edited Nov 7, 2016 at 15:57

answered Nov 7, 2016 at 14:48

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

ZJAY Over a year ago

@ a_horse_with_no_name unfortunately this solution still returns [42601] ERROR: syntax error at or near "values" Position: 37

user330315 Over a year ago

@ZJAY: sorry, copy & paste error. The values must be removed completely of course

Gottfried Lesigang · Accepted Answer · 2016-11-01 20:47:43Z

0

I don't know Postgres well enough, but with SQL-Server you cannot use the VALUES like a table directly. You must use parenthesis around and provide a table alias with column names to define the derived table.

This would be something like this:

with q as (
select * from
(
    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')
  ) AS tbl(qqyy, firstday, lastday)
)
SELECT *
from q

Attention

You are in high danger!

You are using culture dependant date formats. This might work in your system, but break on another one...
Further more, your are not even consistent!
Your VALUES provide your date values as string.

In SQL-Server I'd suggest to use ISO8601, unseparated or - my favourite - ODBC. But I'm sure there are culture independent formats for literal dates in Postgres too.

And I would suggest to let the CTE come back with typed values or use a temp table with typed columns.

edited Nov 1, 2016 at 20:47

user330315

answered Nov 1, 2016 at 20:00

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

7 Comments

user330315 Over a year ago

In Postgres you can use values like that.

user330315 Over a year ago

And in Postgres it's better to use ANSI date literals, e.g. date '2016-12-31'

Gottfried Lesigang Over a year ago

@a_horse_with_no_name thx again, I was sure, that each RDBMS has a best literal date format. 2016-12-31 seems to be quite close to ISO 8601 anyway, but is quite far away from the (mixed) formats provided above...

user330315 Over a year ago

That's how a date literal is defined by the SQL standard - theoretically every DBMS should honour that (but Microsoft never really cared about the standard)

ZJAY Over a year ago

Thanks @a_horse_with_no_name. What's the downside of using the dates as I used above?

|

Collectives™ on Stack Overflow

Syntax error using the WITH clause

2 Answers 2

2 Comments

Attention

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Attention

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related