Using CONTAINS

[apollo11]

I am trying to use the keyword CONTAINS in a query and I need to search for two seperate words in two different columns.
Can I do that?
The query below does not seem to work. I know its difficult to test this without data but can anybody make out anything wrong in the syntax off-hand. (I know it is wrong somewhere)

SELECT cpi.*, s.name as SupplierName, cpd.ShortDesc, Mfr.Name as MfrName, cp.UnitPrice as DisplayUnitPrice
FROM Cat_PartDesc cpd, Cat_PartInfo cpi, Cat_ProductCatalog cpc,
supplier s, Mfr , Cat_Price cp
WHERE ((CONTAINS(cpd.LongDesc, 'FORMSOF (INFLECTIONAL, copper) and FORMSOF (INFLECTIONAL, wire)))) OR CONTAINS(cpd.ItemDetail, 'FORMSOF (INFLECTIONAL, copper) and FORMSOF (INFLECTIONAL, wire))') and cpd.PartId = cpi.PartId and cpi.ProductCatalogID = cpc.ProductCatalogID and Mfr.MfrId = cpi.MfrId and cp.PartId = cpd.PartId and cp.isCurrent = 1 and cp.Status = 0 and cp.IsSearchable = 1 and cpi.IsObsolete = 0 and s.supplierid = cpc.supplierid and s.name = 'WESCO Distribution, Inc.'

Thanks

 

[SQLSister]

Just a guess, it looks to me like you need to look at your placement of single quotes. Check SQL Books on line for exactly where they need to be. They have a good clear example of the FormsOf construction.

 

[apollo11]

Thanks I figured out what was wrong with the SQL. The problem was I was building up the SQL dynamically reading from an array and the CONTAINS keyword warrants the use of quotes in a special manner.

The correct SQL is as follows:
SELECT cpi.*, s.name as SupplierName, cpd.ShortDesc, Mfr.Name as MfrName, cp.UnitPrice as DisplayUnitPrice
FROM Cat_PartDesc cpd, Cat_PartInfo cpi, Cat_ProductCatalog cpc,
supplier s, Mfr , Cat_Price cp
WHERE (CONTAINS(cpd.LongDesc, 'FORMSOF (INFLECTIONAL, copper) and FORMSOF (INFLECTIONAL, wire)') OR CONTAINS(cpd.ItemDetail, 'FORMSOF (INFLECTIONAL, copper) and FORMSOF (INFLECTIONAL, wire)'))
and cpd.PartId = cpi.PartId and cpi.ProductCatalogID = cpc.ProductCatalogID and Mfr.MfrId = cpi.MfrId and cp.PartId = cpd.PartId and cp.isCurrent = 1 and cp.Status = 0 and cp.IsSearchable = 1 and cpi.IsObsolete = 0 and s.supplierid = cpc.supplierid and s.name = 'WESCO Distribution, Inc.'