Can I sum up two columns from temporary tables together?

I have created 2 temporary tables which I need to sum . I.e.

CREATE temp TABLE XXX1 (DATE TEXT, PRICE INTEGER);
INSERT INTO XXX1 VALUES 
('2019-04-27 01:00', 1), ('2019-04-27 02:30', 3), ('2019-04-27 18:00',2), 
('2019-04-28 17:00', 2), ('2019-04-28 21:00', 5), 
('2019-04-29 17:00',50), ('2019-04-29 21:00',10), 
('2019-04-30 17:00',10), ('2019-04-30 21:00',20), 
('2019-05-01 17:00',40), ('2019-05-01 21:00',10), 
('2019-05-02 17:00',10), ('2019-05-02 21:00', 6);

CREATE temp TABLE XXX2 (Daydiff INTEGER);
INSERT INTO XXX2 VALUES 
(1), ( 3), (2), 
(2), ( 5), 
(50), (10), 
(10), (20), 
(40), (10), 
(10), (6);

I tried to use

select (DATE+Daydiff) as CorrectDate, Price from XXX2 XXX1

It went wrong. Then I tried Here it shows that Daydiff does not exist

select (DATE+(SELECT Daydiff from XXX2)) as CorrectDate, Price from XXX1

And this also cam with an error. Could you help me how to add the days from XXX2 to XXX1. So the first row should have 2019-04-28, next 2019-04-30... May the problem is that these two tables are temporary, but to the whole query I need these temp. tables.

Solution

@John Mayer:

As @Gordon Linoff pointed out: SQL is a descriptive language on un-ordered sets.

If you want to match the fifth tuple, your ('2019-04-28 21:00', 5) , with the other fifth tuple, your ( 5), then you need to uniquely identify the rows of both tables, with matching identifiers, and use a join functionality to get the info.

And, if you specify Vertica as the RDBMS, you should not use multiple tuples in the VALUES clause, as you do above, as Vertica does not support it.

If you want to create an in-line table in Vertica, you must use a SELECT .. UNION SELECT statement for that.

With these pontifications out of the way , try this:

Create your tables (whether temporary or permanent, does not matter) with a minimal due diligence concerning data types - where a DATE and a TIMESTAMP are two different things, and definitely not VARCHAR, let alone the strange TEXT you are using, and, if you need to join them later, with identifiers, and then fill them.

DROP TABLE IF EXISTS xxx1;
DROP TABLE IF EXISTS xxx2;
CREATE TABLE xxx1 (
  id    INTEGER
, dt    TIMESTAMP(0)
, price INTEGER
);

CREATE TABLE xxx2 (
  id      INTEGER
, daydiff INTEGER
);

INSERT INTO xxx1
          SELECT  1, TIMESTAMP '2019-04-27 01:00', 1
UNION ALL SELECT  2, TIMESTAMP '2019-04-27 02:30', 3                                                                                                                
UNION ALL SELECT  3, TIMESTAMP '2019-04-27 18:00', 2
UNION ALL SELECT  4, TIMESTAMP '2019-04-28 17:00', 2
UNION ALL SELECT  5, TIMESTAMP '2019-04-28 21:00', 5
UNION ALL SELECT  6, TIMESTAMP '2019-04-29 17:00',50
UNION ALL SELECT  7, TIMESTAMP '2019-04-29 21:00',10
UNION ALL SELECT  8, TIMESTAMP '2019-04-30 17:00',10
UNION ALL SELECT  9, TIMESTAMP '2019-04-30 21:00',20
UNION ALL SELECT 10, TIMESTAMP '2019-05-01 17:00',40
UNION ALL SELECT 11, TIMESTAMP '2019-05-01 21:00',10
UNION ALL SELECT 12, TIMESTAMP '2019-05-02 17:00',10
UNION ALL SELECT 13, TIMESTAMP '2019-05-02 21:00', 6
;

INSERT INTO xxx2
          SELECT   1, 1
UNION ALL SELECT   2, 3
UNION ALL SELECT   3, 2
UNION ALL SELECT   4, 2
UNION ALL SELECT   5, 5
UNION ALL SELECT   6,50
UNION ALL SELECT   7,10
UNION ALL SELECT   8,10
UNION ALL SELECT   9,20
UNION ALL SELECT  10,40
UNION ALL SELECT  11,10
UNION ALL SELECT  12,10
UNION ALL SELECT  13, 6
;

Then, run a query that joins the two tables, like so:

SELECT 
  dt::DATE + daydiff AS correct_date
, TIMESTAMPADD(day,daydiff,dt) AS correct_timestamp
, price
FROM xxx1
JOIN xxx2 
USING(id)
-- (or: ON xxx1.id = xxx2.id)
;