New user, enjoying Metabase so far. But, I’ve run into an issue: I can’t seem to get Common Table Expressions (aka “WITH” queries) to work when making raw SQL queries.
For example:
WITH sub_query AS (select * from users limit 1) select * from sub_query;
That works totally fine through a normal connection to the database (and indeed, through other competing projects like SQLPad), but throws the following error in Metabase:
Syntax error in SQL statement " WITH TESTING_QUERY AS[*] (SELECT * FROM USERS LIMIT 1) SELECT * FROM TESTING_QUERY; "; expected "., ("; SQL statement: -- Metabase:: userID: 1 executionID: 2d597259-eed2-4546-b754-3f6a189ab56c queryType: native queryHash: 1582907852 with testing_query as (select * from users limit 1) select * from testing_query; [42001-193]
Am I missing something, or does Metabase not allow Common Table Expressions in queries?
Ahhh, I see. That’s too bad, I was hoping we could finally transition off of SQLPad. Is there a ticket somewhere I could subscribe to track this, or is it not really on the roadmap?
Following up here: diagnosed this is not a real issue! The problem was the dropdown for the database selection was “stuck” and in actuality I was querying the Sample Database instead of my PostgreSQL instance, thus resulting in the CTE error.
Once I rebooted Metabase this issue went away and I was able to use CTEs.