FTI and multiple columns - another question, but opposite behaviour?

I have a table with 2 FTI columns, but my query results seems strange. If I
run this query:
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
it returns no rows.
AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
this returns one row as both words are in the same FTI column.
AND CONTAINS( J.*, '"FIFE"' )
returns the same row, as FIFE exists in the other FTI column.
So, why doesn't the top query return the row in the results? Is this normal
behaviour? Is there anything I can do to make it work?
Thanks.
A contains query can't look across columns, a freetext query can. So if your
hits come from different columns a freetext query will return a hit, whereas
a contains won't.
Hilary Cotter
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
"Joe Bloggs" <Joe.Bloggs@.acme.com> wrote in message
news:%23C6lxLZcFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have a table with 2 FTI columns, but my query results seems strange. If
I
> run this query:
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING" AND "FIFE"' )
> it returns no rows.
> AND CONTAINS( J.*, '"DEVELOPMENT" AND "MARKETING"' )
> this returns one row as both words are in the same FTI column.
> AND CONTAINS( J.*, '"FIFE"' )
> returns the same row, as FIFE exists in the other FTI column.
> So, why doesn't the top query return the row in the results? Is this
normal
> behaviour? Is there anything I can do to make it work?
> Thanks.
>

FT searching and weights

I have a couple questions about fulltext searching...
1) FULLTEXTTABLE appears to be my preference. I have 3 columns in my
catalog. I'm getting results, but I think the rankings should be different.
My lower rankings should be higher than the highest ranking results. Can I
give weight to specific columns with FULLTEXTTABLE? I don't see that option
available in BOL with FULLTEXTTABLE. If not, how could I skew the results by
saying one column is more important than another column?
2) Assume a scenario...
I index a table (10 columns, 10MB size, 100,000 rows)
Catalog is populated.
I empty and re-populate the table. Most of the data would be identical.
Can I get away with doing an incremental update? Would this be faster than a
re-population?
Or would it basically be the same thing as a full re-population?
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 1)
Hi Shank,
#1. You can do something like the below CONTAINSTABLE (or FREETEXTTABLE as
there is no FULLTEXTTABLE ) query below using the Northwind employee table
and it's two FT-enabled columns Notes and Title with different weights:
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as
B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
Also, you should review SQL Server 2000 BOL title "Full-text Search
Recommendations" and the last paragraph on RANK as in order to get valid
RANK values you must have statisticlly significatntly number of rows
(10,000+) in your FT-enable table.
#2. Since you're using SQL Server 2000, I'd strongly recommend that you
consider using "Change Tracking" and "Update Index in Background" as
Incremental Populations can and do take as long to complete as Full
Populations, even with no changes. If you're emptying the table and
repopulating it, I'd recommend a Full Population over CT with UIiB and over
Incremental Pop as the Incremental will have to first delete all the entries
in the FT Catalog and then re-populate it, so a Full Population is best
given your situation.
Regards,
John
"shank" <shank@.tampabay.rr.com> wrote in message
news:#jZ5A47fEHA.2524@.TK2MSFTNGP09.phx.gbl...
> I have a couple questions about fulltext searching...
> 1) FULLTEXTTABLE appears to be my preference. I have 3 columns in my
> catalog. I'm getting results, but I think the rankings should be
different.
> My lower rankings should be higher than the highest ranking results. Can I
> give weight to specific columns with FULLTEXTTABLE? I don't see that
option
> available in BOL with FULLTEXTTABLE. If not, how could I skew the results
by
> saying one column is more important than another column?
> 2) Assume a scenario...
> I index a table (10 columns, 10MB size, 100,000 rows)
> Catalog is populated.
> I empty and re-populate the table. Most of the data would be identical.
> Can I get away with doing an incremental update? Would this be faster than
a
> re-population?
> Or would it basically be the same thing as a full re-population?
>
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.1 (Build 2600: Service Pack 1)
>

FT Index on [German|English] columns. SQL Server 2005

