Cannot create view with CTE on DB2

Can't run the following code, editor says there is a syntax error and refuses to attempt to run it, however this works fine when run using another IDE (eg. TOAD).

 

The following SQL gives the error below

SQL:

CREATE VIEW PLAYERDASHBOARD.THIS_WONT_WORK AS
WITH L AS (SELECT * FROM DW.LOCALE)
SELECT * FROM L;

ERROR:

[42601][-104] An unexpected token "END-OF-STATEMENT" was found following "RD.THIS_WONT_WORK AS". Expected tokens may include: "<query_expr>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.7.85

 

I tested this exact example using TOAD which worked fine.

 

The statement does run successfully without the view part.

3 comments
Comment actions Permalink

Hi,
I can't reproduce:



It looks like you've got some termination character in your query.

Thank you.

0
Comment actions Permalink

I’ve still been unable to get this to work.

This is the error message I’ve received. Can you possibly indicate what I’m doing wrong?

0
Comment actions Permalink

Hi,
I reproduced the problem in DataGrip 2017.2.3.


As a temporary solution, you can switch to DataGrip 2017.3 EAP 4 https://www.jetbrains.com/datagrip/nextversion/ where I can't reproduce the problem.

Also fix will be available in coming DataGrip 2017.3 Release.

Thank you.

1

Please sign in to leave a comment.