How get all rows for dynamic parameter in hive

user_16356465 0
Viewed 32

I have a below table having some data. If I applied category='A+' then I will get only 'A+' records. But If I applied category='A+1' then I want to list out all records including both A+ & A+1.

I tried many ways but I couldn't able to get it. I'm beginner and learning SQL.

Please anyone help on this.

*category value is dynamic input value. We will get either A+ or A+1. So based on input output will be generated.

**     
      Query: hive -e "select * from db.testing where category='$input';"
    
        ID      Name    marks     category
        1001    Smith    421        A+
        1002    Alex     419        A+
        1003    Martin   441        A+
        1004    Andy     463        A+1
        1005    Johnson  437        A+
        1006    Ajay     401        A+
        1007    Marnus   423        A+
        1008    Bhupal   459        A+1

**

hiveql hive where-clause sql
1 Answers

Use case expression to generate required boolean value used in WHERE to filter rows:

hive -e "select * from db.testing where case when '$input'='A+' then category='A+' when '$input'='A+1' then ( category='A+' OR  category='A+1') else category='$input' end;"
user_27003440
Related