The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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.
Thanks in advance,
Keren
0
Answers
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 ...
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?
Example: