Case Insensitive SELECT on IBM i

[jyoung]

How do I make a case insensitive select on the IBM i?

I have the following query:

Code: Select all

select fields(#XF1102List) from_file(XF110201) with_key(#OFOTY #OFOFIN) nbr_keys(*COMPUTE) generic(*YES)

I need to make the OFOFIN filter case insensitive as the data contains mixed case.

[jyoung]

I finally figured out how to do this with the SELECT_SQL.

Code: Select all

#STD_TEXT := #OFOFIN.UpperCase.Concat( "%" )
select_sql fields(#OFOFID #OFOFIN #OFELVL #OFOTY #OFDFC) from_files((XF110201)) where('UPPER(#OFOFIN) LIKE :STD_TEXT AND #OFOTY = :OFOTY')

It works, but its not ideal as you have to specify ALL the fields you want back. It would be nice if you could specify a list or group_by like you can do with the normal select.

[LANSAGuru]

Your 2 querys are not exactly the same. Generic does something different than like. Like searchs the entire field for a match, the generic starts at the beginning of the fied.

For a LANSA controlled file.
To do this you need an index.
The index needs to be built over a real field, so typically you store the value as all upper and have an index built over this.
If you do not do this, the db engine will need to build an index every time you access the file this way (your query).
You can have a virtual for the uppercase version of a field, but you can not build an index with a virtual, so you are stuck with a real field or a SQL Query where it will build the index on the fly.

If this is an OTHER file you could try building a case insensitive view over the file and then loading this version.

http://www.ibm.com/developerworks/data/ ... mache.html

http://use-the-index-luke.com/sql/where ... ive-search

[jyoung]

I am not understanding how generic and the like are not the same.

In the original query OFOFIN is essentially the name, the File XF110201 is a logical keyed by a type code and the name.

If I am looking for all US cities that start with "ALB" and have a logical defined with the key "NAME" then with

Code: Select all

SELECT WITH_KEY(1 'Alb') GENERIC(*YES)

How is this different then

Code: Select all

CODE = 1 AND NAME LIKE 'Alb%' 

I get that a "LIKE" will do a full table scan, but since the NAME is part of the logical key, I thought it would be the same as the SELECT WITH_KEY.

In this case the logical is in mixed case, and I cannot create one. The only thing I could think to do then was to use SELECT_SQL and force the case. However I was having difficultly getting the LIKE wildcard in place, hence the concatenation to STD_TEXT. I should also note that #OFOFIN is "alb" so STD_TEXT becomes "ALB%".

I can't get access to second link you provided, but I will look it up tonight after work.

[LANSAGuru]

like '%ABC%'

will find

ABCXYZ
123ABC
ttABCtt

generic ABC

will find

ABCXYZ
not the other 2

In your specific case (above) they are in fact identical.