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

kfruchtmankfruchtman Vertica Customer ✭
in General Discussion

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

Answers

  • lop2loplop2lop ✭
    edited June 2021

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

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
  • kfruchtmankfruchtman Vertica Customer ✭

    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

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...