Nesting functions in 'where' in 4.3

[gmor]

Hi,

Very quick question that someone may be able to answer.

In a complex form search that we have, we use the 'where' function to filter the results. So that the data is case-insensitive, we use the 'lower' function.

In version 4.2.5, this syntax works fine:

where like(lower(Room),lower("%MB.1%"))

('Room' is a field in the search, the other data is user entered so can vary wildly)

In version 4.3, I get the error:

"Error in 'where' command: The arguments to the 'like' function are invalid."

Is this an error, or can we no longer nest functions?

Any advice or suggestions would be appreciated.

Thanks,

Graham.

[woodcock]

It works in v6 but why are you lowering your match string; why not just lower it yourself like this:

where like(lower(Room),"%mb.1%"))

Assuming that there actually is some valid reason for needing this (e.g. you are using a $token$ for the match string), switch from SQL-type like to RegEx-type match and use the ignorecase inline token like this:

where match(lower(Room),"(?i)MB.1"))

[gmor]

Hi. Wow, this takes me back a few years...

Thanks for your answer.

Yes, the question did relate to the use of a $token$ in an Advanced XML View. Otherwise yes, I get that I could turn off Cap Lock and type the string in lowercase(!)

But thank you for the suggestion of using the match function. I hadn't come across it before and I think it will prove useful in a number of different situations.

[woodcock]

I am mining older unanswered questions for karma so please accept my answer. I am glad it was at least educational, if it could not be useful.