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.
Case-Insensitive querying with SQL Server 2012
Re: Case-Insensitive querying with SQL Server 2012
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.
thats where you need to do this - and you never need to worry about what you are trying in your example, OR user filtering.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Case-Insensitive querying with SQL Server 2012
Music to my ears. Thanks so much, Jaymer!
Re: Case-Insensitive querying with SQL Server 2012
Jaymer, I'm sorry - are you referring to a setting within AwareIM or SQL Server?
Re: Case-Insensitive querying with SQL Server 2012
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.
Thanks again.
-
- Posts: 7526
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Re: Case-Insensitive querying with SQL Server 2012
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?
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?
Aware IM Support Team
Re: Case-Insensitive querying with SQL Server 2012
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.
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.
-
- Posts: 7526
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Re: Case-Insensitive querying with SQL Server 2012
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.
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.
Aware IM Support Team
Re: Case-Insensitive querying with SQL Server 2012
Sending the BSV. Thank you.
-
- Posts: 7526
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Re: Case-Insensitive querying with SQL Server 2012
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.
Aware IM Support Team