Hi,
sorry for the cryptic topic. I wasn't able to come up with anything
better.
We are currently working on a project thats aims to keep all students
thesis es in a database and enable searches on it via a ASP .NET web
app. The problem we have is that some students write their thesis in
german and some in english. Right now there are more germans texts than
english ones but i guess that will change in the near future. We've
heard it's possible to maintain a fulltext index with both languages at
once but we would have to specify with our queries which index should
be used. While this is ok for entering data, asking the user if he is
going to enter his thesis in english or german, asking someone that is
searching we find it to be troublesome or at least leading to
confusion. Most of the german thesis texts contain a fair number of
english words too (computer science kinda lives from english buzzwords
;-)). So languages might be even mixed inside a text.
Anyone recommendations on how to solve this kinda of problem? Would it
be reasonable to "merge" the englisch and german stopword list and
create a "new language"?
Thanks for your help! All hints/links appreciated.
Kind regards from Germany
Phil
Phil,
In SQL Server 2005 Full-Text Search (FTS) you can store and search on
language-specific words or phrases at query time via CONTAINS or
FREETEXT. So, you can store both languages (English and German are both
supported FTS languages) and use either English or German for the
"Language for Word Breaker" and then use the following CONTAINS
statement (with or without the formsof(inflectional) parameter):
select * from FTSTable where
contains(*,'formsof(inflectional,"english_word")', language 1033) OR
contains(*,'formsof(inflectional,"German_word")',l anguage 1031)
Additionally, on an advance search form, you may want to add a box for
keyword and the associated language, so that the searchers can select
the approprate language for the keyword. Note, the default language is
whatever language you defined for the "Language for Word Breaker".
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||Such a project is doomed. German and English generate a large number of
false friends/false conjugates; wander words/worts which are common in both
languages but with different meanings. For instance Gift in English means a
present; in German its means poison.
So what will happen no matter what the language you specify your query to
occur in you will get false hits. The better way to do it is to sniff your
browser settings and check for German as the supported language and then
have the docs stored in different columns/tables/databases. Then a query
would be directed against a language segregated column/table or database.
If your queries are done using the Contains predicate the false conjugates
will be minimized.
Hilary Cotter
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
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131651975.271992.103620@.z14g2000cwz.googlegr oups.com...
> Hi,
> sorry for the cryptic topic. I wasn't able to come up with anything
> better.
> We are currently working on a project thats aims to keep all students
> thesis es in a database and enable searches on it via a ASP .NET web
> app. The problem we have is that some students write their thesis in
> german and some in english. Right now there are more germans texts than
> english ones but i guess that will change in the near future. We've
> heard it's possible to maintain a fulltext index with both languages at
> once but we would have to specify with our queries which index should
> be used. While this is ok for entering data, asking the user if he is
> going to enter his thesis in english or german, asking someone that is
> searching we find it to be troublesome or at least leading to
> confusion. Most of the german thesis texts contain a fair number of
> english words too (computer science kinda lives from english buzzwords
> ;-)). So languages might be even mixed inside a text.
> Anyone recommendations on how to solve this kinda of problem? Would it
> be reasonable to "merge" the englisch and german stopword list and
> create a "new language"?
> Thanks for your help! All hints/links appreciated.
> Kind regards from Germany
> Phil
>
|||Thanks for your input! This basicaly means i'll have to tell SQL Server
which language i want it to use? On inserts and searches? Meaning i
have to ask the user if the text he is going to submit is english or
german (or apply some magic to detect the language)?
|||I wish there was a german-english word breaker :-) Detecting browser
language settings won't help us much. People can choose to write their
diploma/master thesis in german or english. Browser language settings
will be german all the way since student can only enter their thesis
from within the university, where all clients are "german". Most user
searching for a thesis will use some acronyms/keywords like XML, C#,
UML etc which are kinda language indepedent. But there are some
technological terms which have a good and commonly used german
expression, usually a longer word combined from substantives so i think
the correct wordbreaker would help a lot if a user search for a
substring of the longer word. Thanks for pointing the problems out.
Seems to me that our goal is kinda not feasable right now :-/
|||You're welcome, Philipp,
Yes, you still need to define the "Language for Word Breaker" (either German
or English) and you only need to do this once when you define the Full-Text
Catalog and the specific FT-enabled column where these languages are stored.
The default will be which ever language you define, and you only need to
specific the non-default language at query time, i.e., when you execute a
CONTAINS or FREETEXT searches.
No, you don't (and cannot) define the language during INSERTS as
syntactically this is not possible with the INSERT statement. The SQL Server
2005 FTS engine (msftesql.exe) will detect and determine the language of the
mixed text in this column.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131886568.811593.201700@.o13g2000cwo.googlegr oups.com...
> Thanks for your input! This basicaly means i'll have to tell SQL Server
> which language i want it to use? On inserts and searches? Meaning i
> have to ask the user if the text he is going to submit is english or
> german (or apply some magic to detect the language)?
>

From varchar(max) to xml

