"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?
Here's an example...
The table contains this data, all items are varchar
32611317
3261Non-Client
32612
3261mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz
|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
David Portas
SQL Server MVP
|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegro ups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>
|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
David Portas
SQL Server MVP
|||"Aaron [SQL Server MVP]" wrote:

> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?Here's an example...
The table contains this data, all items are varchar
3261 1317
3261 Non-Client
3261 2
3261 mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
--
David Portas
SQL Server MVP
--|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegroups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
--
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" wrote:
> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"On Error Resume Next" in SQL Server

I am executing a stored procedure that uses a cursor to do
a CONTAINS search for each record and to produce a result
set from that for output. The trouble is that if a good
old "ignored words" error occurs for one of the records
the procedure stops running. I need this to carry on
running regardless of this as the only reason an error
would occur would be due to bad user input which does not
bother me and can therefore be "ignored".
I have followed the advice in the KB article at
http://support.microsoft.com/default.aspx?scid=kb;en-
us;246800 for formatting input for CONTAINS searches but
there is always some nasty ASCII/UNICODE character that
slips through.
Is there a method to isolate the full-text search (or any
part of the procedure for that matter) and to guarantee
that my stored procedure will run to the end of the cursor?
Any help would be much appreciated.Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"Andy Wakeling" wrote:

> I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.
>|||When you are running your query in query analyzer, does it stop running? If
so, then please post the code. If it doesn't, then it is your code that is
causing it to stop. Just have your calling code ignore the errors and
continue on.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
>I am executing a stored procedure that uses a cursor to do
> a CONTAINS search for each record and to produce a result
> set from that for output. The trouble is that if a good
> old "ignored words" error occurs for one of the records
> the procedure stops running. I need this to carry on
> running regardless of this as the only reason an error
> would occur would be due to bad user input which does not
> bother me and can therefore be "ignored".
> I have followed the advice in the KB article at
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;246800 for formatting input for CONTAINS searches but
> there is always some nasty ASCII/UNICODE character that
> slips through.
> Is there a method to isolate the full-text search (or any
> part of the procedure for that matter) and to guarantee
> that my stored procedure will run to the end of the cursor?
> Any help would be much appreciated.|||Louis,
I won't post the actual code as it is a massive SP and
besides, we've tried various tests in QA as well but the
gist is as follows:
DECLARE TestCursor CURSOR FOR /*WHATEVER*/
OPEN TestCursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM TestCursor INTO @.SearchText
SET @.SearchText = FormatSearchText(@.SearchText)
/*
This is a UDF with output as per KB article as mentioned.
If anything goes wrong this returns '' as I am not
bothered if it cannot resolve input but note: This can
still output junk that can break the CONTAINS search.
*/
INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
/*
When run in QA, if this query causes an ignored-word error
the SP stops dead. I need it to carry on to the end of the
cursor.
*/
END, CLOSE, DEALLOCATE etc.
SELECT * FROM #TEMPTABLE /* Output of entire cursor */
That's pretty much what I'm trying to achieve. What do you
reckon?
Cheers
Andy

