We are getting the dreaded "A clause of the query contained only
ignored words." error when a user searches for the following term:
"3.0" (without quotes).
I've checked all the noise word lists and "3.0" is not in there. "3" is
however.
Also, when one searches for "3.1" it's fine - no error.
When one searches for "3.00" it's also fine.
How can one explain this and which other "hidden" noise words should we
add to our noise word filter (in the search form)?
remove the 3 and the 0 from your noise word lists. You will need to rebuild
the catalogs!
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Jack" <cawoodm@.gmail.com> wrote in message
news:1140526298.751202.15490@.f14g2000cwb.googlegro ups.com...
> We are getting the dreaded "A clause of the query contained only
> ignored words." error when a user searches for the following term:
> "3.0" (without quotes).
> I've checked all the noise word lists and "3.0" is not in there. "3" is
> however.
> Also, when one searches for "3.1" it's fine - no error.
> When one searches for "3.00" it's also fine.
> How can one explain this and which other "hidden" noise words should we
> add to our noise word filter (in the search form)?
>
|||Aha! It's because of the "." being a word-breaker - is that correct?
Shall I remove it from the noise_eng file? or the enu...
I also noticed that a standalone "#" was being treated as a noise word
and throwing that ugly error. Can you explain that one?
Many thanks
|||Yes . is a word or token boundary (actually there are some rules about
this). # is not a noise word when prefaced by a c, j or f.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Jack" <cawoodm@.gmail.com> wrote in message
news:1140543145.061487.313930@.f14g2000cwb.googlegr oups.com...
> Aha! It's because of the "." being a word-breaker - is that correct?
> Shall I remove it from the noise_eng file? or the enu...
> I also noticed that a standalone "#" was being treated as a noise word
> and throwing that ugly error. Can you explain that one?
> Many thanks
>
|||I actually need to understand where I could see a complete list of
noise words. # is not in the noise word list. Is it a token boundary?
Where is the complete list of these?
Currently we are fighting fires as these errors occur.
|||The noise words are in the noise word lists. Some characters have special
significance - for example C#, and C++.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Jack" <cawoodm@.gmail.com> wrote in message
news:1140593482.893539.110220@.o13g2000cwo.googlegr oups.com...
>I actually need to understand where I could see a complete list of
> noise words. # is not in the noise word list. Is it a token boundary?
> Where is the complete list of these?
> Currently we are fighting fires as these errors occur.
>
Showing posts with label words. Show all posts
Showing posts with label words. Show all posts
Friday, March 23, 2012
Monday, March 12, 2012
Hepl on SQL !!!!!!!
I am trying to get the most recent info on say, billetedid 1, regardless of its availability is 0 of 1. (In other words, I want to get a unique billet for cstunitd = 69)
But I also want to know what its availability.
SQL1 gives me un-unique values but gives me the value of the availability that I need.
SQL2 gives me the uniqueness of values that I want but does not gives me the value of the availability that I need.
How can I get uniqueness and also the corresponding availability values?
SQL1:
SELECT cstunitid, billetid, cast(availability as int) as availability,
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid,cast(availability as int)
order by cstunitid,billetid
RESULTS1:
cstunitid billetid availability datetimestamp
---- ---- ---- --------
69 1 1 2002-11-18 14:04:55.000
69 1 0 2002-11-21 00:17:22.000
69 2 1 2002-11-18 14:04:55.000
69 2 0 2002-11-21 00:17:22.000
69 3 1 2002-11-21 00:17:22.000
69 3 0 2002-11-18 14:04:34.000
69 4 1 2002-11-21 00:17:22.000
69 5 1 2002-11-21 00:17:22.000
SQL2:
SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
order by cstunitid,billeted
RESULTS2:
cstunitid billetid datetimestamp
---- ---- --------
69 1 2002-11-21 00:17:22.000
69 2 2002-11-21 00:17:22.000
69 3 2002-11-21 00:17:22.000
69 4 2002-11-21 00:17:22.000
69 5 2002-11-21 00:17:22.000Surely it should be sufficient just to do a max(availability):
SELECT cstunitid, billetid,
max(availability) as availability
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
order by cstunitid,billeted|||If I use that query, I will always end up with a "1" in cases where there are "1" and "0". I do not want that; I want to be able to pick up the status as "0" if the most recent status is "0".
Thanks for your response.|||OK, I understand. Then you need a 2 stage process:
1. identify the latest record for each cstunitid, billetid
SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
into #t1
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
2. Get the availability for that latest record
SELECT a.cstunitid, a.billetid,a.availability
FROM DM_Bllet_Assignment_Details_view a, #t1 b
where a.cstunitid=a.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by cstunitid,billeted
Note: if there's any chance of two records with the same timestamp you might want to create a new time-ordered key using identity(int,1,1) and use this instead of the datetimestamp.|||Thanks a lot andyabel. Before I try what you have just suggested, I want to ask whether I should create the views or table #t1 first. I using views and the database is SQL Server.|||Hi. If you use "select ... into tablename from ..." SQL Server creates a table for you automatically (it works out what fields and types you need). All you have to do is remember to drop it when you've finished.
However, if you give the table a name beginning with '#' it makes it a temporary table and SQL Server will actually drop the table for you at the end of the batch.|||When I now run
SELECT a.cstunitid, a.billetid,a.availability
FROM DM_Bllet_Assignment_Details_view a, #t1 b
where a.cstunitid=b.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by cstunitid,billetid
I get an error message? Aaaaargh!!!!|||um, you need neither a view nor a temp table, one query will do it
select A.cstunitid
, A.billetid
, cast(A.availability as int) as availability
, A.datetimestamp
from DM_Bllet_Assignment_Details_view as A
inner
join ( select cstunitid
, billetid
, max(datetimestamp) as maxdatetime
from DM_Bllet_Assignment_Details_view
where datetimestamp is not null
and cstunitid = 69
group by cstunitid
, billetid ) as B
on A.cstunitid = B.cstunitid
and A.billetid = B.billetid
and A.datetimestamp = B.maxdatetime
order
by A.cstunitid
, A.billetid
caution: not tested (obviously)
rudy|||Well, my script parses now:
set transaction isolation level read uncommitted
drop table DM_Bllet_Assignment_Details_view
drop table #t1
create table DM_Bllet_Assignment_Details_view
(
cstunitid smallint,
billetid smallint,
availability smallint,
datetimestamp datetime
)
go
SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
into #t1
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
go
SELECT a.cstunitid, a.billetid, availability
FROM DM_Bllet_Assignment_Details_view a inner join #t1 b
on a.cstunitid=b.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by a.cstunitid, a.billetid
go
But I also want to know what its availability.
SQL1 gives me un-unique values but gives me the value of the availability that I need.
SQL2 gives me the uniqueness of values that I want but does not gives me the value of the availability that I need.
How can I get uniqueness and also the corresponding availability values?
SQL1:
SELECT cstunitid, billetid, cast(availability as int) as availability,
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid,cast(availability as int)
order by cstunitid,billetid
RESULTS1:
cstunitid billetid availability datetimestamp
---- ---- ---- --------
69 1 1 2002-11-18 14:04:55.000
69 1 0 2002-11-21 00:17:22.000
69 2 1 2002-11-18 14:04:55.000
69 2 0 2002-11-21 00:17:22.000
69 3 1 2002-11-21 00:17:22.000
69 3 0 2002-11-18 14:04:34.000
69 4 1 2002-11-21 00:17:22.000
69 5 1 2002-11-21 00:17:22.000
SQL2:
SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
order by cstunitid,billeted
RESULTS2:
cstunitid billetid datetimestamp
---- ---- --------
69 1 2002-11-21 00:17:22.000
69 2 2002-11-21 00:17:22.000
69 3 2002-11-21 00:17:22.000
69 4 2002-11-21 00:17:22.000
69 5 2002-11-21 00:17:22.000Surely it should be sufficient just to do a max(availability):
SELECT cstunitid, billetid,
max(availability) as availability
MAX(datetimestamp) as datetimestamp
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
order by cstunitid,billeted|||If I use that query, I will always end up with a "1" in cases where there are "1" and "0". I do not want that; I want to be able to pick up the status as "0" if the most recent status is "0".
Thanks for your response.|||OK, I understand. Then you need a 2 stage process:
1. identify the latest record for each cstunitid, billetid
SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
into #t1
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
2. Get the availability for that latest record
SELECT a.cstunitid, a.billetid,a.availability
FROM DM_Bllet_Assignment_Details_view a, #t1 b
where a.cstunitid=a.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by cstunitid,billeted
Note: if there's any chance of two records with the same timestamp you might want to create a new time-ordered key using identity(int,1,1) and use this instead of the datetimestamp.|||Thanks a lot andyabel. Before I try what you have just suggested, I want to ask whether I should create the views or table #t1 first. I using views and the database is SQL Server.|||Hi. If you use "select ... into tablename from ..." SQL Server creates a table for you automatically (it works out what fields and types you need). All you have to do is remember to drop it when you've finished.
However, if you give the table a name beginning with '#' it makes it a temporary table and SQL Server will actually drop the table for you at the end of the batch.|||When I now run
SELECT a.cstunitid, a.billetid,a.availability
FROM DM_Bllet_Assignment_Details_view a, #t1 b
where a.cstunitid=b.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by cstunitid,billetid
I get an error message? Aaaaargh!!!!|||um, you need neither a view nor a temp table, one query will do it
select A.cstunitid
, A.billetid
, cast(A.availability as int) as availability
, A.datetimestamp
from DM_Bllet_Assignment_Details_view as A
inner
join ( select cstunitid
, billetid
, max(datetimestamp) as maxdatetime
from DM_Bllet_Assignment_Details_view
where datetimestamp is not null
and cstunitid = 69
group by cstunitid
, billetid ) as B
on A.cstunitid = B.cstunitid
and A.billetid = B.billetid
and A.datetimestamp = B.maxdatetime
order
by A.cstunitid
, A.billetid
caution: not tested (obviously)
rudy|||Well, my script parses now:
set transaction isolation level read uncommitted
drop table DM_Bllet_Assignment_Details_view
drop table #t1
create table DM_Bllet_Assignment_Details_view
(
cstunitid smallint,
billetid smallint,
availability smallint,
datetimestamp datetime
)
go
SELECT cstunitid, billetid,
MAX(datetimestamp) as datetimestamp
into #t1
FROM DM_Bllet_Assignment_Details_view
where datetimestamp is not null and cstunitid = 69
GROUP BY cstunitid, billetid
go
SELECT a.cstunitid, a.billetid, availability
FROM DM_Bllet_Assignment_Details_view a inner join #t1 b
on a.cstunitid=b.cstunitid
and a.billetid=b.billetid
and a.datetimestamp=b.datetimestamp
order by a.cstunitid, a.billetid
go
Subscribe to:
Posts (Atom)