How to use SQL so the one Select is used if one condition and another Select if another condition

Question

I have a table A, which is created when I use SELECT_A code and Table B, which is used with SELECT_B code. Additionally I have table C, where one of the columns is DATE. I want to create a query so that if maximum (latest) date in Table C (say it is in table SELECT_C) is lower than today so SQL runs SELECT_A query else SELECT_B query.

However, I do not know how it can be technically possible. I was thinking to use this method, but it did not work:

CREATE temp TABLE SELECT_A (DATE TEXT, PRICE INTEGER);
INSERT INTO SELECT_A VALUES 
('2019-04-27 01:00', 1), ('2019-04-27 02:30', 3), ('2019-04-27 18:00',2), 

CREATE temp TABLE SELECT_B (DATE TEXT, PRICE INTEGER);
INSERT INTO SELECT_B VALUES 
 ('2019-05-02 21:00', 6);


CREATE temp TABLE SELECT_C (DATE TEXT, PRICE INTEGER);
INSERT INTO SELECT_C VALUES 
 ('2020-05-02', 55555);

So, as date in column DATE in table SELECT_C is lower than today, SQL should run SELECT_B

if ((select(max(DATE::TIMESTAMP) from SELECT_C)<current_date) then SELECT_A else SELECT_B

Can you help?

Answer 1

Please, please - use Vertica syntax when creating a Vertica example!

And please stop using TEXT as a data type. It makes me nervous ...

I also suggest you read a beginner's SQL tutorial. There is nothing hierarchical per se in a relational database....

@Gordon Linoff's answer works. SQL has no IF. It is a descriptive, not a procedural or object oriented language. Any Boolean evaluation to filter data happens in the WHERE condition. And if the WHERE condition is unconditionally TRUE, all rows of the query (or, in our case, of the branch of the UNION SELECT in question) are returned.

I'm only shamelessly taking advantage that select_c is a single_row table, where @Gordon Linoff is more careful than me.

  1 WITH                                                                                                                                                                                                                                      
  2 select_a (dt, price) AS (
  3           SELECT TIMESTAMP '2019-04-27 01:00', 1
  4 UNION ALL SELECT TIMESTAMP '2019-04-27 02:30', 3
  5 UNION ALL SELECT TIMESTAMP '2019-04-27 18:00', 2
  6 )
  7 ,
  8 select_b (dt, price) AS (
  9           SELECT TIMESTAMP '2019-05-02 21:00', 6
 10 )
 11 ,
 12 select_c (dt, price) AS (
 13           SELECT DATE '2020-05-02', 55555
 14 )
 15 SELECT
 16  a.*
 17 FROM select_a a CROSS JOIN select_c c
 18 WHERE c.dt < CURRENT_DATE
 19 UNION ALL SELECT
 20  b.*
 21 FROM select_b b CROSS JOIN select_c c
 22 WHERE c.dt >= CURRENT_DATE
 23 ;