Error: "Test Query Error near 'as': syntax error" when executing a 'WITH' clause against a SQLITE table

[Suskil]

Issue 

The following SQLite query executes successfully when running outside of Designer:
 

WITH
newname as

(SELECT * from tablename where column_name = 'something')

SELECT * from newname

However, when testing the syntax in the 'SQL Editor' tab of the Visual Query Builder popup, the following error is displayed:
 

near 'as': syntax error

Environment Details

• Alteryx Designer
  • 2021.3
• SQLite
  • CTE Query

Cause

The query is being parsed as two separate select statements.

Resolution

Enclose the full SQL statement in parentheses and re-test the query.

e.g.

(
WITH
newname as

(SELECT * from tablename where column_name = 'something')

SELECT * from newname
)

Additional Resources

• Supported Data Sources