CODEWITHSUNDEEP
hadoophiveconditional-statementshiveql
Felipe
Felipe

Reputation: 87

CASE WHEN conditional in HiveQL

I'm having trouble bringing a case then into HiveQL.

I have a column with data: if column is NULL = FALSE if the column is PEOPLE = TRUE. It returns all results as TRUE.

What's wrong with my role?

SELECT 
    id,
    datetime,
    CASE 
        WHEN tb_people !="" THEN 'TRUE'
        ELSE 'FALSE'
    END
 FROM <BD>.<TABLE>

Upvotes: 0

Views: 1107

Answers (1)

Ben Watson
Ben Watson

Reputation: 5521

!="" is only checking for blank strings. "" and NULL are two different things. As such, what you're looking for is:

SELECT 
    id,
    datetime,
    CASE 
        WHEN tb_people IS NOT NULL THEN 'TRUE'
        ELSE 'FALSE'
    END
 FROM <BD>.<TABLE>

I'd also recommend using the boolean true and false rather than string literals. This would let you replace your entire case statement with ifnotnull(tb_people) https://hive.apache.org/javadocs/r3.0.0/api/org/apache/hadoop/hive/ql/exec/vector/expressions/IsNotNull.html.

Upvotes: 1

Related Questions