I have data base ,Vertica,
I create tableC & tableB with this value:
CREATE TABLE tst.tableC(id int,ts timestamptz,cl1_ varchar(30),cl2_ varchar(30),cl3_ varchar(30)); INSERT INTO tst.tableC VALUES(1, '2020-12-22', 'value1', 'value2', 'value3'); INSERT INTO tst.tableC VALUES(1, '2020-12-23', 'value4', 'value5', 'value6'); INSERT INTO tst.tableC VALUES(1, '2020-12-24', 'value7', 'value8', 'value9'); CREATE TABLE tst.tableB( id int, w1_ varchar(30),w2_ varchar(30),w3_ varchar(30)); INSERT INTO tst.tableB VALUES(1, 'b1','b2', 'b3');
this Query don't Run at all :
WITH
list1 AS (SELECT 'id' AS ' ','ts' AS ' ',(SELECT w1_ FROM tst.tableB LIMIT 1) AS ' ',
(SELECT w2_ FROM tst.tableB LIMIT 1) AS ' ', (SELECT w3_ FROM tst.tableB LIMIT 1) AS ' '),
list2 AS (SELECT c.id::varchar, c.ts::varchar , cl1_, cl2_, cl3_ FROM tst.tableC c inner join tst.tableB b on b.id=c.id),
list3 AS (SELECT * FROM list1 UNION ALL SELECT * FROM list2)
SELECT * FROM list3 ORDER BY 1 DESC;
also this one:
Select * from (SELECT 'id' AS ' ','ts' AS ' ',(SELECT w1_ FROM tst.tableB LIMIT 1) AS ' ',
(SELECT w2_ FROM tst.tableB LIMIT 1) AS ' ', (SELECT w3_ FROM tst.tableB LIMIT 1) AS ' '
UNION ALL
SELECT c.id::varchar, c.ts::varchar , cl1_, cl2_, cl3_ FROM tst.tableC c inner join tst.tableB b on b.id=c.id
)d
If I run in the -vsql or Dbeaver it's run ok.