DB2 case-insensitive search

[jDanM]

I am trying to use Upper() or Lower() combined with VBscript ucase/lcase to perform a case-insensitive search on DB2. My script produces SQL like the following:

Select * From INFO, Sources, Banks, Services, Verify, Importance WHERE Verify.VerifyID = Info.Quality AND Importance.ImportanceID = Info.Importance AND (Info.SourceCode = Sources.SourceID) AND (Info.BankCode = Banks.BankID) AND (Info.ServiceCode = Services.ServiceID) AND ( lower(SERVICENAME) LIKE '%test%' OR lower(ACTCODE) LIKE '%test%' OR lower(BANKNAME) LIKE '%test%' OR lower(TITLE) LIKE '%test%' OR lower(PRICE) LIKE '%test%' OR lower(NOTES) LIKE '%test%' OR lower(SOURCENAME) LIKE '%test%' OR lower(USER) LIKE '%test%' )

From everything I've read on using these functions in DB2 this looks like good SQL, but it produces this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[IBM][CLI Driver][DB2/NT] SQL0440N No function by the name "LOWER" having compatible arguments was found in the function path. SQLSTATE=42884

Any help would be greatly appreciated. The columns being searched are all varchar or long varchar.

 

[sathyarams]

Can you post the table DDL ?

I think
lower(PRICE) LIKE '%test%'
is the culprit

Cheers
Sathyaram

For db2 resoruces visit

http://www.db2click.com

More DB2 questions answered at

http://www.dbforums.com/f8

&

http://www.idug.org/user/UserLogin.asp

 

[jDanM]

Price is actually a varchar field. I've found out that the long varchar field is the one giving trouble- "NOTES". I've built a workaround at the moment to check for the uppercase, lowercase and proper case of any search term, but that's a little novice. If you have any ideas on how to work with these functions on a long varchar field please let me know. Thanks-

The main table DLL is below.

CREATE TABLE "DB2ADMIN"."INFO" (
"ACTCODE" VARCHAR(20) ,
"SERVICECODE" BIGINT ,
"BANKCODE" BIGINT ,
"TITLE" VARCHAR(80) ,
"PRICE" VARCHAR(20) ,
"USER" VARCHAR(80) ,
"INPUTDATE" DATE ,
"NOTES" LONG VARCHAR ,
"SOURCECODE" BIGINT ,
"OTHERSOURCE" VARCHAR(80) ,
"INFOID" BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +0 , INCREMENT BY +1 , NO CACHE ) ,
"IMPORTANCE" BIGINT WITH DEFAULT 0,
"EDITDATE" DATE ,
"QUALITY" BIGINT WITH DEFAULT 0,
"EDITUSER" VARCHAR(80) ,
"DELETED" BIGINT WITH DEFAULT 0,
"DELETEDBY" VARCHAR(60) )
IN "USERSPACE1" ;

 

[MarcLodge]

JDanM,
The lower/lcase function will only work on CHAR or VARCHAR fields, as documented here:

http://www-306.ibm.com/cgi-bin/db2w...rt/document.d2w/report?fn=db2v7s0sqls04c6.htm

so I guess your LONG VARCHAR is not acceptable. I was going to suggest messing about with SOUNDEX() but that looks like it only works on similar formats.

It looks like you may have to work with your work around. Sorry to not be of more help.

Marc