Options

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

  • Options
    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.

  • Options
    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)
  • Options
    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

  • Options
    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 ...