Interesting. Can I assume running that SQL outside Maximo and in some SQL editor gives the same result? If so, perhaps we need to post in a SQL Server forum.
Original Message:
Sent: 05-05-2022 12:39
From: Travis Herron
Subject: SQL Server Full-Text search giving me strange results
It's not generating any ideas. That's the problem!
This is the Item I'm trying to get it to find:
THHN Electrical Wire, 12 AWG, Solid, Green
So in the Description filter I typed:
wire green 12
And it did not find it.
The WHERE clause this generated is:
(status != 'OBSOLETE' and contains(description , 'FORMSOF(INFLECTIONAL,"wire") & FORMSOF(INFLECTIONAL,"green") & FORMSOF(INFLECTIONAL,"12")') and itemsetid = 'ITEMSET1')
which looks fine.
Now if I change the Item's Description to:
THHN Electrical Wire, 12 AWG, Solid, Green, 12<!-- just adding a comma and then the "word" 12 again at the end -->
And then I do the same search again:
wire green 12
This time it finds it!
So that makes me think that it doesn't like the AWG behind it. So I'll change the Item's Description again to:
THHN Electrical Wire, 12 awg, Solid, Green<!-- took the extra comma and 12 off the end, and made AWG lower-case -->
Now if I do the same search yet again (and therefore the same WHERE clause),
wire green 12
it finds it!!
Why does having AWG in caps after the 12 prevent the search from finding it? Is there anything I can do about it? I want AWG there, in caps; and I want the search to succeed in finding it.
------------------------------
Travis Herron
Pensacola Christian College
Original Message:
Sent: 05-03-2022 20:26
From: Steven Hauptman
Subject: SQL Server Full-Text search giving me strange results
This is more a SQL Server question than a Maximo one. I suggest looking at the where clause that is created and see if that generates any ideas.
------------------------------
Steven Hauptman
IBM
Original Message:
Sent: 05-03-2022 17:56
From: Travis Herron
Subject: SQL Server Full-Text search giving me strange results
I should also note:
On the database, ITEM table, Full-Text Index properties, the Full-Text Index Stoplist is set to <Off>. And I have done a Full repopulation.
------------------------------
Travis Herron
Pensacola Christian College
Original Message:
Sent: 05-03-2022 16:15
From: Travis Herron
Subject: SQL Server Full-Text search giving me strange results
SQL Server 2012, Maximo 7.6.0.8
In Item Master (and Inventory), I'm doing a search on the Description field (which is configured for Full-Text Indexing) for:
12 green
and I get results such as these:
Spray Paint, Sherwin-Williams Krylon, #2001, Decorator, Interior/Exterior, Hunter Green, Finish: Gloss, 12 oz, Aerosol CanHandle, Threaded (3/8" Male), Telescoping, Length: 5' - 12', Aluminum, Color: Silver/Green, Tip: PlasticSign, Exit, LED, Aluminum Cast, Black Body, Brushed Face, Lettering Green, 120/277 AC with Battery Back Up, 12-3/4" W x 8-1/2" H
The first example above just has 12 standing alone, a space before and after. The second one has 12 followed by a single quote mark. The third has 12 followed by a hyphen. All good.
My problem is: it DID NOT find this:
THHN Electrical Wire, 12 AWG, Solid, Green
I cannot understand why it is not finding this record. To make it worse, if I change the Description to:
THHN Electrical Wire, 12 AWG, Solid, Green, 12
it finds it! And if I change it to:
THHN Electrical Wire, 12 AWG, Solid, Green, 12 AWG
it DOES NOT find it.
Any ideas what's going on? I'm baffled.
#Administration
#EverythingMaximo
------------------------------
Travis Herron
Pensacola Christian College
------------------------------