managing multiple filter values in Advance sql 

managing multiple filter values in Advance sql 

I have multiple filter option in the screen and data is fetched using advance sql. How can I manage the filter values not selected (null values). I tried declearing variables in advanced sql but it's not supported. 

I am sure there will be many business cases where simple query will not work out. How do me manage such scenarios?

Is it recommended to use Stored procedures in such scenarios?
Hi Vivek,

I think you should try using the Expand Inline Property of the input parameters in your Advance Query. That way you can programmatically inject fragments of code in your SQL Statement when needed. 

It is something like, IF you have your filter variable is not null, THEN insert and Entity.Attribute = 'FilterVariable' as your input parameter inside Advance Query,ELSE insert blanks ''

HTH, Best Regards,
Mark Chua
Hi Vivek,

Like Mark said you can use expand inline for more complex filters or even inject subqueries. But if you only need to control the filters you can still use a simple query, you can change a property in the input parameter to search, this way the plataform make by itself the clause Entity.Attribute = FilterValue or FilterValue is null


Hi Nuno, Thank you for your response.
I am in a situation where I cann't use the simple query OR search parameter is really great help.

I have to execute the Advance SQL which should take care all the filter options too.

In that case make yourself the logic in the WHERE clause of the query. Something  like:

Select {ENTITY}.*
Where (@FilterValue is null or {Entity}.[Attribute] = @FilterValue)
    and (@FilterValue2 is null or {Entity}.[Attribute2] = @FilterValue2)

Hope that helps,