>--Original Message--
>When you are running your query in query analyzer, does
it stop running? If
>so, then please post the code. If it doesn't, then it is
your code that is
>causing it to stop. Just have your calling code ignore
the errors and
>continue on.
>--
>----
--
>Louis Davidson - drsql@.hotmail.com
>SQL Server MVP
>Compass Technology Management - www.compass.net
>Pro SQL Server 2000 Database Design -
>http://www.apress.com/book/bookDisplay.html?bID=266
>Blog - http://spaces.msn.com/members/drsql/
>Note: Please reply to the newsgroups only unless you are
interested in
>consulting services. All other replies may be ignored :)
>"Andy Wakeling" <anonymous@.discussions.microsoft.com>
wrote in message
>news:203501c50ad3$6ee202d0$a601280a@.phx.gbl...
do
result
not
any
cursor?
>
>.
>|||No idea, as I don't use full text search at all. However, if the
formatSearchText can output stuff to cause it to fail, is this text
something that would obviously make it fail? Such that you could clean it
up in the UDF? Hopefully someone else who has used full text search can see
the problem with it. Maybe posting some of the values that cause it to
fail?
Sorry I am not much help on this subject.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:21c301c50cff$5894aea0$a401280a@.phx.gbl...
> Louis,
> I won't post the actual code as it is a massive SP and
> besides, we've tried various tests in QA as well but the
> gist is as follows:
> DECLARE TestCursor CURSOR FOR /*WHATEVER*/
> OPEN TestCursor
> WHILE (1 = 1)
> BEGIN
> FETCH NEXT FROM TestCursor INTO @.SearchText
> SET @.SearchText = FormatSearchText(@.SearchText)
> /*
> This is a UDF with output as per KB article as mentioned.
> If anything goes wrong this returns '' as I am not
> bothered if it cannot resolve input but note: This can
> still output junk that can break the CONTAINS search.
> */
> INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
> WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
> /*
> When run in QA, if this query causes an ignored-word error
> the SP stops dead. I need it to carry on to the end of the
> cursor.
> */
> END, CLOSE, DEALLOCATE etc.
> SELECT * FROM #TEMPTABLE /* Output of entire cursor */
> That's pretty much what I'm trying to achieve. What do you
> reckon?
> Cheers
> Andy
>
> it stop running? If
> your code that is
> the errors and
> --
> interested in
> wrote in message
> do
> result
> not
> any
> cursor?

"Cursor-Fetch" problem:Oracle2SQL Server Migration

Dear all,

I have a procedure in Oracle that contains the following cursor:

CURSOR SCHED_TRIPS IS
SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID
FROM Dbo.SCHEDTRIPS_VIEW
WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE)
AND DISPOSITION <> 'V';
BEGIN
FOR S IN SCH_TRIPS LOOP
UPDATE dbo.SCHEDULES T
SET T.DIRTYBIT = 1
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);

UPDATE dbo.SCHEDULES T
SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE
WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE;
END LOOP;
COMMIT ;
END;

My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.

Anxiously waiting for help!Most common loop structure is:

while @.@.fetch_status = 0 begin
...
end

But you'll have to change your UPDATE statement to reference the variables that you're going to be FETCHing the values into, rather than referencing the fields from the cursor. Also, by looking at your JOINs you'll have to implement conditional UPDATE because values from the cursor will correspont to only 1 row at a time, while your current syntax suggests that the cursor now is used as a subquery which will not be possible in SQL. In other words it'll look something like this:declare @.stop_num int, @.tripid int, @.stop_type char(1), @.promised_time datetime, @.eta datetime, @.etd datetime
declare s cursor local for
select STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, ETD
from Dbo.SCHEDTRIPS_VIEW
where UNQ_ID = SESSION_ID AND convert(char(8), TRAVELDATE, 112) = convert(char(8), TDATE, 112)
open s
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
while @.@.fetch_status = 0 begin
update t
set t.STOP_NUM = @.stop_num,
t.PROMISED_TIME = @.promised_time,
t.ETA = @.eta,
t.ETD = @.etd,
t.LAST_CHANGED = current_timestamp,
t.DIRTYBIT = case when (t.STOP_NUM <> @.stop_num OR t.ETA <> @.eta) then 1 else t.DIRTYBIT end
from dbo.SCHEDULES t
where t.TRIPID = @.tripid AND t.STOP_TYPE = @.stop_type
fetch next from s into @.stop_num, @.tripid, @.stop_type, @.promised_time, @.etd, @.etd
end
deallocate s
close s|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all. I would code:

UPDATE T
SET T.DIRTYBIT = 1
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID
AND T.STOP_TYPE = S.STOP_TYPE
AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA)
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

UPDATE T
SET T.STOP_NUM = S.STOP_NUM,
T.PROMISED_TIME = S.PROMISED_TIME,
T.ETA = S.ETA,
T.ETD = S.ETD,
T.LAST_CHANGED = SYSDATE
FROM dbo.SCHEDULES T,
Dbo.SCHEDTRIPS_VIEW S
WHERE T.TRIPID = S.TRIPID AND
T.STOP_TYPE = S.STOP_TYPE;
AND UNQ_ID = SESSION_ID
AND TRUNC(TRAVELDATE) = TRUNC(TDATE)

