UCASE as an index?

[Guest.Visitor]

Tom,

I haven't used this before, but in theory it should work:

Use the TRNTBL() keyword on the appropriate field and specify the QSYS/QSYSTRNTBL as the table to use. It's purpose is to translate lower to upper case.

Bill

I have a field that contains upper and lower case (it may not much longer if this keeps up.) When this data was on the PC they were able to search on smith' and find smith', Smith', SMITH', or any other variation. I can do the same thing in SQL by using UCASE on what the search string as well as the field coming from the PF. But it's slow. I can't find any way to simulate UCASE in a DDS key field and UCASE either can't be used with SQL create index or I'm not getting the syntax down. Can anyone help?

[J.Pluta]

Look at the SRTSEQ parameter in the CRTLF command, specifically SRTSEQ(*LANGIDSHR). This little-known keyword will do exactly what you want.

[Guest.Visitor]

Thanks guys. I tried both techniques and they both seem to give identical results. Joe - You must have done some serious digging to find that one. I had a hard time finding a decent description - http://publib.boulder.ibm.com/cgi-bi...6.2#TBLENGSORT - even after I knew what I was looking for! Tom McQuinn

[Guest.Visitor]

I have a field that contains upper and lower case (it may not much longer if this keeps up.) When this data was on the PC they were able to search on smith' and find smith', Smith', SMITH', or any other variation. I can do the same thing in SQL by using UCASE on what the search string as well as the field coming from the PF. But it's slow. I can't find any way to simulate UCASE in a DDS key field and UCASE either can't be used with SQL create index or I'm not getting the syntax down. Can anyone help? FWIW, if there's an easier way than using a derived field as an index or forcing everything to upper case at entry time than I prefer that shortcut. Tom McQuinn

[Guest.Visitor]

In SQL the function upper(column) converts characters ti UCASE

[J.Pluta]

The nice thing about the *LANGIDSHR technique is that it works with European languages, sorting character with umlauts and tildes into their appropriate canonical positions. Joe