Case Expression on Vertica Question
kfruchtman
Vertica Customer ✭
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: