Hive - ELSE in SUM

Question

The following runs fine on as SQL on an RDBMS database. However, this fails to run on hive.

SELECT
        entry_date,
        customer,
        cust_loc,
        SUM(run_time) AS TOTAL_RUN,
        SUM(CASE WHEN run_time BETWEEN 10000 AND 20000 THEN entry_date ELSE 0 END) AS SLOT_1,
        SUM(CASE WHEN run_time BETWEEN 200001 AND 30000 THEN entry_date ELSE 0 END) AS SLOT_2,
        SUM(CASE WHEN run_time BETWEEN 31000 AND 40000 THEN entry_date ELSE 0 END) AS SLOT_3
FROM
        cust_run_details
WHERE
        app_env IN ('a','b')
        AND entry_date = '2015-02-01'
        AND flag_set='U'
GROUP BY
        customer,
        customer,
        cust_loc

The error I get when I run on HIVE is

FAILED: SemanticException [Error 10016]: Line 6:80 Argument type mismatch '0': The expression after ELSE should have the same type as those after THEN: "string" is expected but "int" is found

How can I convert this to Hive equivalent? Please any ideas would help me here

Answer 1

Assuming that entry_date is a date, you probably do not want to sum() it. If you want the sum of the run times in the different ranges, then you want a select like this:

 SELECT entry_date,
        customer,
        cust_loc,
        SUM(run_time) AS TOTAL_RUN,
        SUM(CASE WHEN run_time BETWEEN 10000 AND 20000 THEN run_time ELSE 0 END) AS SLOT_1,
        SUM(CASE WHEN run_time BETWEEN 200001 AND 30000 THEN run_time ELSE 0 END) AS SLOT_2,
        SUM(CASE WHEN run_time BETWEEN 31000 AND 40000 THEN run_time ELSE 0 END) AS SLOT_3

If you want a count of the run times in the different ranges, then:

 SELECT entry_date,
        customer,
        cust_loc,
        SUM(run_time) AS TOTAL_RUN,
        SUM(CASE WHEN run_time BETWEEN 10000 AND 20000 THEN 1 ELSE 0 END) AS SLOT_1,
        SUM(CASE WHEN run_time BETWEEN 200001 AND 30000 THEN 1 ELSE 0 END) AS SLOT_2,
        SUM(CASE WHEN run_time BETWEEN 31000 AND 40000 THEN 1 ELSE 0 END) AS SLOT_3

If entry_date could be NULL and you want to ignore those records, then you can include and entry_date is not null in the WHEN condition.