I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
Column a contains the following data:
Col1;Col2
New York;USA
Rio;Brasil
Tokio;Japan
The first line contains the column header, the following the data.
The data should be transferred to column b with the following xml-structure:
<Col1>New York</Col1><Col2>USA</Col2>
<Col1>Rio</Col1><Col2>Brasil</Col2>
<Col1>Tokio</Col1><Col2>Japan</Col2>
The number of columns and the column names are various.
Any ideas?
Thanks psychodad71
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1It looks like this would require a lot of string manipulation. Although I
believe it could be done with T-SQL, the string functions are a little limit
ed.
I'd suggest you use the CLR.
I'll give it a shot myself when I get some time and I'll post an update.
Denis Ruckebusch
http://blogs.msdn.com/denisruc
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||I can't think of a nice set-based method of doing this, but you could use
procedural code to do it. I agree with Denis, this should probably be done
in the front end. But that said, here's a little procedural sample. Note
that I don't have SQL 2005 on the computer I'm at right now so I had to put
this thing together on SQL 2000. It should run properly on SQL 2005 as
well. It's *extremely* procedural and assumes that the TestInput table has
a numeric id for each row, row 0 being the column names and all other rows
containing data. The procedural nature of this type of code makes me think
you'd be a lot better off doing it on the front end though:
-- Create a "Numbers" table and an inline UDF that uses it to parse your
-- comma-delimited string. Run this section one time.
SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
INTO Numbers
FROM syscomments a1
CROSS JOIN syscomments a2
-- Add Primary Key to Numbers table
ALTER TABLE Numbers
ALTER COLUMN Number INT NOT NULL
ALTER TABLE Numbers
ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
-- Create inline UDF
GO
CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
RETURNS TABLE
AS
RETURN (
SELECT Number, LTRIM(RTRIM(CASE Number
WHEN 1 THEN SUBSTRING(@.list, 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - 1
ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
END)
ELSE SUBSTRING(@.list, Number + 1,
CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
CHARINDEX(';', @.list, Number + 1) - Number - 1
ELSE LEN(@.list)
END)
END)) AS Value
FROM Numbers
WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
)
GO
-- End of the Numbers table/UDF initialization.
CREATE TABLE TestInput([id] INT PRIMARY KEY,
a VARCHAR(8000),
b VARCHAR(8000))
INSERT INTO TestInput([id], a)
SELECT 0, 'Col1;Col2'
UNION SELECT 1, 'New York;USA'
UNION SELECT 2, 'Rio;Brasil'
UNION SELECT 3, 'Tokio;Japan'
DECLARE @.sql VARCHAR(8000)
DECLARE @.temp_str VARCHAR(8000)
DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[col_name] VARCHAR(8000))
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = 0
INSERT INTO @.cols([col_name])
SELECT Value
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
DECLARE @.col_count INT
SELECT @.col_count = MAX([id_num])
FROM @.cols
DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[value] VARCHAR(8000))
DECLARE @.id INT
SELECT @.id = 1
DECLARE @.i INT
WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
BEGIN
SELECT @.temp_str = a
FROM TestInput
WHERE [id] = @.id
IF NOT(@.temp_str IS NULL)
BEGIN
INSERT INTO @.vals([value])
SELECT [Value]
FROM dbo.ParseDelimitedList(@.temp_str)
ORDER BY [Number]
SELECT @.temp_str = ''
SELECT @.i = 1
WHILE @.i <= @.col_count
BEGIN
SELECT @.temp_str = @.temp_str + '<' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.temp_str = @.temp_str +
(
SELECT COALESCE([value], '')
FROM @.vals
WHERE [id_num] = @.i + (@.id - 1) * @.col_count
)
SELECT @.temp_str = @.temp_str + '</' +
(
SELECT [col_name]
FROM @.cols
WHERE [id_num] = @.i
) + '>'
SELECT @.i = @.i + 1
END
UPDATE TestInput
SET b = @.temp_str
WHERE [id] = @.id
END
SELECT @.id = @.id + 1
END
SELECT *
FROM @.vals
SELECT *
FROM TestInput
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:6232e7ee08949@.uwe...
>I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...er-xml/200606/1|||Ooops, the "syscomments" references will need to be changed for SQL 2005 to
"sys.comments".
"Mike C#" <xyz@.xyz.com> wrote in message
news:ORxRYYwlGHA.2056@.TK2MSFTNGP03.phx.gbl...
>I can't think of a nice set-based method of doing this, but you could use
>procedural code to do it. I agree with Denis, this should probably be done
>in the front end. But that said, here's a little procedural sample. Note
>that I don't have SQL 2005 on the computer I'm at right now so I had to put
>this thing together on SQL 2000. It should run properly on SQL 2005 as
>well. It's *extremely* procedural and assumes that the TestInput table has
>a numeric id for each row, row 0 being the column names and all other rows
>containing data. The procedural nature of this type of code makes me think
>you'd be a lot better off doing it on the front end though:
> -- Create a "Numbers" table and an inline UDF that uses it to parse your
> -- comma-delimited string. Run this section one time.
> SELECT TOP 10000 number = IDENTITY(INT, 1, 1)
> INTO Numbers
> FROM syscomments a1
> CROSS JOIN syscomments a2
> -- Add Primary Key to Numbers table
> ALTER TABLE Numbers
> ALTER COLUMN Number INT NOT NULL
> ALTER TABLE Numbers
> ADD CONSTRAINT PK_Numbers PRIMARY KEY (Number)
> -- Create inline UDF
> GO
> CREATE FUNCTION dbo.ParseDelimitedList (@.list AS NVARCHAR(4000))
> RETURNS TABLE
> AS
> RETURN (
> SELECT Number, LTRIM(RTRIM(CASE Number
> WHEN 1 THEN SUBSTRING(@.list, 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - 1
> ELSE LEN(@.list) - CHARINDEX(';', @.list, Number + 1)
> END)
> ELSE SUBSTRING(@.list, Number + 1,
> CASE WHEN CHARINDEX(';', @.list, Number + 1) > 0 THEN
> CHARINDEX(';', @.list, Number + 1) - Number - 1
> ELSE LEN(@.list)
> END)
> END)) AS Value
> FROM Numbers
> WHERE (SUBSTRING(@.list, Number, 1) = ';' OR Number = 1)
> )
> GO
> -- End of the Numbers table/UDF initialization.
> CREATE TABLE TestInput([id] INT PRIMARY KEY,
> a VARCHAR(8000),
> b VARCHAR(8000))
> INSERT INTO TestInput([id], a)
> SELECT 0, 'Col1;Col2'
> UNION SELECT 1, 'New York;USA'
> UNION SELECT 2, 'Rio;Brasil'
> UNION SELECT 3, 'Tokio;Japan'
> DECLARE @.sql VARCHAR(8000)
> DECLARE @.temp_str VARCHAR(8000)
> DECLARE @.cols TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [col_name] VARCHAR(8000))
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = 0
> INSERT INTO @.cols([col_name])
> SELECT Value
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> DECLARE @.col_count INT
> SELECT @.col_count = MAX([id_num])
> FROM @.cols
> DECLARE @.vals TABLE ([id_num] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
> [value] VARCHAR(8000))
> DECLARE @.id INT
> SELECT @.id = 1
> DECLARE @.i INT
> WHILE @.id <= (SELECT MAX([id]) FROM TestInput)
> BEGIN
> SELECT @.temp_str = a
> FROM TestInput
> WHERE [id] = @.id
> IF NOT(@.temp_str IS NULL)
> BEGIN
> INSERT INTO @.vals([value])
> SELECT [Value]
> FROM dbo.ParseDelimitedList(@.temp_str)
> ORDER BY [Number]
> SELECT @.temp_str = ''
> SELECT @.i = 1
> WHILE @.i <= @.col_count
> BEGIN
> SELECT @.temp_str = @.temp_str + '<' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.temp_str = @.temp_str +
> (
> SELECT COALESCE([value], '')
> FROM @.vals
> WHERE [id_num] = @.i + (@.id - 1) * @.col_count
> )
> SELECT @.temp_str = @.temp_str + '</' +
> (
> SELECT [col_name]
> FROM @.cols
> WHERE [id_num] = @.i
> ) + '>'
> SELECT @.i = @.i + 1
> END
> UPDATE TestInput
> SET b = @.temp_str
> WHERE [id] = @.id
> END
> SELECT @.id = @.id + 1
> END
> SELECT *
> FROM @.vals
> SELECT *
> FROM TestInput
>
>
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:6232e7ee08949@.uwe...
>|||psychodad71 via webservertalk.com wrote:
> I have a table with 2 columns. Column a(varchar(max)) and column b(xml).
> Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following xml-structur
e:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
If your database supports access to external scripting languages, dump
column a out and pass it through the following filter
awk -F\; 'BEGIN {ORS=""}
{if(NR==1)n=split($0,gi);else{for(i=1;i<=NF;++i)print "<" gi[i] ">" $i
"</" gi[i] ">";print "\n"}}'
and read the result into column b. The GNU awk processor for Windows can
be downloaded from http://gnuwin32.sourceforge.net/packages/gawk.htm
///Peter|||Your table will need something to identity the first row, so I added an iden
tity
col. The first row inserted is assumed to be a column header.
CREATE TABLE #t1
(
id int identity primary key,
city VARCHAR(50),
xdata xml DEFAULT ''
)
INSERT INTO #t1 (city) values ('Col1;Col2;Col3;Col4')
INSERT INTO #t1 (city) values ('New York;Boston;Chicago;USA')
INSERT INTO #t1 (city) values ('Rio;Bla;Sao Paulo;Brasil')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;Japan')
INSERT INTO #t1 (city) values ('Tokio;Nagasaki;ABCD;EF;Japan')
The city column has a compound value in it. You can use recursion to "unflat
ten"
this into a table with one row per city. Once you have done that you can
compose that table into xml, using recursion again.
-- start by making CTE of elementNames
WITH elementNames
AS
(
SELECT TOP(1) id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1)
as colName, RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remai
n
from #t1
ORDER BY id
UNION ALL
SELECT t.id, en.colNum + 1 as colNum, LEFT(en.remain, CHARINDEX(';', en.rema
in)-1)
as single, RIGHT(en.remain, LEN(en.remain)-CHARINDEX(';', en.remain)) as
remain from elementNames en
JOIN #t1 AS t ON t.id = en.id
WHERE LEN(remain)>0
),
-- now recurse to "unflatten" the composite value in the city column
pos
AS
(
-- find the first city
SELECT id, 1 as colNum, LEFT(city+';', CHARINDEX(';', city+';')-1) as single
,
RIGHT(city+';', LEN(city+';')-CHARINDEX(';', city+';')) as remain from #t1
WHERE id not in (select id from elementNames)
UNION ALL
-- find the rest
SELECT id, colNum + 1 as colNum,
LEFT(remain, CHARINDEX(';', remain)-1) as single, RIGHT(remain, LEN(remain)-
CHARINDEX(';',
remain))
as remain from pos
where LEN(remain) > 0
and id not in (select id from elementNames)
),
-- now compose xml of of the expanded table
compose
as
(
SELECT p.id, p.colNum, CAST('<' + e.colName + '>' + p.single + '</' + e.colN
ame
+ '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
where p.colNum = 1
UNION ALL
SELECT p.id, p.colNum, CAST(c.xdata + '<' + e.colName + '>' + p.single +
'</' + e.colName + '>'
as VARCHAR(MAX)) as xdata from pos AS p
JOIN elementNames AS e ON p.colNum = e.colNum
JOIN compose AS c on p.colNum = c.colNum+1 and p.id = c.id
)
-- use composed xml to update the original table
UPDATE #t1 set xdata = (SELECT xdata from compose where #t1.id = compose.id
AND compose.colNum = (SELECT MAX(colNum) from compose as c WHERE c.id = #t1.
id)
)
Then to test the results:
SELECT * FROM #t1
1 Col1;Col2;Col3;Col4 NULL
2 New York;Boston;Chicago;USA <Col1>New
York</Col1><Col2>Boston</Col2><Col3>Chicago</Col3><Col4>USA</Col4>
3 Rio;Bla;Sao Paulo;Brasil <Col1>Rio</Co
l1><Col2>Bla</Col2><Col3>Sao
Paulo</Col3><Col4>Brasil</Col4>
4 Tokio;Nagasaki;ABCD;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>Japan</Col4>
5 Tokio;Nagasaki;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>Japan</Col3>
6 Tokio;Nagasaki;ABCD;EF;Japan <Col1>Tokio</
Col1><Col2>Nagasaki</Col2><Col3>ABCD</Col3><Col4>EF</Col4>
Note that this works as even when the number of column headings do not match
the number of cities, though the results might not be what you want.
Dan