Bill|||Thx rdjabarov for going into the intricacies of my proc and giving a detailed reply.
But this was something which I was trying to avoid. Isn't there something similar to Oracle in SQL Server. Else I will have to declare hundreds of vars bcoz this is not the only proc with this style of code.
Moreover, shouldn't Close cursor statement come before deallocation?

Plz do suggest something to overcome my dilemma.

Thx again|||If you want to mimic the PL/SQL cursor style of updates in TSQL, I'm afraid there are no shortcuts.

As you'll be aware, the widespread use of cursors in ORACLE is unavoidable - that's just how you do things like updating one table from another. The particular syntax of the cursor loop in your example is neat PL/SQL shorthand to make cursor loops easier and quicker to code.

There is no equivalent to this shorthand in TSQL. You just have to do it the long way :(

In TSQL (in both MSSQL and Sybase) the use of cursors is widely discouraged, where avoidable. There is a significant overhead in using them that simply isn't there in ORACLE.

I don't know if this might be of some use to you...

http://www.swissql.com/products/oracle-to-sqlserver/index.html

Bill|||Actually the overhead associated with cursors also exists in Horacle. It's just the latter is usually run on monsterous hardware that can handle sloppy coding and poor design. SQL Server is running in prod environment on machines that are several times (sometimes a dozen or more) cheaper, and every intelligent attempt to optimize a process brings a reward in improved performance.|||But In this sort of case, native TSQL programmers probably wouldm't use a cursor at all...TSQL programmers would also rewrite it into 1 update and convert the Horacle style into ANSI ;)

I just tried to retain the structure as it was presented in the post, that also included the use of cursor.|||Thx guys for the tips,

rdjabarov, why "Horacle"?

thompbil, I have already used the link that u kindly pointed out. Didn't find the results satisfactory. Thx all the same. Another thing, besides the marginal loss in performance by using Cursors, what other overheads can I expect? Moreover, what cud be a substitute for cursors, if the overheads are significant?

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)|||Thx guys for the tips,

Accepted that SQL Server is user friendly, but I think it is miles behind in "usefulness" as compared to Oracle. My original post is a case inpoint. Just imagine the lengths that I will have to go to achieve what has been accomplished so simply in Oracle.
Date functions of Oracle is another feather in Oracle's cap if we put these 2 RDBMSs head-to-head.

