While using Search parameters in Advanced Query the result list is always empty

While using Search parameters in Advanced Query the result list is always empty

I am porting a simple query to an advanced query.
The query has a couple of input Search parameters.
Search parameters should be set to text and inline=yes within the advanced query.
I also did an encodeSql on the inputs and use an IF statement to handle nullidentifiers where needed.
The advanced query works when all parameters are used.
However it returns nothing when 1 or more parameters are not filled.
I have tried several approaches but can't get the result I need.
Any tips or suggestions for this?
(see also picture)


Maybe you could solve your problem using something like this:

AND (@qSearch='' OR (ENTASK.[SUBJECT] LIKE (('%'+ @qSearch) + '%').....

The part with the @qSearch='' OR should make the query return results even if @qSearch is empty

Hope this helps


I don't think you need to had the
AND (@qSearch='' OR (ENTASK.[SUBJECT] LIKE (('%'+ @qSearch) + '%')

because when the @qSearch is empty the query accepts all
ENTASK.[SUBJECT] because of the metacharacter '%'.

I think that your problem is on the other search parameters. I need to check if the parameter is null. I usually use something like:

@qepTASK_TYPE_WISH = 0).  In this case when the parameter @qepTASK_TYPE_WISH is not filled, the second part of the expression returns true and the parameter is ignored.

On the other hand you don't need to set all parameters to text and with inline to yes, you can still use the datatypes they're supposed to be.

Hope this help,
Bruno Lourenço

Hi Bruno,

Thanks for you reply, the problem is indeed not that the @qSearch parameter doesn't work (it works) but that the search parameters (like qStatusID) from the simple query no longer function in the advance query.

If all parameters are filled in, it works.

If 1 of the search parameters (ID-->Text, inline=yes) is empty it allways returns zero data.
The search parameters in the adv. query are TaskTypeID, StatusID, PriorityID and TaskGroupID.

I use an IF on the query inputs to check on empty parts, like "If(inStatusID<>NullIdentifier(), EncodeSql(inStatusID), EncodeSql(NullIdentifier()))"
The @qepTASK_TYPE_Wish parameter is not an search parameter but a static entity. I fill it when calling the adv. query and it's never empty.

I also tried to go back to the original dataType (identifiers) instead of 'text and inline=yes' but that made no difference.

The problem (I expect) lays around the 4 parameters mentioned above but I can't figure out what is causing it.

Created a simple form of my complex query for testing. It showed the same problem: when the search parameter is empty; there are no results.
In the query below TaskTypeID is a search parameter (text, inline=yes). If it is filled, I got a result.
When it is empty there are no results.

I tried also variations whereby I use the original dataType and have exact the same results as using the text+inline=yes.

                Inner JOIN {TASK} ENTASK ON (ENTASK.[TYPE_ID] = ENTASK_TYPE.[ID]))
AND   ((ENTASK.[TYPE_ID]= @qTaskTypeID)  and   (ENTASK.[TYPE_ID] is not null))
AND   (ENTASK.[SUBJECT] LIKE (('%' + @qSearch) + '%'))

Mailed support about this issue.
Hi Hans,

What Bruno Lourenço said before is the solution to your problem, I'll try to demonstrate it in a smaller query just for clarification purposes.

First the simple query with Search Parameter = Yes:

Now the Advanced Query:

Hope this helps!

Hermínio Mira
Hi Herminio,
Thanks for you remark but I am not sure if this works.
The problem is related to input 'entity identifier' parameters 
So, translating your remark to my situation I should have:
AND   ((ENTASK.[TYPE_ID]= @qTaskTypeID)  or   (ENTASK.[TYPE_ID] is null))
instead of
AND   ((ENTASK.[TYPE_ID]= @qTaskTypeID)  and   (ENTASK.[TYPE_ID] is not null))
unfortunately this gives the same result as before.
But wait, we set the data type to Text/inline=yes so maybe this should work?
AND   ((ENTASK.[TYPE_ID]= @qTaskTypeID)  or   (ENTASK.[TYPE_ID] = ''))
This also does not work and inline=yes or no has no effect.

The problem is that you need to check if the input parameter is null and not the data field, right now you you have this:
AND   ((ENTASK.[TYPE_ID]= @qTaskTypeID)  or   (ENTASK.[TYPE_ID] = ''))

And what you need to evaluate is this:
AND   ((ENTASK.[TYPE_ID]= @qTaskTypeID)  or   (@qTaskTypeID = ''))

Try it like this and let me know if it worked!

Hermínio Mira
Yes, it works! Thank you (and Bruno).

Tested it in my test query and than build it in my real query.
Still testing the real query but the results so far are good.

The expand inline should be set to yes since the no doesn't work.