> I have a table with 2 columns. Column a(varchar(max)) and column
> b(xml). Column a contains the following data:
> Col1;Col2
> New York;USA
> Rio;Brasil
> Tokio;Japan
> The first line contains the column header, the following the data.
> The data should be transferred to column b with the following
> xml-structure:
> <Col1>New York</Col1><Col2>USA</Col2>
> <Col1>Rio</Col1><Col2>Brasil</Col2>
> <Col1>Tokio</Col1><Col2>Japan</Col2>
> The number of columns and the column names are various.
> Any ideas?
> Thanks psychodad71
>|||For some reason I see that some of the xml like stuff I have shown in this q
uery seems to be lost once it is posted. I have attached a text file version
of it.
Dan|||Alternatively, using a numbers table
as in http://www.aspfaq.com/show.asp?id=2516
you can do this
;
with Headers(id,rn,ColName)
as(
select id,
rank() over(order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id=1),
Cities(id,rn,City)
as(
select id,
rank() over(partition by id order by Number),
ltrim(substring(city,
Number,
charindex(';',
city + ';',
Number) - Number))
from #t1
inner join Numbers on Number between 1 and len(city) + 1
and substring(';' + city, Number, 1) = ';'
where id>1)
update #t1
set xdata=(select cast('<'+h.ColName+'>'+c.City+'</'+h.ColName+'>' as
xml)
from Headers h
inner join Cities c on c.rn=h.rn
where c.id=#t1.id
for xml path(''))
select * from #t1
Regards
Mark|||Slight correction.
where c.id=#t1.id
for xml path(''))
should be
where c.id=#t1.id
order by c.rn
for xml path(''))

Freeze Panes?

Is there functionality within the Table or Matrix objects to freeze columns or row headers when you scroll thru a wide or long report? Similay to the freeze panes functionality in Excel.

Thanks

Yes, RS 2005 provides this functionality. E.g. in the table properties dialog on the general tab, select "Header should remain visible while scrolling".

-- Robert

|||Sorry.. i may be missing something obvious within the properties, but where is this functionality with a Matrix for row and column headings?|||

In the matrix case, the setting is on the row / column groupings. For instance, you can
1. open the matrix properties dialog
2. go to the Groups tab
3. edit a column or row group
4. and on the general tab of the grouping and sorting dialog, select "Group header should remain visible while scrolling"

-- Robert

|||

I noticed that facility in RS 2005, but could not found it in RS 2000.
I'd appreciate if anybody could give a work around.
Vincent

|||

As I mentioned in my initial response, only RS 2005 provides this feature. It is not available in RS 2000.

-- Robert

|||Thank you Robert.
Vincent|||

I am unable to get this to work on my matrix. When I select 'group header should remain visible while scrolling' (for either row or column), I then get the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

Can anyone provide any help?

|||It seems that your matrix is nested inside another data control which as the error message suggests does not allow fixed headers.|||

Hi ,

Is ther any way to keep the first set of N columns along with the data as frozen and the other columns move behind it as in excel.

I tried to check the option in layout --> fixed header = true but the header along remains frozen but the data is getting overlapped.

please do help at the earliest.

thanks,
Vidula

|||

Hi,

