If you have questions or if you want to share your opinion about Aware IM post your message on this forum
#54278 by Stetson
Tue Jul 07, 2020 2:50 pm
When searching for matches for user-entered AwareIM form fields in an external SQL Server 2012 db, what's the best way to make the query case-insensitive?

The following statements (using SQL Server functions and AwareIM functions respectively) generate the error "Internal error. Unable to convert query condition":

FIND Param2 WHERE upper(Param2.VENDOR_VNAME) CONTAINS upper(Param1.VenName1)
FIND Param2 WHERE to_upper_case(Param2.VENDOR_VNAME) CONTAINS to_upper_case(Param1.VenName1)

The following statement only finds matches when the case in the db EXACTLY matches the case entered by the user on the AwareIM form (which is not what I need):

FIND Param2 WHERE Param2.VENDOR_VNAME CONTAINS Param1.VenName1

How do I make this search case-insensitive? Thanks.
#54279 by Jaymer
Tue Jul 07, 2020 3:41 pm
there's a MSSQL setting to be case insensitive.
thats where you need to do this - and you never need to worry about what you are trying in your example, OR user filtering.
#54282 by Stetson
Tue Jul 07, 2020 8:06 pm
Jaymer, if you're referring to a setting within SQL Server, this is not something we can change. Is there a way to implement case-insensitive SQL Server searching from within AwareIM?

Thanks again.
#54290 by aware_support
Wed Jul 08, 2020 6:14 am
FIND Param2 WHERE to_upper_case(Param2.VENDOR_VNAME) CONTAINS to_upper_case(Param1.VenName1)

This should work in theory. Can you check SQL that this gets translated into (you can enable SQL in the Log Settings if running 8.4). Maybe there is a bug somewhere. What exactly is the error message you are getting?
#54295 by Stetson
Wed Jul 08, 2020 4:42 pm
I enabled SQL in our log settings, however no SQL gets generated for this FIND statement. Only the error "Internal error. Element TO_UPPER_CASE in query condition …<query>... cannot be converted to the currently installed version of SQL".

Our SQL Server version is 2012 SP1, 11.0.3156.0 (X64).

Is there a workaround? Please advise. The case of the data being searched varies in our DB due to mergers and system consolidations, so case-insensitive searching is essential in order for this AwareIM application to be successful.

Thanks very much.
#54301 by aware_support
Thu Jul 09, 2020 2:11 am
This works for us. The error message you are getting indicates that there is something wrong with the name of the TO_UPPER_CASE function. Are you sure you are spelling it right in your BSV?

This is the query we are using and it works:
FIND T2 WHERE TO_UPPER_CASE(T1.Name) CONTAINS TO_UPPER_CASE(T2.Name)

If you are sure your query is correct prepare a small BSV that demonstrates the problem and send it to [email protected] describing the steps required to reproduce the problem in this BSV.
#54318 by aware_support
Fri Jul 10, 2020 4:46 am
The issue has nothing to do with the TO_UPPER_CASE function. It is because the function is applied to an object stored externally. Currently you cannot use any Aware IM functions in queries if a query is on an external object. The workaround is to use a stored procedure.

Who is online

Users browsing this forum: Google [Bot] and 18 guests