Case Expression on Vertica Question

kfruchtmankfruchtman 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

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
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file