I got the same problem when I select 'group header should remain visible while scrolling' (for either row or column), I got the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

I have matrix inside a table which I set FixedHeader to False but still got the same error. I can't find other places who set fixedheader properties. Any suggestion would be very appraciate!

Thanks in advance!

Freeze Panes?

Is there functionality within the Table or Matrix objects to freeze columns or row headers when you scroll thru a wide or long report? Similay to the freeze panes functionality in Excel.

Thanks

Yes, RS 2005 provides this functionality. E.g. in the table properties dialog on the general tab, select "Header should remain visible while scrolling".

-- Robert

|||Sorry.. i may be missing something obvious within the properties, but where is this functionality with a Matrix for row and column headings?|||

In the matrix case, the setting is on the row / column groupings. For instance, you can
1. open the matrix properties dialog
2. go to the Groups tab
3. edit a column or row group
4. and on the general tab of the grouping and sorting dialog, select "Group header should remain visible while scrolling"

-- Robert

|||

I noticed that facility in RS 2005, but could not found it in RS 2000.
I'd appreciate if anybody could give a work around.
Vincent

|||

As I mentioned in my initial response, only RS 2005 provides this feature. It is not available in RS 2000.

-- Robert

|||Thank you Robert.
Vincent|||

I am unable to get this to work on my matrix. When I select 'group header should remain visible while scrolling' (for either row or column), I then get the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

Can anyone provide any help?

|||It seems that your matrix is nested inside another data control which as the error message suggests does not allow fixed headers.|||

Hi ,

Is ther any way to keep the first set of N columns along with the data as frozen and the other columns move behind it as in excel.

I tried to check the option in layout --> fixed header = true but the header along remains frozen but the data is getting overlapped.

please do help at the earliest.

thanks,
Vidula

|||

Hi,

I got the same problem when I select 'group header should remain visible while scrolling' (for either row or column), I got the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

I have matrix inside a table which I set FixedHeader to False but still got the same error. I can't find other places who set fixedheader properties. Any suggestion would be very appraciate!

Thanks in advance!

Freeze Panes?

Is there functionality within the Table or Matrix objects to freeze columns or row headers when you scroll thru a wide or long report? Similay to the freeze panes functionality in Excel.

Thanks

Yes, RS 2005 provides this functionality. E.g. in the table properties dialog on the general tab, select "Header should remain visible while scrolling".

-- Robert

|||Sorry.. i may be missing something obvious within the properties, but where is this functionality with a Matrix for row and column headings?|||

In the matrix case, the setting is on the row / column groupings. For instance, you can
1. open the matrix properties dialog
2. go to the Groups tab
3. edit a column or row group
4. and on the general tab of the grouping and sorting dialog, select "Group header should remain visible while scrolling"

-- Robert

|||

I noticed that facility in RS 2005, but could not found it in RS 2000.
I'd appreciate if anybody could give a work around.
Vincent

|||

As I mentioned in my initial response, only RS 2005 provides this feature. It is not available in RS 2000.

-- Robert

|||Thank you Robert.
Vincent|||

I am unable to get this to work on my matrix. When I select 'group header should remain visible while scrolling' (for either row or column), I then get the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

Can anyone provide any help?

|||It seems that your matrix is nested inside another data control which as the error message suggests does not allow fixed headers.|||

Hi ,

Is ther any way to keep the first set of N columns along with the data as frozen and the other columns move behind it as in excel.

I tried to check the option in layout --> fixed header = true but the header along remains frozen but the data is getting overlapped.

please do help at the earliest.

thanks,
Vidula

|||

Hi,

I got the same problem when I select 'group header should remain visible while scrolling' (for either row or column), I got the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

I have matrix inside a table which I set FixedHeader to False but still got the same error. I can't find other places who set fixedheader properties. Any suggestion would be very appraciate!

Thanks in advance!

Freeze Panes?

Is there functionality within the Table or Matrix objects to freeze columns or row headers when you scroll thru a wide or long report? Similay to the freeze panes functionality in Excel.

Thanks

Yes, RS 2005 provides this functionality. E.g. in the table properties dialog on the general tab, select "Header should remain visible while scrolling".

-- Robert

|||Sorry.. i may be missing something obvious within the properties, but where is this functionality with a Matrix for row and column headings?|||

In the matrix case, the setting is on the row / column groupings. For instance, you can
1. open the matrix properties dialog
2. go to the Groups tab
3. edit a column or row group
4. and on the general tab of the grouping and sorting dialog, select "Group header should remain visible while scrolling"

-- Robert

|||

I noticed that facility in RS 2005, but could not found it in RS 2000.
I'd appreciate if anybody could give a work around.
Vincent

|||

As I mentioned in my initial response, only RS 2005 provides this feature. It is not available in RS 2000.

-- Robert

|||Thank you Robert.
Vincent|||

I am unable to get this to work on my matrix. When I select 'group header should remain visible while scrolling' (for either row or column), I then get the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

Can anyone provide any help?

|||It seems that your matrix is nested inside another data control which as the error message suggests does not allow fixed headers.|||

Hi ,

Is ther any way to keep the first set of N columns along with the data as frozen and the other columns move behind it as in excel.

I tried to check the option in layout --> fixed header = true but the header along remains frozen but the data is getting overlapped.

please do help at the earliest.

thanks,
Vidula

|||

Hi,

I got the same problem when I select 'group header should remain visible while scrolling' (for either row or column), I got the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

I have matrix inside a table which I set FixedHeader to False but still got the same error. I can't find other places who set fixedheader properties. Any suggestion would be very appraciate!

Thanks in advance!

Freeze Panes?

Is there functionality within the Table or Matrix objects to freeze columns or row headers when you scroll thru a wide or long report? Similay to the freeze panes functionality in Excel.

Thanks

Yes, RS 2005 provides this functionality. E.g. in the table properties dialog on the general tab, select "Header should remain visible while scrolling".

-- Robert

|||Sorry.. i may be missing something obvious within the properties, but where is this functionality with a Matrix for row and column headings?|||

In the matrix case, the setting is on the row / column groupings. For instance, you can
1. open the matrix properties dialog
2. go to the Groups tab
3. edit a column or row group
4. and on the general tab of the grouping and sorting dialog, select "Group header should remain visible while scrolling"

