FTS Newbie - Help Please!

Hi!
I've got a table SupplierItem with full text catalogue set up on it
and I need to search two columns of it, Description2 and
SupplierItemCode, for any combination of terms entered in any order
with wildcards
(ie if user puts in FOLDER, it finds FOLDERS as well) and also search
for two columns non-FTS for
possible entries as well. I've played around with wildcards, CONTAINS
but it still doesn't work properly!!
Please help...
Here's the example:
SELECT
SupplierProduct.SupplierProductId,
SupplierProduct.SupplierProduct,
CurrentItem.SupplierItemId,
CurrentItem.SupplierItemCode,
CurrentItem.Description2,
CurrentItem.BuyUnit,
CurrentItem.PricingUnit,
CurrentItem.OCCost,
CurrentItem.OCCost1,
CurrentItem.OCCost2,
CurrentItem.OCCost3,
CurrentItem.ConnCost,
CurrentItem.ConnCost1,
CurrentItem.ItemStatusId,
ItemStatus.ItemStatusName,
ItemStatus.ItemStatusImage,
ItemStatus.ItemStatusColour,
Supplier.SourceCode,
CurrentItem.UnitId,
CatalogueItem.Item
FROMSupplierItem CurrentItem
INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
SupplierProduct.SupplierProductId)
INNER JOIN Supplier ON (Supplier.SupplierId =
SupplierProduct.SupplierId)
INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
ItemStatus.ItemStatusId)
INNER JOIN BaseProduct ON (BaseProduct.BaseProductId =
CurrentItem.BaseProductId)
LEFT OUTER JOIN CatalogueItem ON (CatalogueItem.BaseProductId =
BaseProduct.BaseProductId)
AND ((CatalogueItem.DateActive <= dbo.Date(year(getdate()), 1,1))
AND (CatalogueItem.DateArchived IS NULL OR CatalogueItem.DateArchived
> dbo.Date(year(getdate()), 12, 31) ))
WHERE(IsApproved = 1 -- item must be approved
AND (CurrentItem.DateArchived <= '1901-01-01') -- item must not be
archived
)
AND ( (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.Description2,
@.SearchTerm))
OR (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.SupplierItemCode,
@.SearchTerm))
OR @.SearchTerm = CatalogueItem.Item)
OR (@.SearchTerm = BaseProduct.BaseItem)
)
AND CurrentItem.OCCost > 0
ORDER BY
CurrentItem.SupplierItemId
What do you mean by it doesn't work? Are you able to find anything? Are
there any error messages?
RelevantNoise.com - dedicated to mining blogs for business intelligence.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"masterslave" <touchdowntokyo@.gmail.com> wrote in message
news:1193969441.786616.323720@.i38g2000prf.googlegr oups.com...
> Hi!
> I've got a table SupplierItem with full text catalogue set up on it
> and I need to search two columns of it, Description2 and
> SupplierItemCode, for any combination of terms entered in any order
> with wildcards
> (ie if user puts in FOLDER, it finds FOLDERS as well) and also search
> for two columns non-FTS for
> possible entries as well. I've played around with wildcards, CONTAINS
> but it still doesn't work properly!!
> Please help...
> Here's the example:
> SELECT
> SupplierProduct.SupplierProductId,
> SupplierProduct.SupplierProduct,
> CurrentItem.SupplierItemId,
> CurrentItem.SupplierItemCode,
> CurrentItem.Description2,
> CurrentItem.BuyUnit,
> CurrentItem.PricingUnit,
> CurrentItem.OCCost,
> CurrentItem.OCCost1,
> CurrentItem.OCCost2,
> CurrentItem.OCCost3,
> CurrentItem.ConnCost,
> CurrentItem.ConnCost1,
> CurrentItem.ItemStatusId,
> ItemStatus.ItemStatusName,
> ItemStatus.ItemStatusImage,
> ItemStatus.ItemStatusColour,
> Supplier.SourceCode,
> CurrentItem.UnitId,
> CatalogueItem.Item
> FROM SupplierItem CurrentItem
> INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
> SupplierProduct.SupplierProductId)
> INNER JOIN Supplier ON (Supplier.SupplierId =
> SupplierProduct.SupplierId)
> INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
> ItemStatus.ItemStatusId)
> INNER JOIN BaseProduct ON (BaseProduct.BaseProductId =
> CurrentItem.BaseProductId)
> LEFT OUTER JOIN CatalogueItem ON (CatalogueItem.BaseProductId =
> BaseProduct.BaseProductId)
> AND ((CatalogueItem.DateActive <= dbo.Date(year(getdate()), 1,1))
> AND (CatalogueItem.DateArchived IS NULL OR CatalogueItem.DateArchived
> WHERE (IsApproved = 1 -- item must be approved
> AND (CurrentItem.DateArchived <= '1901-01-01') -- item must not be
> archived
> )
> AND ( (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.Description2,
> @.SearchTerm))
> OR (@.SearchTerm IS NULL OR FREETEXT(CurrentItem.SupplierItemCode,
> @.SearchTerm))
> OR @.SearchTerm = CatalogueItem.Item)
> OR (@.SearchTerm = BaseProduct.BaseItem)
> )
> AND CurrentItem.OCCost > 0
> ORDER BY
> CurrentItem.SupplierItemId
>
|||Sorry for being really vague... The problem is I'm getting error
messages like this seemingly randomly:
Execution of a full-text operation failed. A clause of the query
contained only ignored words.
I've got a search log but if I try to replicate the search terms I
don't get the error... I've emptied the
words file in SQL Server, but it's still happening!
And the other issue is that it's very slow. The database has about
half a million records, but the query
takes at least five seconds to run...
Any help very much appreciated!!
On Nov 2, 3:05 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> What do you mean by it doesn't work? Are you able to find anything? Are
> there any error messages?
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com"masterslave" <touchdownto...@.gmail.com> wrote in message
> news:1193969441.786616.323720@.i38g2000prf.googlegr oups.com...
>
>
>
> - Show quoted text -