So, whatsay? (Is it a pandora's box I am opening here or what?)

They are just different. SQL Server does some things better than ORACLE. ORACLE does some things better than SQL Server.
You could say that the "UPDATE...FROM..." construct (as in my original reply) is even neater than the PL/SQL cursor update example you originally cited. I think so...but that's just an opinion.|||Man, just wait till Yukon comes out, - talking about Horacle...|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?|||Yukon! Horacle! Whoa.. What? Who? When?

Duhh...?Yukon is the project name for the next version of SQL Server (either 9.0 or SQL 2005, depending on your point of view).

Horacle is an often used rdjabarovism for Oracle.

-PatP

"CONTAINS" ignores "fahrenheit"?

I've posted this before in microsoft.public.sqlserver.programming, and
someone suggested me to post to this newsgroup.
Anyway, I have articles table like this:
tblArticle
article_id
article_title
article_text
article_title and article_text are part of full-catalog.
And among those article records there is one record like this:
article_id: 999
article_title: "Michael Moore..."
article_text: "...Fahrenheit 9/11..."
When I do search like this:
SELECT *
FROM tblArticle
WHERE CONTAINS(article_text, ' "*Fahrenheit 9/11*" ')
it returned me no result.
However, if I do search using LIKE:
SELECT *
FROM tblArticle
WHERE article_text LIKE '%Fahrenheit 9/11%'
it returned me that article_id: 999
More interesting is if I run this SQL Statement:
SELECT *
FROM tblArticle
WHERE CONTAINS(article_text, ' "*Fahrenheit 9/11*" ')
it returned me records that contains "9/11" but not "Fahrenheit 9/11",
for example:
it returns -> article_text: ... in 9/11 event...
it doesn't return -> article_text: ... Michael Moore who wrote Fahrenheit
9/11 ...
At first, I thought probably full-text catalog were not populated.
So, I did repopulate the full-text catalog, yet it still didn't work.
Also, I thought "Fahrenheit" is part of "noise" words, but it's not listed
in the noise.enu.
Is there any way to resolve this "CONTAINS" issue?
Or is this SQL Server bug?
Thanks in advance,
Danny
Correction:

> More interesting is if I run this SQL Statement:
> SELECT *
> FROM tblArticle
> WHERE CONTAINS(article_text, ' "*9/11*" ')
> it returned me records that contains "9/11" but not "Fahrenheit 9/11",
> for example:
> it returns -> article_text: ... in 9/11 event...
> it doesn't return -> article_text: ... Michael Moore who wrote Fahrenheit
> 9/11 ...
>
|||Hi Danny,
Hmm... that someone would be me? <G>
Ok, you need to provide some additional info, specifically, run and post the
full output of the following SQL script:
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- Note, you may need to set advance options on
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help tblArticle
go
Depending upon the language (the FULLTEXT_LANGUAGE column from
sp_help_fulltext_columns) of the wordbreaker you are using, have you removed
all single digits from the noise.<language> (noise.enu = US_English) file
under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
then run a Full Population. Note, you will need to stop the "Microsoft
Search" service first in order to save the changes to the noise.* files.
Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
always ignored and therefore adds no value to your query. SQL FTS support
only "word prefix" wildcard searches with the asterisk and not "word
suffix", for example ' "*og" ' would find dog and log, but these "words"
are not related. However, using a "word prefix" search such as ' "9/11*" '
returns rows that contain "9/11", or ' "fish*" ' will return "fish",
"fishes" or "fishing" as these words are inflectionally related...
Regards,
John
"Danny" <daniel_c@.NOSPAMmyrealbox.com> wrote in message
news:uoE953peEHA.2440@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Correction:
Fahrenheit
>
|||John, I am confused by this sentance:
"Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
always ignored and therefore adds no value to your query. SQL FTS support
only "word prefix" wildcard searches with the asterisk and not "word
suffix", for example ' "*og" ' would find dog and log, but these "words"
are not related."
In the first part you seem to be saying that the * in the prefix is always
ingored - ie ""Additionally, the preceding asterisk "*" in your query '
"*9/11*" ' is always ignored and therefore adds no value to your query."
and then you go on to say "SQL FTS support only "word prefix" wildcard
searches with the asterisk and not "word suffix", for example ' "*og" '
would find dog and log, but these "words" are not related."
Prefix goes in front, suffix goes behind. Your example of *og matching with
dog and log does not work.
Don't you mean to say "SQL FTS support only "word suffix" wildcard searches
with the asterisk and not "word prefix", for example ' "wild*" ' would find
wildcard and wild, but these "words" are not related."?
Then you go on to say "However, using a "word prefix" search such as '
"9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
"fish", "fishes" or "fishing" as these words are inflectionally related..."
I think you mean to say "However, using a "word suffix" search such as '
"9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
"fish", "fishes" or "fishing" as these words are inflectionally related...""
And further more the wild card operator does not do stemming, it simply
returns hits to words that start with the letters in front of the *. You are
thinking on the Inflectional operator. To get an idea of what I am talking
add the words mouse to one row and mice to another
Then do this search:
select * from tablename where contains(*,'FormsOF(INFLECTIONAL, Mouse)')
you will get hits to mouse and mice as mouse and mice are inflectionally
related.
as opposed to select * from tablename where contains(*,'mi*')
which will only return hits to mice. So your statement "fish*" ' will
return "fish", "fishes" or "fishing" as these words are inflectionally
related..." seems to be incorrect, or will only hold true if the
inflectionally related terms have the same stems, like with fish, fishes,
and fishing, but not for many English words which do not have the same
stems, like mouse, mice/tooth, teeth/wake,woke/fight, fought/wear,
wore/teach, taught/win, won/sit, sat/write, wrote/take, took/sleep,
slept/run, ran/tell, told/hold, held, off the top of my head .
Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
catalog had not completely built? After you removed 9 and 1 from your noise
word list, did you rebuild your catalog?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uFZGJPreEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi Danny,
> Hmm... that someone would be me? <G>
> Ok, you need to provide some additional info, specifically, run and post
the
> full output of the following SQL script:
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> -- Note, you may need to set advance options on
> sp_configure 'default full-text language'
> EXEC sp_help_fulltext_catalogs
> EXEC sp_help_fulltext_tables
> EXEC sp_help_fulltext_columns
> EXEC sp_help tblArticle
> go
> Depending upon the language (the FULLTEXT_LANGUAGE column from
> sp_help_fulltext_columns) of the wordbreaker you are using, have you
removed
> all single digits from the noise.<language> (noise.enu = US_English) file
> under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
> then run a Full Population. Note, you will need to stop the "Microsoft
> Search" service first in order to save the changes to the noise.* files.
> Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related. However, using a "word prefix" search such as ' "9/11*"
'
> returns rows that contain "9/11", or ' "fish*" ' will return "fish",
> "fishes" or "fishing" as these words are inflectionally related...
> Regards,
> John
>
>
> "Danny" <daniel_c@.NOSPAMmyrealbox.com> wrote in message
> news:uoE953peEHA.2440@.tk2msftngp13.phx.gbl...
> Fahrenheit
>
|||Hilary,
All I was trying to explain (late at night my time ;-0) was that the
preceding asterisk is ignored in SQL FTS as seems to be a consistent problem
for many people understanding FTS relative to T-SQL LIKE. We still need the
OS and SQL configuration info from Danny. If you want we can discuss this
off-line...
Danny, could you provide your server's configuration info and table info via
the following SQL script and post the full output?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
-- Note, you may need to set advance options on
sp_configure 'default full-text language'
EXEC sp_help_fulltext_catalogs
EXEC sp_help_fulltext_tables
EXEC sp_help_fulltext_columns
EXEC sp_help tblArticle
go
Thanks,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> John, I am confused by this sentance:
> "Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related."
> In the first part you seem to be saying that the * in the prefix is always
> ingored - ie ""Additionally, the preceding asterisk "*" in your query '
> "*9/11*" ' is always ignored and therefore adds no value to your query."
> and then you go on to say "SQL FTS support only "word prefix" wildcard
> searches with the asterisk and not "word suffix", for example ' "*og" '
> would find dog and log, but these "words" are not related."
> Prefix goes in front, suffix goes behind. Your example of *og matching
with
> dog and log does not work.
> Don't you mean to say "SQL FTS support only "word suffix" wildcard
searches
> with the asterisk and not "word prefix", for example ' "wild*" ' would
find
> wildcard and wild, but these "words" are not related."?
> Then you go on to say "However, using a "word prefix" search such as '
> "9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
> "fish", "fishes" or "fishing" as these words are inflectionally
related..."
> I think you mean to say "However, using a "word suffix" search such as '
> "9/11*" ' returns rows that contain "9/11", or ' "fish*" ' will return
> "fish", "fishes" or "fishing" as these words are inflectionally
related...""
> And further more the wild card operator does not do stemming, it simply
> returns hits to words that start with the letters in front of the *. You
are
> thinking on the Inflectional operator. To get an idea of what I am talking
> add the words mouse to one row and mice to another
> Then do this search:
> select * from tablename where contains(*,'FormsOF(INFLECTIONAL, Mouse)')
> you will get hits to mouse and mice as mouse and mice are inflectionally
> related.
> as opposed to select * from tablename where contains(*,'mi*')
> which will only return hits to mice. So your statement "fish*" ' will
> return "fish", "fishes" or "fishing" as these words are inflectionally
> related..." seems to be incorrect, or will only hold true if the
> inflectionally related terms have the same stems, like with fish, fishes,
> and fishing, but not for many English words which do not have the same
> stems, like mouse, mice/tooth, teeth/wake,woke/fight, fought/wear,
> wore/teach, taught/win, won/sit, sat/write, wrote/take, took/sleep,
> slept/run, ran/tell, told/hold, held, off the top of my head .
> Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
> catalog had not completely built? After you removed 9 and 1 from your
noise[vbcol=seagreen]
> word list, did you rebuild your catalog?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uFZGJPreEHA.2804@.TK2MSFTNGP11.phx.gbl...
> the
> removed
file[vbcol=seagreen]
support[vbcol=seagreen]
"words"[vbcol=seagreen]
"9/11*"[vbcol=seagreen]
> '
9/11",
>
|||> Hi Danny,
> Hmm... that someone would be me? <G>
* Yes, it would be you John

> Ok, you need to provide some additional info, specifically, run and post the
> full output of the following SQL script:
> use <your_database_name_here>
> go
> SELECT @.@.language
Returned:
us_english

> SELECT @.@.version
Returned:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

> -- Note, you may need to set advance options on
> sp_configure 'default full-text language'
Returned:
name | minimum | maximum | config_value | run_value
--+--+--+--+--
default full-text language| 0 |2147483647 | 1033 | 1033
--+--+--+--+--

> EXEC sp_help_fulltext_catalogs
Returned:
ftcatid | Name | Path | Status | Number_Of_Full_Text_Tables
--+--+--+--+--
6 | FT_CSPDB | d:\SQL\MSSQL\FTDATA | 0 | 10
--+--+--+--+--

> EXEC sp_help_fulltext_tables
Returned several records:
Table_Owner | Table_Name | FullText_Key_Index_Name | FullText_Key_ColID | FullText_Index_Active | FullText_Catalog_Name
--+--+--+--+--+--
...
dbo | tblArticle | PK_tblArticle | 1 | 1 | FT_CSPDB
...

> EXEC sp_help_fulltext_columns
Returned many records:
Table_Owner | Table_ID | Table_Name | FullText_Column_Name | FullText_ColID | FullText_Blobtp_ColName | FullText_Bloptp_ColID | FullText_Language
--+--+--+--+--+--+--+--
...
dbo |1721877301| tblArticle | title | 4 | NULL | NULL | 1033
dbo |1721877301| tblArticle | description | 7 | NULL | NULL | 1033
...

> EXEC sp_help tblArticle
This returned many results. Any specific data you want to know John?

> Depending upon the language (the FULLTEXT_LANGUAGE column from
> sp_help_fulltext_columns) of the wordbreaker you are using, have you removed
> all single digits from the noise.<language> (noise.enu = US_English) file
> under the folder: \FTDATA\SQLServer\Config ? If not, then you should and
> then run a Full Population. Note, you will need to stop the "Microsoft
> Search" service first in order to save the changes to the noise.* files.
* Yes, I did

> Additionally, the preceding asterisk "*" in your query ' "*9/11*" ' is
> always ignored and therefore adds no value to your query. SQL FTS support
> only "word prefix" wildcard searches with the asterisk and not "word
> suffix", for example ' "*og" ' would find dog and log, but these "words"
> are not related. However, using a "word prefix" search such as ' "9/11*" '
> returns rows that contain "9/11", or ' "fish*" ' will return "fish",
> "fishes" or "fishing" as these words are inflectionally related...
So, logically if I use CONTAINS(description, ' "9/11" '), I should get record of "Fahrenheit 9/11" right?
(Since it contains 9/11). The fact is, I don't get that record.
Any more ideas John?
Thanks a lot for your help so far.
|||"John Kane" <jt-kane@.comcast.net> wrote in message
news:OvFUl7veEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Yes, I have an idea as to why "Fahrenheit 9/11" is not returned as you are
> using SQL 2000 on Windows 2000 (Win2K), there is a bug relative to
searching
> for words that may have punctuation characters "touching" or in contact
with
> the search word or phrase. The workaround for this bug is to use the
Neutral
> "Language for Word Breaker" on your FT-enable column and then run a Full
> Population. Note, if you change the language for word breaker, be sure to
> remove single numbers from noise.dat (Neutral noise word file) prior to
> running the Full Population.
* Pardon my lack of understanding, could you elaborate more detail on how to
do this John?

> Could you provide the exact content from the FT-enable column where
> "Fahrenheit 9/11" (including any html tags, if present) as well as the
rows
> that contain only "9/11" ?
* The exact content of "Fahrenheit 9/11" is:
Filmmaker Michael Moore is attacking President George W. Bush and the war in
Iraq with <EM>Fahrenheit 9/11</EM>.
* The exact content of "9/11" is:
The 9/11 crisis has provided a dramatic opportunity for manifesting&nbsp;the
gradual strategic shift in the country's domestic and foreign policy
priorities.
|||"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> Daniel, 9/11 as a search phrase works fine for me. Is is possibly your
> catalog had not completely built? After you removed 9 and 1 from your
noise
> word list, did you rebuild your catalog?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
Thanks Hilary for your response.
First of all, my noise word list is completely blank (I have removed them
all).
Then I rebuilt my catalog, and restarted Microsoft Search agent.
Also, if I search 9/11, it returns all records contain 9/11, but NOT records
contain "Fahrenheit 9/11".
It's odd, and I can't figure it out.
- Danny
|||what happens if you remove the <EM>, </EM> tags?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DC" <dc@.office> wrote in message
news:%23OzB5GweEHA.140@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OvFUl7veEHA.3792@.TK2MSFTNGP09.phx.gbl...
are[vbcol=seagreen]
> searching
> with
> Neutral
to
> * Pardon my lack of understanding, could you elaborate more detail on how
to
> do this John?
> rows
> * The exact content of "Fahrenheit 9/11" is:
> Filmmaker Michael Moore is attacking President George W. Bush and the war
in
> Iraq with <EM>Fahrenheit 9/11</EM>.
> * The exact content of "9/11" is:
> The 9/11 crisis has provided a dramatic opportunity for
manifesting&nbsp;the
> gradual strategic shift in the country's domestic and foreign policy
> priorities.
>
|||Stop mssearch. Place a single blank space in your noise word list. Restart
MSSearch and rebuild your catalog.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"DC" <dc@.office> wrote in message
news:OnHHDJweEHA.1652@.TK2MSFTNGP09.phx.gbl...
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:ONGxDZueEHA.236@.tk2msftngp13.phx.gbl...
> noise
> Thanks Hilary for your response.
> First of all, my noise word list is completely blank (I have removed them
> all).
> Then I rebuilt my catalog, and restarted Microsoft Search agent.
> Also, if I search 9/11, it returns all records contain 9/11, but NOT
records
> contain "Fahrenheit 9/11".
> It's odd, and I can't figure it out.
> - Danny
>

"Balance Carried Forward" / "Balance Brought Forward"

I have a statement report. There is a table which contains the statement
line details. The printout of this may last several pages. Assume it lasts n
pages.
The bottom of page1 should show the total debits and total credits for page
1 only.
The bottom of page 2 should show the total debits and credits for page 1 and
2 only e.t.c.
I found I cant add Globals!PageNumber as a group to the table.
Neither am I allowed to put the data field in the page footer.
I thought RunningValue function might do it, but this always prints the
grand total for all pages.
Help !
Thanks
Chris BrooksbankPagination and aggregation (I suspect) are in unrelated parts of the RS
code..(I think all of the aggregation stuff is done prior to pagination).
There are no page totals kinds of functions that I am aware of..
Probably the best you can do is to create small groupings, so that a group
will never be larger than a page and total at the group level...
Unless one of the dev guys has a suggestion.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
news:OR2sIiBZEHA.2408@.tk2msftngp13.phx.gbl...
> I have a statement report. There is a table which contains the statement
> line details. The printout of this may last several pages. Assume it lasts
n
> pages.
> The bottom of page1 should show the total debits and total credits for
page
> 1 only.
> The bottom of page 2 should show the total debits and credits for page 1
and
> 2 only e.t.c.
> I found I cant add Globals!PageNumber as a group to the table.
> Neither am I allowed to put the data field in the page footer.
> I thought RunningValue function might do it, but this always prints the
> grand total for all pages.
> Help !
> Thanks
> Chris Brooksbank
>