Case Expression on Vertica Question

@kfruchtman said:
Hi Guys!

I am trying to create a statement that will decide on a query to execute something like:

select 1,
CASE WHEN 1
THEN
(
select a,b,c,d from table1

)
ELSE
(
select e,f,g,h from table2

)
END
FROM dual

but I get the error: "Subquery must return only one column"

Is there a workaround for that somehow? I cannot see the "if" statements exist in Vertica also.
Thanks in advance,
Keren

Hi Keren, I did this and it seems to be working fine:

create table table1(a int,b int,c int,d int);
create table table2(e int,f int,g int,h int);

insert into table1 values(101,102,103,104);
insert into table1 values(111,112,113,114);
insert into table1 values(121,122,123,124);
insert into table1 values(131,132,133,134);
insert into table1 values(141,142,143,144);

insert into table2 values(201,202,203,204);
insert into table2 values(211,212,213,214);
insert into table2 values(221,222,223,224);
insert into table2 values(231,232,233,234);
insert into table2 values(241,242,243,244);

create table dual(selector int);

insert into dual values(1);
insert into dual values(1);
insert into dual values(0);
insert into dual values(1);

SELECT * FROM
(
select selector,
CASE WHEN selector != 1 THEN t2.e ELSE t1.a END col1,
CASE WHEN selector != 1 THEN t2.f ELSE t1.b END col2,
CASE WHEN selector != 1 THEN t2.g ELSE t1.c END col3,
CASE WHEN selector != 1 THEN t2.h ELSE t1.d END col4
FROM dual as d
FULL JOIN table1 AS t1 on selector = 1
FULL JOIN table2 AS t2 on selector = 0
) as r

WHERE r.col1 IS NOT null
ORDER BY selector,col1;

Hope you got your answer.

Other options ...

verticademos=> SELECT * FROM table1;
 a | b | c | d
---+---+---+---
 1 | 2 | 3 | 4
(1 row)

verticademos=> SELECT * FROM table2;
 e | f  | g  | h
---+----+----+----
 5 |  6 |  7 |  8
 9 | 10 | 11 | 12
(2 rows)

verticademos=> WITH wq AS (SELECT 1 which_query),
verticademos->      t1 AS (SELECT 1 which_query, a, b, c, d FROM table1),
verticademos->      t2 AS (SELECT 2 which_query, e, f, g, h FROM table2)
verticademos-> SELECT a, b, c, d
verticademos->   FROM wq
verticademos->   JOIN t1 USING(which_query)
verticademos-> UNION ALL
verticademos-> SELECT e, f, g, h
verticademos->   FROM wq
verticademos->   JOIN t2 USING(which_query);
 a | b | c | d
---+---+---+---
 1 | 2 | 3 | 4
(1 row)

verticademos=> WITH wq AS (SELECT 2 which_query),
verticademos->      t1 AS (SELECT 1 which_query, a, b, c, d FROM table1),
verticademos->      t2 AS (SELECT 2 which_query, e, f, g, h FROM table2)
verticademos-> SELECT a, b, c, d
verticademos->   FROM wq
verticademos->   JOIN t1 USING(which_query)
verticademos-> UNION ALL
verticademos-> SELECT e, f, g, h
verticademos->   FROM wq
verticademos->   JOIN t2 USING(which_query);
 a | b  | c  | d
---+----+----+----
 5 |  6 |  7 |  8
 9 | 10 | 11 | 12
(2 rows)

verticademos=> WITH wq AS (SELECT 2 which_query),
verticademos->      t1 AS (SELECT 1 which_query, a, b, c, d FROM table1),
verticademos->      t2 AS (SELECT 2 which_query, e, f, g, h FROM table2)
verticademos-> SELECT a col1, b col2, c col3, d col4
verticademos->   FROM wq
verticademos->   JOIN t1 USING(which_query)
verticademos-> UNION ALL
verticademos-> SELECT e, f, g, h
verticademos->   FROM wq
verticademos->   JOIN t2 USING(which_query);
 col1 | col2 | col3 | col4
------+------+------+------
    5 |    6 |    7 |    8
    9 |   10 |   11 |   12
(2 rows)

