HQL, insert two rows if a condition is met-CodePudding

I have the following table called table_persons in Hive:

 -------- ------ ------------ 
| people | type | date       |
 -------- ------ ------------ 
| lisa   | bot  | 19-04-2022 |
| wayne  | per  | 19-04-2022 |
 -------- ------ ------------

If type is "bot", I have to add two rows in the table d1_info else if type is "per" i only have to add one row so the result is the following:

 --------- ------ ------------ 
| db_type | info | date       |
 --------- ------ ------------ 
| x_bot   | x    | 19-04-2022 |
| x_bnt   | x    | 19-04-2022 |
| x_per   | b    | 19-04-2022 |
 --------- ------ ------------

How can I add two rows if this condition is met? with a Case When maybe?

CodePudding user response：

You may try using a union to merge or duplicate the rows with bot. The following eg unions the first query which selects all records and the second query selects only those with bot.

Edit

In response to the edited question, I have added an additional parity column (storing 1 or 0) named original to differentiate the duplicate entry named

    SELECT
        p1.*,
        1 as original
    FROM
        table_persons p1
    UNION ALL
    SELECT
        p1.*,
        0 as original
    FROM
        table_persons p1
    WHERE p1.type='bot'

You may then insert this into your other table d1_info using the above query as a subquery or CTE with the desired transformations CASE expressions eg

INSERT INTO d1_info
  (`db_type`, `info`, `date`)
WITH merged_data AS (
    SELECT
        p1.*,
        1 as original
    FROM
        table_persons p1
    UNION ALL
    SELECT
        p1.*,
        0 as original
    FROM
        table_persons p1
    WHERE p1.type='bot'
)
SELECT
    CONCAT('x_',CASE
           WHEN m1.type='per' THEN m1.type
           WHEN m1.original=1 AND m1.type='bot' THEN m1.type
           ELSE 'bnt'
    END) as db_type,
    CASE
       WHEN m1.type='per' THEN 'b'
       ELSE 'x'
    END as info,
    m1.date
FROM
    merged_data m1
ORDER BY m1.people,m1.date;

See working demo db fiddle here

CodePudding user response：

I think what you want is to create a new table that captures your logic. This would simplify your query and make it so you could easily add new types without having to edit logic of a case statement. It may also make it cleaner to view your logic later.

CREATE TABLE table_persons (
  `people` VARCHAR(5),
  `type` VARCHAR(3),
  `date` VARCHAR(10)
);

INSERT INTO table_persons
VALUES
  ('lisa', 'bot', '19-04-2022'),
  ('wayne', 'per', '19-04-2022');
  


CREATE TABLE info (
  `type` VARCHAR(5),
  `db_type` VARCHAR(5),
  `info` VARCHAR(1)
);

insert into info 
   values 
   ('bot', 'x_bot', 'x'), 
   ('bot', 'x_bnt', 'x'), 
   ('per','x_per','b');

and then you can easily do a join:

select 
  info.db_type, 
  info.info, 
  persons.date date 
from 
  table_persons persons inner join info 
on 
  info.type = persons.type