More on Full Text Searching across multiple tables

Imagine you're building a search for a movie database which you wanted to index actors, quotes from scenes and movie names into a single search.... how do you accomplish this using full text search? The closest I can think of is... (for a sample search for "Walken"

select RANK, actorId as Id from FREETEXTTABLE( actors, *, 'ISABOUT (+ WALKEN + WEIGHT(1.0))') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from FREETEXTTABLE( scenes, *, 'ISABOUT (+ WALKEN + WEIGHT(1.0))') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from FREETEXTTABLE( movie, *, 'ISABOUT (+ WALKEN + WEIGHT(1.0))') a JOIN movie b on a.[key] = b.movieId

But it doesn't rank correctly. Suggestions?

How do you want the ranking to work?

What do you want to return? have you tried

select RANK, actorId as Id from CONTAINSTABLE( actors, *, 'WALKEN') a JOIN actors b on a.[key] = b.actorid
UNION ALL
select RANK, sceneId as Id from CONTAINSTABLE( scenes, *, 'WALKEN') a JOIN scenes b on a.[key] = b.sceneId
UNION ALL
select RANK, movieId as Id from CONTAINSTABLE( movie, *, 'WALKEN') a JOIN movie b on a.[key] = b.movieId

The other thing to note is that RANK is not a fixed number it is relative to a number of factors, largely it is related to the batch of data a record was indexed with. So if you have records being indexed in differing batchs you can end up with odd ranking.

Bottom linke is a rank from tableA can't be used to compare with a rank from tableB

|||Ideally, i'd like to return the item(s), in order that match the query string. This means if the scene is a closer match than the movie, the scene is returned first. Each one should have the id returned which i can then construct a result from.|||

Unfortunately you can't use the rank from one index to compare with a rank from another index.

Have a lookin BOL under the heading "Understanding Ranking "

|||Right, what i'm looking for is a solution that will work across multiple tables... any suggestions?

More help with Contains

