SQL problem when using <%AND(filtername,column_name like ?)%>

[former_member365423]

Hello Experts

I am trying to create a query that has optional Filters using the Filter <%AND(filtername,column_name like ?)%> token.

i want for my query to return everything if the user does not specify anything in the filter.

when i use this token nothing is returned by my query.

does anybody know how to use this token <%AND(filtername,column_name like ?)%> for optional filters.

your help will be greatly appreciated.

kind regards,

Thabiso Kamanga

[vikram_shanmugasundaram]

Hi Thabiso,

For optional filters you could use the AND_OP token. So for example, if the filter prompt is for filtering using the document display name, the syntax would be

<%AND_OP("UPPER(T1.DISPLAY_NAME)",<%?(T1.DISPLAY_NAME)%>)%>

Note, the second occurrence of T1.DISPLAY_NAME is the name of the filter parameter defined on the Filter Prompts tab.

You can set a default value for the filter by entering the value in the Default Value field of the filter prompt. If you intend to use a wildcard as the default value, you need to take into consideration the volume. If the query returns a large result it might not be a good idea to use the wildcard as the default value.

The help section provides more information of the usage of the AND_OP token that you might find useful.

Regards,

Vikram