hive query with multiple where IN clause with AND type logical connection

Question

I have a web application with hive db at back end from which users selects 4 different type of values and related data is displayed on UI.

Example the fields may be:

• Project ID
• Sub-portfolio
• Project Status
• Project Finance Status

User can select different values for the fields mentioned ( single or multiple) and request for data.

I have a single hive query with multiple where IN clause and combined with AND logical operator to generate the data.

All is good untill user is selecting atleast one value for each field. But if the user selects filters for 3 fields and leaves on blank then '' is passed to IN clause in where condition and no data is displayed. As none of the rows match the condition.

Where as if a field is not selected I have to pass something in IN clause to get all the data for that field or to remove IN clause for that field.

To check everytime if a value is selected before creating a query will involve a lot of cases.

If anyone can suggest a single query with multiple where IN, and pass some parameter to get all the rows for a fields where no value is selected.

Note: I am using hive JDBC queries.

Answer 1

You can handle this issue with constructs like:

where ($subporfolio is null or subportfolio = $subportfolio) and
      ($status is null or status = $status) and
      . . .

Of course the in null might be = '' depending on how you are passing the values.

Answer 2

I found creating dynamic query to be better. I looped in through all the input parameters and checked if its values is not null or empty only then it is added in the where IN clause in query.