-- Robert

|||

I noticed that facility in RS 2005, but could not found it in RS 2000.
I'd appreciate if anybody could give a work around.
Vincent

|||

As I mentioned in my initial response, only RS 2005 provides this feature. It is not available in RS 2000.

-- Robert

|||Thank you Robert.
Vincent|||

I am unable to get this to work on my matrix. When I select 'group header should remain visible while scrolling' (for either row or column), I then get the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

Can anyone provide any help?

|||It seems that your matrix is nested inside another data control which as the error message suggests does not allow fixed headers.|||

Hi ,

Is ther any way to keep the first set of N columns along with the data as frozen and the other columns move behind it as in excel.

I tried to check the option in layout --> fixed header = true but the header along remains frozen but the data is getting overlapped.

please do help at the earliest.

thanks,
Vidula

|||

Hi,

I got the same problem when I select 'group header should remain visible while scrolling' (for either row or column), I got the error

[rsFixedHeadersInInnerDataRegion] The matrix ‘matrix1’ includes a table header or column with FixedHeader set to true. This is not allowed on data regions inside other data regions.

I have matrix inside a table which I set FixedHeader to False but still got the same error. I can't find other places who set fixedheader properties. Any suggestion would be very appraciate!

Thanks in advance!

Freeze panes like excel

Is there a chance that columns used in sql reporting services can be freezed
as in excel work sheet.
Is it not implemented till date, if they implement when can we expect that
to happen for sql reporting services.
Freezin pane is one part they even want to freeze there own columns
dynamically.
Is there a chance in near future we have this provision, if we have it would
be great.
Any thought of when can we expect it.
Askin a lot :-)
NavinNavin,
There is a setting in RS that you can apply per object (matrix, table), in
which you can specifiy whether you want the header to remain visible when
scrolling. Look for this on the general tab. Name of the setting is "Header
should remain visible when scrolling".
(this is in RS2005)
regards,
Perry
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> Is there a chance that columns used in sql reporting services can be
> freezed
> as in excel work sheet.
> Is it not implemented till date, if they implement when can we expect that
> to happen for sql reporting services.
> Freezin pane is one part they even want to freeze there own columns
> dynamically.
> Is there a chance in near future we have this provision, if we have it
> would
> be great.
> Any thought of when can we expect it.
> Askin a lot :-)
> Navin|||Freezing columns is big priority for us as well, and I've asked about
about it several times, but the feature is not available.
And I wouldn't expect it in a subsequent release, just based on the
technical issues to implement it, but maybe I'll be surprised.
NAVIN.D wrote:
> Is there a chance that columns used in sql reporting services can be freezed
> as in excel work sheet.
> Is it not implemented till date, if they implement when can we expect that
> to happen for sql reporting services.
> Freezin pane is one part they even want to freeze there own columns
> dynamically.
> Is there a chance in near future we have this provision, if we have it would
> be great.
> Any thought of when can we expect it.
> Askin a lot :-)
> Navin|||Seems certain part of my question solved, look forward for dynamic selection
as well, Thank for the info Perry
"Perry" wrote:
> Navin,
> There is a setting in RS that you can apply per object (matrix, table), in
> which you can specifiy whether you want the header to remain visible when
> scrolling. Look for this on the general tab. Name of the setting is "Header
> should remain visible when scrolling".
> (this is in RS2005)
> regards,
> Perry
>
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> > Is there a chance that columns used in sql reporting services can be
> > freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it
> > would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>
>|||unfortunatly it dont work the way i intended i want column to be scrolled as
well not only headers, supose i have 20 columns i want only two columns to be
freezed. and as i traversve through other columns the 2 columns which are
freezed still exists.
"Perry" wrote:
> Navin,
> There is a setting in RS that you can apply per object (matrix, table), in
> which you can specifiy whether you want the header to remain visible when
> scrolling. Look for this on the general tab. Name of the setting is "Header
> should remain visible when scrolling".
> (this is in RS2005)
> regards,
> Perry
>
> "NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
> news:9E12F763-4546-4C89-9347-C2E7F479E1B0@.microsoft.com...
> > Is there a chance that columns used in sql reporting services can be
> > freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it
> > would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>
>|||Header scrolling dont work for matrix, only table header got that option.
"cowznofsky" wrote:
> Freezing columns is big priority for us as well, and I've asked about
> about it several times, but the feature is not available.
> And I wouldn't expect it in a subsequent release, just based on the
> technical issues to implement it, but maybe I'll be surprised.
> NAVIN.D wrote:
> > Is there a chance that columns used in sql reporting services can be freezed
> > as in excel work sheet.
> >
> > Is it not implemented till date, if they implement when can we expect that
> > to happen for sql reporting services.
> >
> > Freezin pane is one part they even want to freeze there own columns
> > dynamically.
> >
> > Is there a chance in near future we have this provision, if we have it would
> > be great.
> >
> > Any thought of when can we expect it.
> >
> > Askin a lot :-)
> >
> > Navin
>

Freeze Panes in Excel

Is there anything I can do so that when my users export their report from
reporting services to Excel that it freezes the columns'
I also need to set a default zoom %
Is there anyway to do this - thanksI also have that problem. Because when it expands, it can be really time
consuming..
"dragon" wrote:
> Is there anything I can do so that when my users export their report from
> reporting services to Excel that it freezes the columns'
> I also need to set a default zoom %
> Is there anyway to do this - thanks
>

