Hi,
There are two different bits at play here. Let's start with a mini view of the architecture. The list selection tied to the UI lookup that is bound to the custom work order field that eventually gets to the framework is pretty loose. By the time it gets to that framework layer, it has no idea that the value came from a list and was not typed in. Furthermore, it also doesn't know if the data is tightly tied to a validation against that list vs just some known values (if you look at the work order reported by field you would see you can get a list of all People, but can type in any value even if it is not in the list). I am sure we can "teach" it to be smarter, but alas, today it is not. The second bit is related, but has to do with the attribute type. As it is defined as ALN, that would make it appear that "fire", "Fire" and "FIRE" are all valid (as would be "FiRe", etc). Most of us would expect that no matter how I type in "fire", it would find all the versions. Therefore, the system "Uppers" both sides to find all matches (the "= "would denote I don't want "fire hydrant", "fire engine", "forest fire", etc, only "fire". Now, notice that if the field was defined as UPPER on the meta data then the system is smart enough to just compare the attribute = "FIRE" without any database conversion. My suggestion - re-configure that field to be of type UPPER (assuming only the values in that list are valid).
Steve
------------------------------
Steve Hauptman
------------------------------
Original Message:
Sent: Mon July 19, 2021 02:49 PM
From: User1971
Subject: Index ignored due to UPPER( ) function?
MAM 7.6.1.2; Oracle 19c:
My organization has a custom/persistent/wildcard ALN field in the WORKORDER table:
I noticed when I filter the WOTRACK list view via the lookup window, Maximo coverts the db field to uppercase via the UPPER() function.
It's my understanding that databases can't utilize indexes when functions are used on fields (I'm not considering function-based indexes here).
And since I'm using the lookup window, Maximo ought to be able to query for exact values right from the domain.
If that's the case, why does Maximo wrap the field in the upper function when the lookup window is used -- forcing the db to ignore the index?
Why can't it just query for cgdivision = 'FIRE' ?
Thanks.
#AssetandFacilitiesManagement
#Maximo