Help with SQL Contains clause

why does this
Select * from cv_Volunteer_Section where
CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR "accounting
clerk" ')
return this error?
Server: Msg 7619, Level 16, State 1, Line 2
The query contained only ignored words.
Is there away to prevent the error by editing the clause?
Thanks in advance
SanjaySanjay,
What is the version of SQL Server and on what OS platform do you have it
installed? Could you post the full output of -- SELECT @.@.version -- as this
is most helpful info in troubleshooting SQL FTS issues.
The OS platform version is key to understanding this results of your
CONTAINS query as Win2K vs. WinXP or Win2003 ship with different OS-supplied
wordbreakers. see http://groups.google.com/groups?q=langwrbk+infosoft for
more details. Also, the language-specific noise word files, under
\FTDATA\SQLServer\Config\ (noise.enu = US_English) can have an affect on the
query as well. Specifically, are the single letters A, P and R in the
noise.enu file (assuming you're using US English as the default language)?
If so, you may want to remove the single letters from this file and then run
a Full Population and re-test your query.
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv#AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
"accounting
> clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Courtesy of John Kane:
http://sqljunkies.com/WebLog/jt_kan.../09/19/217.aspx
-oj
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
> "accounting clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Speaking of the de-vil. ;-)
-oj
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OktcjHEBFHA.4004@.tk2msftngp13.phx.gbl...
> Sanjay,
> What is the version of SQL Server and on what OS platform do you have it
> installed? Could you post the full output of -- SELECT @.@.version -- as
> this
> is most helpful info in troubleshooting SQL FTS issues.
> The OS platform version is key to understanding this results of your
> CONTAINS query as Win2K vs. WinXP or Win2003 ship with different
> OS-supplied
> wordbreakers. see http://groups.google.com/groups?q=langwrbk+infosoft for
> more details. Also, the language-specific noise word files, under
> \FTDATA\SQLServer\Config\ (noise.enu = US_English) can have an affect on
> the
> query as well. Specifically, are the single letters A, P and R in the
> noise.enu file (assuming you're using US English as the default language)?
> If so, you may want to remove the single letters from this file and then
> run
> a Full Population and re-test your query.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OcZnEv#AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> "accounting
>|||Thank you, OJ!
It's good to be noticed! I'm now actively blogging on "SQL Full Text Search
Blog" at http://spaces.msn.com/members/jtkane/ and I've cross-posted the
below entry to "SQL Server 2000 FTS on Windows 2000 vs. Windows Server
2003..." at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!158.e
ntry
Enjoy.
John
"oj" <nospam_ojngo@.home.com> wrote in message
news:#mWGuIEBFHA.2876@.TK2MSFTNGP12.phx.gbl...
> Courtesy of John Kane:
> http://sqljunkies.com/WebLog/jt_kan.../09/19/217.aspx
>
> --
> -oj
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
>|||Kewl. I'll check it out.
Cheers,
--
-oj
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OpJX6TEBFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Thank you, OJ!
> It's good to be noticed! I'm now actively blogging on "SQL Full Text
> Search
> Blog" at http://spaces.msn.com/members/jtkane/ and I've cross-posted the
> below entry to "SQL Server 2000 FTS on Windows 2000 vs. Windows Server
> 2003..." at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!158
.entry
> Enjoy.
> John
>|||Sql Server 7
What was interesting was that it seems like the / seems to be impacting the
searchresults however,
this worked like a charm. (notice i changed a/r or a/p to a/ra/p)
Select * from cv_Volunteer_Section where
CONTAINS(listingdescription, ' "a/pa/r" OR "bookkeeper" OR "accounting
clerk" ')
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
> "accounting clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86)
May 29 2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Thanks John/oj
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
> why does this
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR
> "accounting clerk" ')
> return this error?
> Server: Msg 7619, Level 16, State 1, Line 2
> The query contained only ignored words.
> Is there away to prevent the error by editing the clause?
> Thanks in advance
> Sanjay
>|||Sanjay,
Yes, the "/" or forward slash does impact the search results, but it is
highly dependent upon what OS platform you are using with SQL Server 7.0,
hence the request for the SELECT @.@.version output. What you have done is to
use double quotes around your single letter queries and turn these search
words into a "phrase word - pa" query and under this condition the single
letter noise words are truly ignored, but the "pa" is considered a "word".
Hope that helps!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:eRIXv8HBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> Sql Server 7
> What was interesting was that it seems like the / seems to be impacting
the
> searchresults however,
> this worked like a charm. (notice i changed a/r or a/p to a/ra/p)
> Select * from cv_Volunteer_Section where
> CONTAINS(listingdescription, ' "a/pa/r" OR "bookkeeper" OR "accounting
> clerk" ')
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OcZnEv%23AFHA.2316@.TK2MSFTNGP15.phx.gbl...
>

help with simple query using group by and where clause

have a table with sale_id, date, sales_person_id

i need to find out the sales_person_id's who did 1 sales every month
from jan 2003 and another query who did a sales every quarter.

How many sales person have atleast one sale every month (excluding prints) for either 2003, 2004, or 2005?

How many sales person had atleast 25 sales each year 2003-2005

It is not clear how you have the date specified in the table. And also what does excluding prints mean? Is there some other column that tracks this detail? If so, you need to include the condition in the query below:

SELECT COUNT(*) as NumSalesPerson

FROM (

SELECT year(s.date) as yr, s.sales_person_id

FROM Sales AS s

WHERE s.date >= '20030101' and s.date <'20060101'

GROUP BY year(s.date)

HAVING COUNT(*) >= @.n -- can be 1 or 25 or whatever

) AS s1

Help with outer join query please!

I have a MSDE query that includes a "left outer join..." clause. It runs fine in MSDE Query (a 3rd party GUI tool) and produces 12 rows. column 3 has some NULL values (because of the outer join).

But when I use the same query in an ASP.NET page, and display the result in a datagrid, it only displays 7 rows - the rows with the NULL value in column 3 do not display.

Is there a parameter somewhere in datagrid or dataset that I should be setting?

thank you someone!there should be an AllowDbNull afaik|||

private void AddNullAllowedColumn(){
 DataColumn myColumn;
 myColumn = new DataColumn("classID", System.Type.GetType("System.Int32"));
 myColumn.AllowDBNull = true;
 // Add the column to a new DataTable.
 DataTable myTable;
 myTable = new DataTable();
 myTable.Columns.Add(myColumn);
 }

maybe a foreach (DataColumn column in DataGrid.Columns) { or datatable.
you can specify that.

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~Steven
I guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&[2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&[2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>

Help with MDX selecting multiple attributes in Where clause

Given the following:
Dimension Name: Issues
Measure: Issue Count
Dimension Attributes: Priority (Values 1,2,3,4)
Severity (Values 1,2,3,4)
How can I format an MDX statement to select Issues Where (Priority = 1 or
Priority = 2) and (Severity = 1 or Severity = 2)?
When I try the following:
select [Measures].[Issue Count] on columns,
([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/20
05]) on rows
From [Issues DB]
where
{
([Issues].[Priority].&[1]),([Issues].[Priority].&[2]
),
([Issues].[Severity].&[1]), [Issues].[Severity].&[2]
}
I get the error referenced below:
"Members belong to different hierarchies in the function."
Any pointers would be greatly appreciated!
~StevenI guess you treat Priority and Severity as same level and put them between
{}, which is for a set.
Try this:
(
{[Issues].[Priority].&[1], [Issues].[Priority].&
1;2]},
{[Issues].[Severity].&[1], [Issues].[Severity].&
1;2]}
)
In your case, I would say put these two properties in 2 hierarchies. which
may improve the performance.
Guangming
"Steven" wrote:

> Given the following:
>
> Dimension Name: Issues
> Measure: Issue Count
> Dimension Attributes: Priority (Values 1,2,3,4)
> Severity (Values 1,2,3,4)
>
> How can I format an MDX statement to select Issues Where (Priority = 1 or
> Priority = 2) and (Severity = 1 or Severity = 2)?
>
> When I try the following:
>
> select [Measures].[Issue Count] on columns,
> ([Time].[Date].&[9/18/2005]:[Time].[Date].&[10/17/
2005]) on rows
> From [Issues DB]
> where
> {
> ([Issues].[Priority].&[1]),([Issues].[Priority].&[
2]),
> ([Issues].[Severity].&[1]), [Issues].[Severity].&[
2]
> }
>
> I get the error referenced below:
>
> "Members belong to different hierarchies in the function."
>
> Any pointers would be greatly appreciated!
>
> ~Steven
>
>