Case-Insensitive Sorting and Record Selection in SQL/400

Hey, Ted:

The Microsoft Access database management system, which I use in my work, is not sensitive to case when it selects records and sorts data. To find, for example, all the customers who live in a certain state, I can enter the state code in uppercase, lowercase, or mixed case, and Access finds the data.

I do not even begin to claim that Access is anywhere comparable to DB2/400, but this is one feature that I like, and I find it hard to believe that Access can do something that DB2/400 cannot do. Is it possible to make SQL/400 do case-insensitve record selection and sorting?

--Chad

Yes, and I would like to thank Martin Rowe, a subscriber to this newsletter, for teaching me how. I happened to read this bit of information while visiting his Web site one day.

In interactive SQL, press the F13 key and select option 1 ("change session defaults"). Page down once and change the sort sequence entry to *LANGIDSHR.

For programs that include embedded SQL, you have two choices. One method is to change the SRTSEQ parameter of the command you use to create the program (for example, CRTSQLRPGI). I think the better solution is to code a SET OPTION command in the source code, as the following RPG snippet illustrates.

C/exec sql                       
C+   set option srtseq=*langidshr
C/end-exec                       

--Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

WHAT IF Creating Queries, Reports and Downloads was Easy, Fast and Convenient for ALL Users? - - - Make it happen with SEQUEL ! - - - Get a Free Trial today. Read More