freeze columns (not headers

I have many columns out to the right. The name column is the first column in
the report. I wan to "freeze" the name column so that when you scroll to the
right in the report the name column is still visible.
From this forum i learn that freeze headers isn't available until RS 2005.
Is this the same case for Freeze columns?
Does anyone else try to do this?I'm not sure that Freeze Columns will even be available in the initial RS
2005 release. I haven't heard anything about it and it's a feature that I
would love to see.
Currently the only way to accomplish these things are to programmatically
pull reports from RS and save the resulting output file. Then your
application must open the Excel file and manipulate it directly. You'll need
to have a way for the application to know which columns/rows get frozen for
each report that you have.
It's doable, but you have to have a lot of custom code done and it does slow
down the delivery speed some.
"Phillip" wrote:
> I have many columns out to the right. The name column is the first column in
> the report. I wan to "freeze" the name column so that when you scroll to the
> right in the report the name column is still visible.
> From this forum i learn that freeze headers isn't available until RS 2005.
> Is this the same case for Freeze columns?
> Does anyone else try to do this?|||It will be available in RS 2005.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd" <Todd@.discussions.microsoft.com> wrote in message
news:F6011202-F219-48FB-8674-EEA5E938FEA2@.microsoft.com...
> I'm not sure that Freeze Columns will even be available in the initial RS
> 2005 release. I haven't heard anything about it and it's a feature that I
> would love to see.
> Currently the only way to accomplish these things are to programmatically
> pull reports from RS and save the resulting output file. Then your
> application must open the Excel file and manipulate it directly. You'll
> need
> to have a way for the application to know which columns/rows get frozen
> for
> each report that you have.
> It's doable, but you have to have a lot of custom code done and it does
> slow
> down the delivery speed some.
> "Phillip" wrote:
>> I have many columns out to the right. The name column is the first column
>> in
>> the report. I wan to "freeze" the name column so that when you scroll to
>> the
>> right in the report the name column is still visible.
>> From this forum i learn that freeze headers isn't available until RS
>> 2005.
>> Is this the same case for Freeze columns?
>> Does anyone else try to do this?

FREETEXTTABLE on more than one indexed fields

Is it possible to do a FT search on MULTIPLE fields in an
Index? I know that the FREETEXTTABLE can only do it on either one or all
indexed columns. But what if i want to do it for more columns.
Or is it possible to create a second FT-INDEX for the same table? I want to
index different fields in each one to use it in different areas.
Note: Full-Text seach MUST be used. I know I could write an OR statement,
but it must be done with a FT search. Any ideas?Denis,
See, my reply in the fulltext newsgroup, but yes, this can be done, see "SQL
Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
and substitute freetexttable for containstable in the examples.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Denis" <denis@.pharmiweb.com> wrote in message
news:uzC6lOJKFHA.1340@.TK2MSFTNGP10.phx.gbl...
> Is it possible to do a FT search on MULTIPLE fields in an
> Index? I know that the FREETEXTTABLE can only do it on either one or all
> indexed columns. But what if i want to do it for more columns.
> Or is it possible to create a second FT-INDEX for the same table? I want
to
> index different fields in each one to use it in different areas.
>
> Note: Full-Text seach MUST be used. I know I could write an OR
statement,
> but it must be done with a FT search. Any ideas?
>|||I looked at what you said but I am not sure how that would work. Let me
give you an idea of the type of how i am trying to do the search.
-- Old Search
--SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, *, ''' + @.Keywords + ''') as KW ON
FT_TBL.uID = KW.[KEY]'
--SET @.RankField = 'KW.RANK'
-- NEW SEARCH
SET @.dynQuery = @.dynQuery + ' INNER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldRequirementshtm, ''' + @.Keywords +
''') as KW ON FT_TBL.uID = KW.[KEY] '
SET @.RankField = 'KW.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldJobTitle, ''' + @.Keywords + ''') as
KWw ON FT_TBL.uID = KWw.[KEY]'
SET @.RankField = 'KWw.RANK'
SET @.dynQuery = @.dynQuery + ' FULL OUTER JOIN
FREETEXTTABLE(tblJobsDataWareHouse, fldCompanyName, ''' + @.Keywords + ''')
as KWww ON FT_TBL.uID = KWww.[KEY]'
SET @.RankField = 'KWww.RANK'
I want my new search to look at Title, Description and Company name only,
rather than every field that was included.

FreeTextTable multiple Columns

I'm trying to do a FREETEXTTABLE search on two columns.
I can get it to work correctly with searching only one column.
I basically need it to do an OR on the two columns and haven't been
able to find anything on that. It seems like I'm limited to finding
matches that have it in both columns.
What I have so far is:
SELECT a.Name, b.rank, c.rank
FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
FreeTextTable(Test, SolutionProblem, 'author) AS c
I picture it being in the WHERE clause as:
WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
SolutionProblem, 'author'))
but I haven't found an example where I can put it in the where clause.
Thanks for any help,
Tim
Tim,
Below is an example of using FREETEXTTABLE with two columns from the same
table (Northwind's Employee) with an OR condition on the join of the KEYs:
use Northwind
go
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'psychology') as A,
containstable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 4 rows
Note, that with the below freetexttable query using 'include' as the search
word that 'includes' is retuned as freetext will often return more
"imprecise" results as would a samilar contains query.
SELECT e.EmployeeID, e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'include') as A,
freetexttable(Employees, Title, 'Vice') as B
where
A.[KEY] = e.EmployeeID or
B.[KEY] = e.EmployeeID
-- returns 2 rows
Hope this helps!
Regards,
John
"Tim" <tim_cavins@.hotmail.com> wrote in message
news:3020d2f4.0407071416.2866d84b@.posting.google.c om...
> I'm trying to do a FREETEXTTABLE search on two columns.
> I can get it to work correctly with searching only one column.
> I basically need it to do an OR on the two columns and haven't been
> able to find anything on that. It seems like I'm limited to finding
> matches that have it in both columns.
> What I have so far is:
> SELECT a.Name, b.rank, c.rank
> FROM Test a, FreeTextTable(Test, Keywords, 'author') AS b,
> FreeTextTable(Test, SolutionProblem, 'author) AS c
> I picture it being in the WHERE clause as:
> WHERE (FreeTextTable(Test, Keywords, 'author') OR FreeTextTable(Test,
> SolutionProblem, 'author'))
> but I haven't found an example where I can put it in the where clause.
> Thanks for any help,
> Tim

FreeText on multiple columns

Hello!
I need to do a freetext-search on multiple columns in the same table,
searching with the same keywords.
For example
WHERE FREETEXT({col1, col2}, "searching for this")
I know that this can be accomplished through
WHERE FREETEXT(col1, "searching for this")
OR FREETEXT(col2, "searching for this")
but then it will query the FTS-database (in this case) two times. But
the more oclumns the more queryies against the FTS-database.
I tried to concatenate the columns, like
WHERE FREETEXT(col1+col2, "searching for this")
but that wont work.
I know that this is possible to do through contains/containstable, but I
need to use freetext this time.
Suggestions?
Thanks
I just figured out that since I only have one FT-catalog (which has all
the columns I want to query against) on the table, I can do
WHERE FREETEXT(*, "searching for this")
dotNet wrote:
> Hello!
> I need to do a freetext-search on multiple columns in the same table,
> searching with the same keywords.
> For example
> WHERE FREETEXT({col1, col2}, "searching for this")
> I know that this can be accomplished through
> WHERE FREETEXT(col1, "searching for this")
> OR FREETEXT(col2, "searching for this")
> but then it will query the FTS-database (in this case) two times. But
> the more oclumns the more queryies against the FTS-database.
> I tried to concatenate the columns, like
> WHERE FREETEXT(col1+col2, "searching for this")
> but that wont work.
> I know that this is possible to do through contains/containstable, but I
> need to use freetext this time.
> Suggestions?
> Thanks
|||This is correct. With FreeText and FreeTextTable if you issue queries where
you don't qualify the column name(s) and your search phrase consists of more
than 1 token, ie "searching for this" has three tokens or words, your search
results might come from different columns. IE Searching in col1, for in
col2, and this in col3. Keep in mind that for, and this are noise words.
With a Contains and ContainsTable searches, all the tokens/words will have
to be in the same column for you to get a hit.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"dotNet" <dotnet@.brimba.nu> wrote in message
news:u0MXYc5AFHA.2608@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I just figured out that since I only have one FT-catalog (which has all
> the columns I want to query against) on the table, I can do
> WHERE FREETEXT(*, "searching for this")
>
>
> dotNet wrote:

FREETEXT FILTER ON FREETEXTTABLE USING COLUMN LIST FOR REFINED SEARCH

I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.

select

b.rank,

a.ProductID,

a.ProductName,

a.Sequence,

a.ProductImage,

a.ItemID,

a.ItemName,

a.ManufacturerItemCode,

a.ItemImage,

a.ItemSourceID,

a.PackageID,

a.BrandID,

a.BrandName,

a.ManufacturerID,

a.ManufacturerName,

a.ProductCategoryID,

a.CategoryID,

a.CategoryName,

d.CustomerGroupName,

isnull(h.PackageDescription,a.ItemPKG) as PKG,

case g.StockStatus

when 1 then 'Yes'

when 0 then 'No'

else ''

end as StockStatus,

isnull(g.StandardUnitPrice,a.ListPrice) as Price,

isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource

from

dbo.vw_mcProductItem a

inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @.SearchWord) as b ON a.ItemID = b.[KEY]

inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID

inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID

inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID

inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID

left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID

left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID

left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId

left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID

where

d.CustomerGroupID = @.CustomerGroupID

and f.UserID = @.UserID

and FREETEXT(BrandName,ManufacturerName,CategoryName, @.SearchWord)

Freetext takes either a column or all (*). So, your query should be changed.

e.g.

Code Snippet

and (

FREETEXT(BrandName, @.SearchWord)

or

and FREETEXT(ManufacturerName, @.SearchWord)

or

and FREETEXT(CategoryName, @.SearchWord)

)

Freaking column names

I'm dealing with a database with tables that have freaking columns.
Partial DDL:
Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
-- yeah, this column contains string value

Now, I'd like to rename all these freaking columns without special
charaters like '-', 'whitespace' etc systematically (meaning loop
through all tables and columns dynamically). It seems that the
sp_rename proc can't handle some function call or ...?
e.g.
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
Cost-Mn]','-',''),'COLUMN'

better if it can work,
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
',''),'COLUMN'

TIANickName (dadada@.rock.com) writes:
> I'm dealing with a database with tables that have freaking columns.
> Partial DDL:
> Create table [tbl IMN] ([Ave Cost-Mn] varchar(10))
> -- yeah, this column contains string value
> Now, I'd like to rename all these freaking columns without special
> charaters like '-', 'whitespace' etc systematically (meaning loop
> through all tables and columns dynamically). It seems that the
> sp_rename proc can't handle some function call or ...?
> e.g.
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave
> Cost-Mn]','-',''),'COLUMN'
> better if it can work,
> exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',replace('[Ave Cost-Mn]','-|
> ',''),'COLUMN'

Correct. In difference to most other languages, you cannot pass
expressions as parameters to stored procedures. You can only psss
constants and variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||How about this? Rather than a stored procedure, generate a script to
run against all of your columns, like so:

SELECT 'exec sp_rename ''' + Table_Name + '.'
+ Column_name + ''', ''' +
REPLACE(COLUMN_NAME, '-', '') + ''', ''COLUMN'''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%-%'

