full text indexing help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

full text indexing help

Author

Topic

harpreetkaur
Starting Member

14 Posts

Posted - 2007-02-06 : 12:19:59

I added full text indexing on the title field in a table but when i reference
the same field in an embedded sql it says cannot use contain on a field that
is not full text indexed.

example
SELECT top 400 s.story_id, u.title, s.title AS story_name, u.state,
CONVERT(char(10), u.air_date, 101) AS rundown_date,
'' AS video, '' AS cg_text, SUBSTRING(s.text, 1,500) AS script, SUBSTRING(i.
text, 1, 500) AS item_text, i.type, i.content_status, k.keyword, i.
editorial_description AS description, d.description AS notes,
s.editor AS creator, i.original_material_id AS clipname, i.ar_material_id AS
material_id
FROM
(
SELECT NULL AS state, NULL AS type, p.rundown_id, p.ncs_rundown_id, p.
edit_duration, p.title,
CONVERT(char(10), p.air_date, 101) AS air_date, SUBSTRING(CONVERT(varchar(10),
p.edit_start_time, 114), 1, 8) AS edit_start_time
FROM dbo.na_rundown_tbl p
WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type = 401)))
) AS u
INNER JOIN dbo.na_story_tbl AS s ON s.rundown_id = u.rundown_id
LEFT OUTER JOIN dbo.na_item_tbl AS i ON s.story_id = i.story_id
LEFT OUTER JOIN dbo.na_itemkeyword_tbl AS k ON i.item_id = k.item_id
LEFT OUTER JOIN dbo.na_itemdesc_tbl AS d ON i.item_id = d.item_id where
contains (u.title ,'%midlothian%')

error message received:
Msg 7601, Level 16, State 3, Line 1
Cannot use a CONTAINS or FREETEXT predicate on column 'title' because it is
not full-text indexed.

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-06 : 15:33:23

what is (as U), (may be I am not as strong as I thought I was)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-06 : 15:34:33

quote:
Originally posted by pareshmotiwala

what is (as U), (may be I am not as strong as I thought I was)

It's the derived table alias. You must alias a derived table, otherwise it will error.

Tara Kizer

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-02-06 : 15:44:14

so he is running a full-text search on a derived table?
Would the full-text from p.title automatically propagate into u?
if not could the where contains(u.title,adfadfadf) be shifted before the AS U and in its bracket and referred as p.title

WHERE (rundown_id NOT IN (SELECT ref1 FROM req_state_tbl WHERE (type = 401)))
and contains (p.title ,'%midlothian%')
) AS u

harpreetkaur
Starting Member

14 Posts

Posted - 2007-02-06 : 21:24:25

I cannot use the contains in the sub select as i have other fields also that i serach for. My original query looks like this:

select top 400 * from na_item_view_new where ((story_name like '%midlothian%' ) or (title like '%midlothian%' ) or (script like '%midlothian%' ) or (item_text like '%midlothian%' ) or (keyword like '%midlothian%' ) or (notes like '%midlothian%' ) or (description like '%midlothian%' ) or (clipname like '%midlothian%' ) or (creator like '%midlothian%' )) and Convert(datetime,rundown_date) >= '11/06/2006' and Convert(datetime,rundown_date) <= '02/06/2007' and type=1 and content_status=1 order by convert(datetime, rundown_date) asc

This query takes like over a minute to run so I am trying to add full text indexing. to all the text fields to make it run faster.
If i add the contains for the title to the sub query then that would mean an AND as opposed to an OR. Any other suggestions are welcome.

Can i have a full text index on a view. Please help I am badly stuck. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources