This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
I looked. I've mentioned this in a few postings, long ago. I need to address it again, now that I'm actually working on the migration from 6.2 to 7.6.
I absolutely must have the ITEM table be able to search for fractions, and some other special things that normally you wouldn't want to search for. For example, let's say I have these Descriptions for items in my ITEM table:
X-14, 32 oz. bottle
1/4" x 2" stainless steel machine screw
1 gallon all purpose bleach
(Those are oversimplifications, for example's sake.)
We use SQL Server as our database. By default, a bunch of those are stopwords, which SQL Server will not index by default. The x, the 1, the 2, the 1/4, and the word all are stopwords. But I want them indexed. So I copied the system stoplist, dropped those (and other) entries from my custom stoplist, and applied the new stoplist to the ITEM table. But it's still not working. Those words are not indexed, my searches fail. I don't know what I've done wrong. Help?
Travis Herron
Turn logging on and see what sql maximo creates
Sent from my iPhone
> On 12 May 2016, at 17:40, therron@pcci.edu [MAXIMO] <MAXIMO@yahoogroups.com> wrote:
>
>
> I looked. I've mentioned this in a few postings, long ago. I need to address it again, now that I'm actually working on the migration from 6.2 to 7.6.
>
>
> I absolutely must have the ITEM table be able to search for fractions, and some other special things that normally you wouldn't want to search for. For example, let's say I have these Descriptions for items in my ITEM table:
>
>
> X-14, 32 oz. bottle
> 1/4" x 2" stainless steel machine screw
> 1 gallon all purpose bleach
>
>
> (Those are oversimplifications, for example's sake.)
>
>
> We use SQL Server as our database. By default, a bunch of those are stopwords, which SQL Server will not index by default. The x, the 1, the 2, the 1/4, and the word all are stopwords. But I want them indexed. So I copied the system stoplist, dropped those (and other) entries from my custom stoplist, and applied the new stoplist to the ITEM table. But it's still not working. Those words are not indexed, my searches fail. I don't know what I've done wrong. Help?
>
>
> Travis Herron
>
>
>
>
Did you remember to rebuild the fulltext catalog?
You can also remove the stop word list from a table easily.
ALTER FULLTEXT INDEX ON tablename SET STOPLIST OFF --remove stoplist from table
ALTER FULLTEXT CATALOG textcatalogname REBUILD --rebuild fulltext catalog
---In MAXIMO@yahoogroups.com, <therron@...> wrote :
I looked. I've mentioned this in a few postings, long ago. I need to address it again, now that I'm actually working on the migration from 6.2 to 7.6.
I absolutely must have the ITEM table be able to search for fractions, and some other special things that normally you wouldn't want to search for. For example, let's say I have these Descriptions for items in my ITEM table:
X-14, 32 oz. bottle
1/4" x 2" stainless steel machine screw
1 gallon all purpose bleach
(Those are oversimplifications, for example's sake.)
We use SQL Server as our database. By default, a bunch of those are stopwords, which SQL Server will not index by default. The x, the 1, the 2, the 1/4, and the word all are stopwords. But I want them indexed. So I copied the system stoplist, dropped those (and other) entries from my custom stoplist, and applied the new stoplist to the ITEM table. But it's still not working. Those words are not indexed, my searches fail. I don't know what I've done wrong. Help?
Travis Herron
Yes, yes. Of course I did.
So what I found was that, for whatever reason, it is using the "neutral" language on the full-text index. I had edited my stoplist for "English." Now that that is figured out, it is working how I would expect it to. . .kinda.
If I want to search for a fraction, I have to enclose it in double-quotes, e.g. "1/4" -- and that works fine. A bit annoying, but at least it works. Without the quotes, the UI will break that into three words: 1, /, and 4.
But here's the kicker: if I add another search term -- e.g. "1/4" screw -- the search fails. The UI changes the WHERE clause to include spaces before, during, and after the fraction:
(status != 'OBSOLETE' and contains(description , '" 1 / 4 " & FORMSOF(INFLECTIONAL,"screw")') and itemsetid = 'ITEMSET1')
It should just be:
(status != 'OBSOLETE' and contains(description , '"1/4" & FORMSOF(INFLECTIONAL,"screw")') and itemsetid = 'ITEMSET1')
I refuse to accept that users would have to edit the WHERE clause of their search for something that should be really simple.
Travis Herron
Continuing the saga:
I just saw proof that on Oracle, it does the same thing: if you text-search for a fraction, it splits it into three words (the numerator, the denominator, and the slash). If you text-search for a fraction surrounded in double-quotes, it works. If you text search for a fraction surrounded in double-quotes with an additional search term, it adds spaces before, during, and after the fraction, resulting in a failed search.
So a text search for:
1/4 fails
"1/4" succeeds, provided you edit your database's lexicon so that it treats that as a word
"1/4" bolt fails because it searches " 1 / 4 " instead of "1/4"
Happily I did get the assistance of a helpful IBM tech who arranged a web meeting to really understand the problem to take it higher up the chain of command. We'll see what happens. . .
Travis Herron
My text search sort-of works. You also have to remove each individual number from the stoplist.
The numbers are broken into individual words, the slash is ignored, but the quotes enforces the exact order of numbers inside.
Searching for: "1/4" screw (equivalent of searching for "1 4" screw)
Will find:
1/4 inch screw
1 4 screw
screw 1-4
Will not find:
1 screw 4
14 screw
Changing to wildcard search may be fine if the dataset isn't too large.
What about "1/4 bolt"
-C
This is what I get in Oracle 12c for test environment. We have an in-house DBA, so she may have tweaked some stuff for us. Searching fractions work fine for us. Seems like the $ makes it a literal search
Searched for 1/4 in description
[5/18/16 9:44:25:598 PDT] 000000c5 SystemOut O [WARN] [MXServer] [CID-UIASYNC-498110] BMXAA6720W - USER = (MAXADMIN) SPID = (413) app (ITEM) object (ITEM) : select * from item where ((status != 'OBSOLETE' and contains(description,'$1 & $/ & $4'
) > 0 and itemsetid = 'SET2')) and (itemtype in (select value from synonymdomain where domainid='ITEMTYPE' and maxvalue = 'ITEM')) order by itemid desc (execution took 2501 milliseconds)
and this is what I get when I search for 1/4 kit
[5/18/16 9:45:25:217 PDT] 000000c6 SystemOut O [INFO] [MXServer] [CID-UIASYNC-498124] BMXAA6719I - USER = (MAXADMIN) SPID = (19) app (ITEM) object (ITEM) : select * from item where ((status != 'OBSOLETE' and contains(description,'$1 & $/ & $4 &
$kit') > 0 and itemsetid = 'SET2')) and (itemtype in (select value from synonymdomain where domainid='ITEMTYPE' and maxvalue = 'ITEM')) order by itemid desc
Reviving this thread:
The root problem here was that I couldn't get Maximo to search for a fraction on a Full Text Search field. A partial solution was so simple, I can't believe I missed it. Since Maximo is going to break a fraction into its parts (the numerator, the slash, and the denominator), all that really had to be done was add the slash in the database lexicon. By default, a slash is a wordbreaker and therefore is ignored during the search. Adding to the lexicon makes it stop ignoring it.
But I'm not thoroughly pleased with that as a solution. So I'm thinking of putting in an RFE as follows:
For Full Text Searches within Maximo: IBM should change how it interprets the user input. If the user enters a search with double-quotes, the entirety of the string in the quotes should be interpreted as a LIKE search; anything else would continue to be processed as it is today, FORMSOF(INFLECTIONAL).
So if I were to Full Text Search ITEM.DESCRIPTION for "1/4" screw
the proposed SQL would be
where description like '%1/4%' and contains(description , 'FORMSOF(INFLECTIONAL,"screw")')
What would you all think of that change? I think it would make it more Google-like; simple. Would you vote for such an RFE? Can you think of a problem it would create?
Travis Herron
We are using 7.5.0.8 with Oracle. I don't know if our DBA tweaked the Text index, but searching fractions with quotes work fine for me. If I search for 1/8 without quotes it gets searched as 1 and 8. If I search on "1/8", the exact phrase is searched.
---In MAXIMO@yahoogroups.com, <swkim@getty.edu> wrote :
We are using 7.5.0.8 with Oracle. I don't know if our DBA tweaked the Text index, but searching fractions with quotes work fine for me. If I search for 1/8 without quotes it gets searched as 1 and 8. If I search on "1/8", the exact phrase is searched.
[]
Maybe if you ask your DBA (nicely) what was done, you could share that with the group.
-C
I overcomplicated it from the outset. I tried to add every conceivable fraction, both with and without the quote marks to indicate Inches, to the database lexicon. Then I tried to search with quotes around those fractions and got strange results.
It turns out that the ONLY thing that needed to be done was to add each single-digit character and the slash (/) to the database lexicon. And reconstruct the full-text index and restart fdhosts/SQL Server. Surely that's all the tweaking their DBA may have done.
So now it at least functions to be able to search fractions. If I search for
1/4 screw
I'll get results instead of No Rows to Display. But that might be a one-inch screw with a four-inch length. If I search
"1/4" screw
I'll get fewer, more accurate results, with the 1 and the 4 together in the fraction (though I still would also get 1-1/4, 2-1/4, etc.).
But I just searching for this:
"1/4" "3/8" screw
and got the results I expected. But then re-ordering the terms to search for this:
"1/4" screw "3/8"
returned a Database 7630 error.
So it works, mostly, but it has its quirks. My proposed RFE would be for IBM to change the way interprets the user input so that anything in quotes is passed in as a LIKE instead of the CONTAINS it currently uses. That would prevent the 7630 error.
Travis Herron