Nested query is not completely case insensitive
I am creating a nested query as the attribute I am checking is a dynamic list (Users). If I just execute the inner query and set case insensitive then the query runs fine. But if the inner query is now nested then the inner query part does not become case insensitive, and if the inner query (case sensitive now) returns 0 results then an exception is thrown that inner query cannot return an empty set.
Any workaround for this? It is a serious limitation if I have to tell a client that they have to enter an exact value for dynamic attributes.
Just for clarification these are the queries:
returnVal = “([11614] == ‘” + firstname + “‘ || [11616] == ‘” + lastname + “‘)”;
Inner query:
String innerQuery = “select * from [” + cls.getId().toString() + “]” + “where” + returnVal;
Outer query:
String outerQuery = “select * from [9000] where [1420] in” + returnVal;
I set the query.setCaseSensitive(false) after creating the query. For the outer query an exception is thrown at time of creation.
furqantariq1992,
Did you resolve this? And if yes, what did you find out? Could you leave some insight for us here?
This is how I resolved the issue:
1) Created the inner query and set to case insensitive using sql syntax like Select * from [classname] where [mywhereclauses…]
2) Created the outer query but only the select part like “Select * from [classname]
3) Set this outerquery to case insensitive
4) Build the whereclause separately for this outerquery using the inner query
5) Set the whereclause –> query.setCriteria(myWhereClauseUsingTheInnerQuery)
6) Execute
Hope this helps