# Case Expression on Vertica Question

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.
Keren

• edited June 14

@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.
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;

Other options ...

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

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-> SELECT e, f, g, h
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-> SELECT e, f, g, h
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-> SELECT e, f, g, h
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-> SELECT 2 which_query, NULL a, NULL b, NULL c, NULL d, 3, f, g, h
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(>            DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as "M/YYYY",
verticademos(>            Sum("Difference in sales") as "Diff from prev month"
verticademos(>      group by Catagory, months order by Catagory, months
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(>      group by Catagory,Quarters order by Catagory,Quarters
verticademos-> SELECT col1, col2, col3, col4 FROM (
verticademos(> SELECT which_case, Catagory col1, "M/YYYY" col2, sales col3, "Diff from prev month" col4
col1 |  col2  | col3 | col4
------+--------+------+------
test | 6/2021 |  210 |  420
(1 row)

verticademos(>            DATE_PART('month', months)||'/'||DATE_PART('YEAR', months) as "M/YYYY",
verticademos(>            Sum("Difference in sales") as "Diff from prev month"
verticademos(>      group by Catagory, months order by Catagory, months
verticademos(>            sum(Sales) as 'Sum of Sales per Quarter',
verticademos(>            sum("Difference in Sales") as 'Difference per Quarter'
verticademos(>      group by Catagory,Quarters order by Catagory,Quarters
verticademos-> SELECT col1, col2, col3, col4 FROM (
verticademos(> SELECT which_case, Catagory col1, "M/YYYY" col2, sales col3, "Diff from prev month" col4
col1  | col2 | col3 | col4
-------+------+------+------
test2 | 3    |  210 |  510
(1 row)
```

Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.