I had posted an earlier question about the CONTAINS predicate that uses the
full text search.
The entire process of comparing a column with a keyword variable using the
contains predicate was not only taking too long (cursors) but also breaking
the stored procedure when noise words were encountered.
I have come up with an alternative that involves copying all the data I want
to compare into a table say Table A. This table will contain three columns
ColA - The description Column
ColB - The column containing the keywords
ColC - A Bit/Flag column.
Can I do this?
Update TableA
SET ColC = 1
WHERE CONTAINS(COLA, COLB)
Can I use two columns in the contains predicate
I know I need to refresh the full text search index after I fill Table A
with data and this user needs DBO permission.
Can a user - datareader/datawriter be assigned privileges to update the full
text.
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.
Yes, you can use CONTAINS an UPDATE statement's where clause. If fact, I've
been planning on writing an article entitled "Putting Full Text Search to
work" that would demonstrate how to use FTS queries with INSERT, DELETE and
UPDATE to do real database table data manipulation, and the article is still
on the drawing boards & perhaps will be posted to my blog soon...
Q. Can I use two columns in the contains predicate?
A. No. However, you can use multiple CONTAINS clauses, i.e., CONTAINS(COLA,
'search1') AND/OR CONTAINS(COLA, 'search2') in SQL Server 2000. Furthermore,
in SQL Server 2005 (Yukon) will support multiple columns in the CONTAINS
syntax.
Q. Can a user - datareader/datawriter be assigned privileges to update the
full text?
A. No, as symin privileges are required to update the Full Text
Index/Catalog via the system stored procs: sp_fulltext*. See SQL Server 2000
BOL title "sp_fulltext_catalog" - "Only members of the symin fixed server
role and the db_owner (or higher) fixed database roles can execute
sp_fulltext_catalog."
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp#AFHA.2112@.TK2MSFTNGP14.phx.gbl...
> I had posted an earlier question about the CONTAINS predicate that uses
the
> full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
breaking
> the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
want
> to compare into a table say Table A. This table will contain three columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
full
> text.
> 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
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sql Server 7
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||I setup full text searches on a table to test my solution and it DID NOT
WORK!!!
So it seems like you can't do this
Select * from TableA WHERE CONTAINS(COLA, COLB)
Where COLA & COLB are both columns in TableA
COLA has a full text search index defined on it.
Thanks for the replies though.
The details of the SQL Server are...
****************************************
***********
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)
****************************************
***********
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
>I had posted an earlier question about the CONTAINS predicate that uses the
>full text search.
> The entire process of comparing a column with a keyword variable using the
> contains predicate was not only taking too long (cursors) but also
> breaking the stored procedure when noise words were encountered.
> I have come up with an alternative that involves copying all the data I
> want to compare into a table say Table A. This table will contain three
> columns
> ColA - The description Column
> ColB - The column containing the keywords
> ColC - A Bit/Flag column.
> Can I do this?
> Update TableA
> SET ColC = 1
> WHERE CONTAINS(COLA, COLB)
> Can I use two columns in the contains predicate
> I know I need to refresh the full text search index after I fill Table A
> with data and this user needs DBO permission.
> Can a user - datareader/datawriter be assigned privileges to update the
> full text.
> Thanks in advance
> Sanjay
>|||Sanjay,
I never said that you could use multiple columns in ONE contains statement
with SQL Server 2000.
What I said is that you could use MULTIPLE contains clauses in your WHERE
clause with SQL Server 2000, specifically:
CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
'search_word_here').
More than one column per contains statement is ONLY supported in SQL Server
2005 that is still in limited beta release.
Hope that helps,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> I setup full text searches on a table to test my solution and it DID NOT
> WORK!!!
> So it seems like you can't do this
> Select * from TableA WHERE CONTAINS(COLA, COLB)
> Where COLA & COLB are both columns in TableA
> COLA has a full text search index defined on it.
> Thanks for the replies though.
> The details of the SQL Server are...
> ****************************************
***********
> 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)
> ****************************************
***********
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:u2Rtzp%23AFHA.2112@.TK2MSFTNGP14.phx.gbl...
the
the
>|||I'm sorry if I gave you that impression. My original question was just that
and I finally got down to setting up the environment to see if I could
compare two columns and subsequently discovered I could not. I however have
another bainwave that involves the use of a UDF that takes the keyword as a
parameter and looks up columnA using a uniqueid to compare the value of the
row.
So esentially I plan to do this
Select IdentityInd, ColA, ColB
From TableA Where
UDFContains(IdentityInd, ColB ) = 1
And UDFContains will looklike
UDFContains(@.IdentityInd, @.ColB )
Returns Bit
Begin
IF EXISTS(Select IdentityInd From TableA Where
IdentityInd = @.IdentityInd AND CONTAINS(ColA,
@.ColB)) BEGIN
Return 1
End
ELSE BEGIN
Retuen 0
End
End
I am not sure if this will work but I plan to try and see for myself.
Thanks for the reply though
Sanjay
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
> Sanjay,
> I never said that you could use multiple columns in ONE contains statement
> with SQL Server 2000.
> What I said is that you could use MULTIPLE contains clauses in your WHERE
> clause with SQL Server 2000, specifically:
> CONTAINS(COLA, 'search_word_here') AND CONTAINS(COLA, 'search_word_here')
> Note, that this is not CONTAINS(COLA, COLB), but CONTAINS(COLA,
> 'search_word_here').
> More than one column per contains statement is ONLY supported in SQL
> Server
> 2005 that is still in limited beta release.
> Hope that helps,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e6gfE$IBFHA.2180@.TK2MSFTNGP12.phx.gbl...
> the
> the
>|||Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
Do I have any other alternatives?
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
> I'm sorry if I gave you that impression. My original question was just
> that and I finally got down to setting up the environment to see if I
> could compare two columns and subsequently discovered I could not. I
> however have another bainwave that involves the use of a UDF that takes
> the keyword as a parameter and looks up columnA using a uniqueid to
> compare the value of the row.
> So esentially I plan to do this
> Select IdentityInd, ColA, ColB
> From TableA Where
> UDFContains(IdentityInd, ColB ) = 1
> And UDFContains will looklike
> UDFContains(@.IdentityInd, @.ColB )
> Returns Bit
> Begin
> IF EXISTS(Select IdentityInd From TableA Where
> IdentityInd = @.IdentityInd AND CONTAINS(ColA,
> @.ColB)) BEGIN
> Return 1
> End
> ELSE BEGIN
> Retuen 0
> End
> End
>
> I am not sure if this will work but I plan to try and see for myself.
> Thanks for the reply though
> Sanjay
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%234ChvdOBFHA.2196@.TK2MSFTNGP14.phx.gbl...
>|||You're welcome, Sanjay,
As they say in the movies..., lets try again (or something like that ;-). If
not UDF, how about a cursor operation? I'm not sure if this will meet your
needs, but you may be able to fit it to your requirements.. The table
jtkane_Tables contains a list of keywords that is the source of the @.keyword
variable for FT Searching of the jtkane_SysComments table.
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select TableNane from jtkane_Tables order by TableNane
CREATE TABLE #table_name (tblname varchar(100))
OPEN keyword_cursor
DECLARE @.keyword varchar(100)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select distinct SysNames from jtkane_SysComments
where contains(*, @.keyword)
INSERT INTO #table_name (tblname) VALUES (@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
select * from #table_name order by tblname
drop table #table_name
If the above doesn't fit your needs, checkout the use of
"sp_help_fulltext_catalogs_cursor" in the code example at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308.e
ntry
Enjoy!
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> Oh crap!!! SQL Server 7 does not allow UDF's!!!!!
> Do I have any other alternatives?
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:e4pn1qUBFHA.1076@.TK2MSFTNGP10.phx.gbl...
WHERE
'search_word_here')
NOT
uses
using
data
Table
>|||The problem with cursors is that they take just too long are inefficient and
create all sorts of problems with locks( though I am yet to experience
problems with locking)
We currently have an inefficient procedure that uses cursors that I am
trying to replace hence the discourse :)
Thanks once again for the reply though!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%231gBsdbBFHA.3592@.TK2MSFTNGP09.phx.gbl...
> You're welcome, Sanjay,
> As they say in the movies..., lets try again (or something like that ;-).
> If
> not UDF, how about a cursor operation? I'm not sure if this will meet your
> needs, but you may be able to fit it to your requirements.. The table
> jtkane_Tables contains a list of keywords that is the source of the
> @.keyword
> variable for FT Searching of the jtkane_SysComments table.
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select TableNane from jtkane_Tables order by TableNane
> CREATE TABLE #table_name (tblname varchar(100))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(100)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select distinct SysNames from jtkane_SysComments
> where contains(*, @.keyword)
> INSERT INTO #table_name (tblname) VALUES (@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> select * from #table_name order by tblname
> drop table #table_name
> If the above doesn't fit your needs, checkout the use of
> "sp_help_fulltext_catalogs_cursor" in the code example at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!308
.entry
> Enjoy!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Sanjay Pais" <spaisatnospammarketlinksolutions.com> wrote in message
> news:OwNLZuUBFHA.1992@.TK2MSFTNGP10.phx.gbl...
> WHERE
> 'search_word_here')
> NOT
> uses
> using
> data
> Table
>

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQL
Use the DATENAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL
|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL
|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter

