Support Questions
Find answers, ask questions, and share your expertise

Dynamic where condition using UDF

Dynamic where condition using UDF

New Contributor

we want to run a hive query which has a dynamic where condition.

Select * from table where { this condition is dynamic}

 

We have written a dynamic where UDF and dynamic_where() returns us the dynamic where condition string like below -

1 and ndc_cd='002' and generic_nm = 'MEP'

 

But the query doesn't get us the results when we run it using the dynamic_where() udf.

Select * from table where 1 = dynamic_where();

 

the dynamic_where returns the string correctly when we run select dynamic_where ();

 

Can a dynamic where be used in a condition using UDFs. If yes then how to achieve it and what are we doing incorrect.

 

Is there another better way to do it.

 

 

 

2 REPLIES 2
Highlighted

Re: Dynamic where condition using UDF

Guru
Hi Mamta,

- Do you have examples on what dynamic_where() function will return?
- Is your WHERE clause like "1 = dynamic_where()"? Or is it just an example?
- what's the EXPLAIN EXTENDED output for this query?

Cheers
Eric
Highlighted

Re: Dynamic where condition using UDF

New Contributor

Hi Eric,

To answer your questions:-

 1) Do you have examples on what dynamic_where() function will return?

     Answer - the dynamic_where() returns a string , which is a where condition like "1 and ndc_cd='002' and generic_nm = 'MEP'", this return string is dynamic and based on logged-in user,hence dynamic where condition.

 

2) Is your WHERE clause like "1 = dynamic_where()"? Or is it just an example?

     Answer - yes this is the where claus.


3) what's the EXPLAIN EXTENDED output for this query?

    I ran it for the query but couldn't get much info.

 

Basically, I want to execute a query in which the where claus is dynamic and comes from a UDF.

 

Don't have an account?