Reputation: 3216
typically to create a table in Presto (from existing db tables), I do:
create table abc as (
select...
)
But to make my code simple, I've broken out subqueries like this:
with sub1 as (
select...
),
sub2 as (
select...
),
sub3 as (
select...
)
select
from sub1 join sub2 on ...
join sub3 on ...
Where do I put the create table statement here? The actual query is more complex than the above so I am trying to avoid having to put the subqueries within the main query.
Upvotes: 14
Views: 56930
Reputation: 704
This works
create table answertable as ( select * from ( WITH q as ( select findcode from (values 'G463','G464','G465','G466','G467','G468','I64','I694') as concept(findcode) ) select s.* from table1 s inner join q on starts_with(s.xxx, q.findcode) where s.pk is not null ) )
Upvotes: -2
Reputation: 335
If Strings are concerned, then following works
WITH sample AS (
SELECT * FROM (VALUES ('strA', 'strB'), ('strC', 'strD'), ('strE', 'strF')) AS account (name, cat)
)
SELECT name, cat from sample;
if integers are only concerned values , then following works: -
WITH slab (SNo,Amount) AS (VALUES (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(6,6000),(7,7000),(8,8000),(9,9000),(10,10000),(11, 11000),(12,12000),(13,13000),(14,14000),(15,15000),(16,16000),(17,17000),(18,18000),(19,19000),(20,20000),(21,21000),(22,22000),(23,23000),(24,24000),(25,25000),(26,26000),(27,27000),(28,28000),(29,29000),(30,30000),(31,31000),(32,32000),(33,33000),(34,34000),(35,35000),(36,36000),(37,37000),(38,38000),(39,39000),(40,40000),(41,41000),(42,42000),(43,43000),(44,44000),(45,45000),(46,46000),(47,47000),(48,48000),(49,49000),(50,50000),(51,51000)
)
SELECT * FROM slab;
Upvotes: 11
Reputation: 20730
Syntax is just as if you prepend create table .. as
to the select. E.g. the following worked for me on Presto 0.170:
create table memory.default.a as
with w as (
select * from (values 1) t(x)
)
select * from w;
(I use experimental memory
connector so that this is copy-pastable to try it out.)
Upvotes: 5
Reputation: 302
This is possible with an INSERT INTO not sure about CREATE TABLE:
INSERT INTO s1
WITH q1 AS (...)
SELECT * FROM q1
Maybe you could give this a shot:
CREATE TABLE s1 as
WITH q1 AS (...)
SELECT * FROM q1
Upvotes: 12
Reputation: 3793
I believe you need to 'wrap' the entire query like this:
create table EXAMPLE as (
with sub1 as (
select ...
),
.......
select
from sub1....
)
Upvotes: 2