>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...[vbcol=seagreen]
be
>
>.
>
|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
David Portas
SQL Server MVP
|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J

>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>be
>.
>
|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:[vbcol=seagreen]
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>
> be

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQLUse the DATENAME() function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101
280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter

>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
be[vbcol=seagreen]
>
>.
>|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
David Portas
SQL Server MVP
--|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J

>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>be
>.
>|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:[vbcol=seagreen]
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>
> be
>

Month name

how do i return the month in text from a number?
the vb function is monthname(), there does not seem to be
an equivelent in TSQLUse the DATENAME() function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"mat" <anonymous@.discussions.microsoft.com> wrote in message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||Hi,
select datename(month,getdate())
Thanks
Hari
MCDBA
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
> how do i return the month in text from a number?
> the vb function is monthname(), there does not seem to be
> an equivelent in TSQL|||what i am trying to do is convert a number eg 5, into its
month value eg 'september' all date functions such as
datename() & month() seem to ask for a date as there input
parameter
>--Original Message--
>Use the DATENAME() function.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"mat" <anonymous@.discussions.microsoft.com> wrote in
message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
be
>> an equivelent in TSQL
>
>.
>|||The equivalent of VB MonthName() would be something like this:
SELECT DATENAME(MONTH,DATEADD(MONTH,@.month,-1))
where @.month is the month number.
More generally, DATENAME is used to return the month name from a DATETIME or
SMALLDATETIME value.
--
David Portas
SQL Server MVP
--|||Declare @.Month as char(2)
Declare @.Date as char(10)
set @.Month = '06'
set @.Date = '01/' + @.Month + '/1900'
SELECT DATENAME(month, @.Date) AS 'Month Name'
You may need to modify the @.Date string depending where in
the world you are.
J
>--Original Message--
>what i am trying to do is convert a number eg 5, into its
>month value eg 'september' all date functions such as
>datename() & month() seem to ask for a date as there
input
>parameter
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
>message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>> how do i return the month in text from a number?
>> the vb function is monthname(), there does not seem to
>be
>> an equivelent in TSQL
>>
>>.
>.
>|||> You may need to modify the @.Date string depending where in
> the world you are.
Not if you use a standard, non-ambiguous format!
SET @.Date = '1900' + @.Month + '01'
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
you can use the DATENAME(), but then you have to 'create' a date first:
DECLARE @.MonthNumber integer;
SET @.MonthNumber=11;
select datename(month,
CAST('1/'+CASE WHEN @.MonthNumber <10 THEN CAST(@.MonthNumber AS char(1))
ELSE CAST(@.MonthNumber AS char(2)) END +'/2004' AS DATETIME));
Because the month number has to be casted to a char first, distinguish
the two cases : month-number is one or two digit(s). (Perhaps a trim
function could be used instead of CASE WHEN )
Then cast the date string to a date and use the datename function. I
used the dateformat dd/mm/yyyy.
mat schrieb:
> what i am trying to do is convert a number eg 5, into its
> month value eg 'september' all date functions such as
> datename() & month() seem to ask for a date as there input
> parameter
>
>>--Original Message--
>>Use the DATENAME() function.
>>--
>>Tibor Karaszi, SQL Server MVP
>>http://www.karaszi.com/sqlserver/default.asp
>>
>>"mat" <anonymous@.discussions.microsoft.com> wrote in
> message news:cfdb01c4399a$26df1f00$a101280a@.phx.gbl...
>>how do i return the month in text from a number?
>>the vb function is monthname(), there does not seem to
> be
>>an equivelent in TSQL
>>
>>.

MOLAP or ROLAP

Hi,

I have to implement a report where a text search functionality is to be provided. There will be 3 search options for report users: whole phrase, some of the words or all of the words.

The user will key in the words to be searched and depending on the search option chosen, records must be displayed where two free text columns provide the match.

What would be the performance implications of implementing this in a MOLAP platform? Or is ROLAP more suited for this purpose?

Regards,

Emil

Hi Emil,

It's not obvious from your report description why you're considering an OLAP solution at all - is the data multi-dimensional?

|||

Hi Deepak,

The data is multi-dimensional. The columns where text search is to be performed holds problem and its solution. There are other dimensions on which most of the analysis of the data is done. The fact holds no aggregatable measures except count of records for the various dimensions.

I hope I have been able to give you some clarity.

Thanks and Regards,

Emil