With clause in vertica sql

Question

Querying different tables/schema using with clause

WITH T1 as ( SELECT something as s1 from schema1.table1 ),
WITH T2 as ( SELECT something as s2 from schema2.table1 )
SELECT * FROM T1,T2;

It gives error as : ERROR: Syntax error at or near "WITH"

Could you please point out what am I missing here

A query only has one `WITH` clause and that can contain multiple CTEs. — Gordon Linoff, Aug 03 '20 at 13:48

score 2 · Answer 1 · answered Aug 03 '20 at 13:00

The with keyword should appear only once, at the beginning of the query:

WITH 
    T1 as ( SELECT something as s1 from schema1.table1 ),
    T2 as ( SELECT something as s2 from schema2.table1 )
SELECT T1.something something1, T2.something something2
FROM T1
CROSS JOIN T2;

Note that I rewrote your implicit join to an explicit cross join. You should also probably alias the column names in the resultset, since both queries produce a column that has the same name.

score 0 · Answer 2 · answered Aug 03 '20 at 16:01

What is it actually that you want?

A Cartesian Product of the two tables
All rows of the first table, and all rows of the second table?

I put some sample data in the WITH clause (yes, the WITH keyword only once, then a comma list), and show the two results. Yours, first (and I concur with @GMB that you should use CROSS JOIN if you join two tables with no filter), and then what I think would make more sense .

Here goes.

-- the WITH clause ...
WITH
table1(id,something) AS (
          SELECT 11,'something 1 from 1'
UNION ALL SELECT 12,'something 2 from 1'
UNION ALL SELECT 13,'something 3 from 1'
)
,
table2(id,something) AS (
          SELECT 21,'something 1 from 2'
UNION ALL SELECT 22,'something 2 from 2'
UNION ALL SELECT 23,'something 3 from 2'
)

-- then, I select from this, as you would - with the result you'd get:

SELECT * FROM table1,table2
 id |     something      | id |     something      
----+--------------------+----+--------------------
 11 | something 1 from 1 | 21 | something 1 from 2
 12 | something 2 from 1 | 21 | something 1 from 2
 13 | something 3 from 1 | 21 | something 1 from 2
 11 | something 1 from 1 | 22 | something 2 from 2
 12 | something 2 from 1 | 22 | something 2 from 2
 13 | something 3 from 1 | 22 | something 2 from 2
 11 | something 1 from 1 | 23 | something 3 from 2
 12 | something 2 from 1 | 23 | something 3 from 2
 13 | something 3 from 1 | 23 | something 3 from 2

-- and finally, what I am almost sure you should do:

SELECT * FROM table1 UNION ALL SELECT * FROM table2;
  id |     something      
 ----+--------------------
  11 | something 1 from 1
  12 | something 2 from 1
  13 | something 3 from 1
  21 | something 1 from 2
  22 | something 2 from 2
  23 | something 3 from 2

With clause in vertica sql

2 Answers2