I'm having great difficultly making my DB2 (AS/400) queries case insensitive.
For example:
SELECT *
FROM NameTable
WHERE LastName = 'smith'
Will return no results, but the following returns 1000's of results:
SELECT *
FROM NameTable
WHERE LastName = 'Smith'
I've read of putting SortSequence/SortType into your connection string but have had no luck... anyone have exepierence with this?
Edit:
Here's the stored procedure:
BEGIN
DECLARE CR CURSOR FOR
SELECT T . ID ,
T . LASTNAME ,
T . FIRSTNAME ,
T . MIDDLENAME ,
T . STREETNAME || ' ' || T . ADDRESS2 || ' ' || T . CITY || ' ' || T . STATE || ' ' || T . ZIPCODE AS ADDRESS ,
T . GENDER ,
T . DOB ,
T . SSN ,
T . OTHERINFO ,
T . APPLICATION
FROM
( SELECT R . * , ROW_NUMBER ( ) OVER ( ) AS ROW_NUM
FROM CPSAB32.VW_MYVIEW
WHERE R . LASTNAME = IFNULL ( @LASTNAME , LASTNAME )
AND R . FIRSTNAME = IFNULL ( @FIRSTNAME , FIRSTNAME )
AND R . MIDDLENAME = IFNULL ( @MIDDLENAME , MIDDLENAME )
AND R . DOB = IFNULL ( @DOB , DOB )
AND R . STREETNAME = IFNULL ( @STREETNAME , STREETNAME )
AND R . CITY = IFNULL ( @CITY , CITY )
AND R . STATE = IFNULL ( @STATE , STATE )
AND R . ZIPCODE = IFNULL ( @ZIPCODE , ZIPCODE )
AND R . SSN = IFNULL ( @SSN , SSN )
FETCH FIRST 500 ROWS ONLY )
AS T
WHERE ROW_NUM <= @MAXRECORDS
OPTIMIZE FOR 500 ROW ;
OPEN CR ;
RETURN ;
DB2 is not case sensitive, and all names default to uppercase. DB2 objects include tables, views, columns, and indexes.
In computing, if a written word such as a password is case-sensitive, it must be written in a particular form, for example using all capital letters or all small letters, in order for the computer to recognize it.
For the most part MS-DOS, the Windows command line, and most of the commands are not case-sensitive. Unlike case-sensitive command line operating systems, like Linux, commands such as the MS-DOS dir command can be typed as DIR, Dir, or dir. In a case-sensitive operating systems, only the lowercase command would work.
SQL Server is, by default case insensitive; however, it is possible to create a case sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine a database or database object is by checking its “COLLATION” property and look for “CI” or “CS” in the result.
Why not do this:
WHERE lower(LastName) = 'smith'
If you're worried about performance (i.e. the query not using an index), keep in mind that DB2 has function indexes, which you can read about here. So essentially, you can create an index on upper(LastName)
.
EDIT To do the debugging technique I discussed in the comments, you could do something like this:
create table log (msg varchar(100, dt date);
Then in your SP, you can insert messages to this table for debugging purposes:
insert into log (msg, dt) select 'inside the SP', current_date from sysibm.sysdummy1;
Then after the SP runs, you can select from this log table to see what happened.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With