Cut and paste the results of the query into a new QA window and execute
the statements.

HTH,
Stu|||Actually, I could do something like
select @.colTemp = Replace(col,'-','')
then
exec sp_rename '[tbl-IMN].[Ave Cost-Mn]',@.colTemp,'COLUMN'

I thought about that before posting but I did not try (just don't know
why somtimes I'm so freaking lazy).

Thanks for the note though.

Don|||Interesting idea, however, I don't think it works.|||Worked in my test bed; what error did you get? Just to make sure that
I was clear, the above command will NOT execute the stored procedure;
it'll just generate a script of all the columns in all the tables in
your database with a '-' in the name, like so:

exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'

You have to cut and paste that script into a new window in query
analyzer to actually execute the changes.

You could, of course, modify the SQL statement to actually execute the
stored procedure for you; I just prefer to do it this way so I can
visually check what I'm about to execute.

Stu|||Yeah, I see. Problem resolved. Thanks though.
Stu wrote:
> Worked in my test bed; what error did you get? Just to make sure that
> I was clear, the above command will NOT execute the stored procedure;
> it'll just generate a script of all the columns in all the tables in
> your database with a '-' in the name, like so:
> exec sp_rename 'SPLAT.Check-ID', 'CheckID', 'COLUMN'
> exec sp_rename 'SPLAT.Check-ID2', 'CheckID2', 'COLUMN'
> You have to cut and paste that script into a new window in query
> analyzer to actually execute the changes.
> You could, of course, modify the SQL statement to actually execute the
> stored procedure for you; I just prefer to do it this way so I can
> visually check what I'm about to execute.
> Stu