Page 1 of 1
Case Insensitive SELECT on IBM i
Posted: Wed Nov 30, 2016 1:49 am
by 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.
Re: Case Insensitive SELECT on IBM i
Posted: Wed Nov 30, 2016 2:35 am
by 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.
Re: Case Insensitive SELECT on IBM i
Posted: Wed Nov 30, 2016 2:55 am
by 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
Re: Case Insensitive SELECT on IBM i
Posted: Wed Nov 30, 2016 4:01 am
by 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
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.
Re: Case Insensitive SELECT on IBM i
Posted: Wed Nov 30, 2016 5:40 am
by 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.