verticademos=> CREATE OR REPLACE VIEW table1_table2_vw AS
verticademos-> SELECT 1 which_query, a, b, c, d, NULL e, NULL f, NULL g, NULL h
verticademos->   FROM table1
verticademos->  UNION ALL
verticademos-> SELECT 2 which_query, NULL a, NULL b, NULL c, NULL d, 3, f, g, h
verticademos->   FROM table2;
CREATE VIEW

verticademos=> SELECT a, b, c, d FROM table1_table2_vw WHERE which_query = 1;
 a | b | c | d
---+---+---+---
 1 | 2 | 3 | 4
(1 row)

verticademos=> SELECT e, f, g, h FROM table1_table2_vw WHERE which_query = 2;
 e | f  | g  | h
---+----+----+----
 3 |  6 |  7 |  8
 3 | 10 | 11 | 12
(2 rows)

Thank you so much for the profound and helpful options but the truth is that the example I gave above is much simpler than my
actual situation.I tried to simplify it on my post because I thought that I just needed a way to "pick up" a query by the db somehow.
The real situation is something more like this:(The queries are both from the same table but different columns and different group bys):

Table Sales_Sums :
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-----------------+----------------------+-------------+------+---------+----------+-------------+-------------
public | Sales_Sum | Catagory | varchar(32) | 32 | | f | f |
public | Sales_Sum | Subcatagory | varchar(32) | 32 | | f | f |
public | Sales_Sum | Months | date | 8 | | f | f |
public | Sales_Sum | Quarters | varchar(44) | 44 | | f | f |
public | Sales_Sum | Sales | float | 8 | | f | f |
public | Sales_Sum | "Difference in Sales" | float | 8 | | f | f |

select 1,
CASE WHEN (value of variable is 1)
THEN
(
select Catagory,
DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as 'M/YYYY',sum(Sales) as sales,Sum("Difference in sales") as "Diff from prev month"
from Sales_Sums
where Catagory In (($Catagory))
group by Catagory,months order by Catagory,months

)
ELSE
(

select Catagory,Quarters,sum(Sales) as 'Sum of Sales per Quarter',sum("Difference in Sales") as 'Difference per Quarter'
from Sales_Sums
where CatagoryIn (($Catagory))
group by Catagory,Quarters order by Catagory,Quarters;

)
END
FROM dual

I will have to break down to years also also but I think you got the point.
Is there a way to do that?
Thanks again!!!
I appreciate it so much,
Keren

Maybe something like this?

WITH
  case1 AS (
    select 1 which_case,
           Catagory,
           DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as "M/YYYY",
           sum(Sales) as sales,
           Sum("Difference in sales") as "Diff from prev month"
      from Sales_Sums
     where Catagory In (('test'))
     group by Catagory, months order by Catagory, months
    ),
  case2 AS (
    select 2 which_case,
           Catagory,
           Quarters,
           sum(Sales) as 'Sum of Sales per Quarter', 
           sum("Difference in Sales") as 'Difference per Quarter'
      from Sales_Sums
     where Catagory In (('test2'))
     group by Catagory,Quarters order by Catagory,Quarters
    )
SELECT col1, col2, col3, col4 FROM (
SELECT which_case, Catagory col1, "M/YYYY" col2, sales col3, "Diff from prev month" col4
  FROM case1
UNION ALL
SELECT * FROM case2
) foo
WHERE which_case = 1; 

WITH
  case1 AS (
    select 1 which_case,
           Catagory,
           DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as "M/YYYY",
           sum(Sales) as sales,
           Sum("Difference in sales") as "Diff from prev month"
      from Sales_Sums
     where Catagory In (('test'))
     group by Catagory, months order by Catagory, months
    ),
  case2 AS (
    select 2 which_case,
           Catagory,
           Quarters,
           sum(Sales) as 'Sum of Sales per Quarter', 
           sum("Difference in Sales") as 'Difference per Quarter'
      from Sales_Sums
     where Catagory In (('test2'))
     group by Catagory,Quarters order by Catagory,Quarters
    )
SELECT col1, col2, col3, col4 FROM (
SELECT which_case, Catagory col1, "M/YYYY" col2, sales col3, "Diff from prev month" col4
  FROM case1
UNION ALL
SELECT * FROM case2
) foo
WHERE which_case = 2; 

Example:

verticademos=> SELECT * FROM Sales_Sums;
 Catagory | Subcatagory |   Months   | Quarters | Sales | Difference in Sales
----------+-------------+------------+----------+-------+---------------------
 test     | test-sub    | 2021-06-16 | 3        |   100 |                 200
 test     | test-sub    | 2021-06-16 | 3        |   110 |                 220
 test2    | test2-sub   | 2021-06-16 | 3        |   100 |                 250
 test2    | test2-sub   | 2021-06-17 | 3        |   110 |                 260
(4 rows)

verticademos=> WITH
verticademos->   case1 AS (
verticademos(>     select 1 which_case,
verticademos(>            Catagory,
verticademos(>            DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as "M/YYYY",
verticademos(>            sum(Sales) as sales,
verticademos(>            Sum("Difference in sales") as "Diff from prev month"
verticademos(>       from Sales_Sums
verticademos(>      where Catagory In (('test'))
verticademos(>      group by Catagory, months order by Catagory, months
verticademos(>     ),
verticademos->   case2 AS (
verticademos(>     select 2 which_case,
verticademos(>            Catagory,
verticademos(>            Quarters,
verticademos(>            sum(Sales) as 'Sum of Sales per Quarter',
  case1 AS (
    select 1 which_case,
           Catagory,
           DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as "M/YYYY",
           sum(Sales) as sales,
           Sum("Difference in sales") as "Diff from prev month"
      from Sales_Sums
     where Catagory In (('test'))
     group by Catagory, months order by Catagory, months
    ),
  case2 AS (
    select 2 which_case,
           Catagory,
           Quarters,
           sum(Sales) as 'Sum of Sales per Quarter',
           sum("Difference in Sales") as 'Difference per Quarter'
      from Sales_Sums
     where Catagory In (('test2'))
     group by Catagory,Quarters order by Catagory,Quarters
    )
SELECT col1, col2, col3, col4 FROM (
SELECT which_case, Catagory col1, "M/YYYY" col2, sales col3, "Diff from prev month" col4
verticademos(>            sum("Difference in Sales") as 'Difference per Quarter'
verticademos(>       from Sales_Sums
verticademos(>      where Catagory In (('test2'))
verticademos(>      group by Catagory,Quarters order by Catagory,Quarters
verticademos(>     )
verticademos-> SELECT col1, col2, col3, col4 FROM (
verticademos(> SELECT which_case, Catagory col1, "M/YYYY" col2, sales col3, "Diff from prev month" col4
verticademos(>   FROM case1
verticademos(> UNION ALL
verticademos(> SELECT * FROM case2
verticademos(> ) foo
verticademos-> WHERE which_case = 1;
 col1 |  col2  | col3 | col4
------+--------+------+------
 test | 6/2021 |  210 |  420
(1 row)

verticademos=>
verticademos=> WITH
verticademos->   case1 AS (
verticademos(>     select 1 which_case,
verticademos(>            Catagory,
verticademos(>            DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as "M/YYYY",
verticademos(>            sum(Sales) as sales,
verticademos(>            Sum("Difference in sales") as "Diff from prev month"
verticademos(>       from Sales_Sums
verticademos(>      where Catagory In (('test'))
verticademos(>      group by Catagory, months order by Catagory, months
verticademos(>     ),
verticademos->   case2 AS (
verticademos(>     select 2 which_case,
verticademos(>            Catagory,
verticademos(>            Quarters,
verticademos(>            sum(Sales) as 'Sum of Sales per Quarter',
verticademos(>            sum("Difference in Sales") as 'Difference per Quarter'
verticademos(>       from Sales_Sums
verticademos(>      where Catagory In (('test2'))
verticademos(>      group by Catagory,Quarters order by Catagory,Quarters
verticademos(>     )
verticademos-> SELECT col1, col2, col3, col4 FROM (
verticademos(> SELECT which_case, Catagory col1, "M/YYYY" col2, sales col3, "Diff from prev month" col4
verticademos(>   FROM case1
verticademos(> UNION ALL
verticademos(> SELECT * FROM case2
verticademos(> ) foo
verticademos-> WHERE which_case = 2;
 col1  | col2 | col3 | col4
-------+------+------+------
 test2 | 3    |  210 |  510
(1 row)