fulltext query problem

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FU_SZ_B_B_S_M_W]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FU_SZ_B_B_S_M_W]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
SELECT ContactName, CompanyName
FROM dbo.Suppliers
WHERE CONTAINS(ContactName, 'Marie')
Create function FU_SZ_B_B_S_M_W (@.idTB_JEZYK int, @.branza nvarchar(100),
@.slowo nvarchar(100), @.miasto varchar(100), @.woj int)
RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA int)
AS
BEGIN
DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
int)
INSERT @.BranzeTMP
SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
TB_FIRMA,
TB_SLOWO_BRANZA,TB_SL_KLUCZOWE
WHERE TB_BRANZA.idTB_BRANZA = TB_SLOWO_BRANZA.idTB_BRANZA
AND TB_SLOWO_BRANZA.idSL_KLUCZOWE = TB_SL_KLUCZOWE.idSL_KLUCZOWE
AND TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
AND SLOWO like N'%'+@.slowo+'%'
AND NAZWA_BRANZA = N''+LOWER(@.branza)+''
AND TB_MIEJSCOWOSC.nazwa_miejscowosc like '%'+@.miasto+'%'
AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
INSERT @.Branze
SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
FROM @.BranzeTMP
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOexactly what is the problem? Did you want the full text query to replace the
like statements in the function?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
news:O3JJueffGHA.408@.TK2MSFTNGP04.phx.gbl...
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FU_SZ_B_B_S_M_W]') and xtype in (N'FN', N'IF', N'TF'))
> drop function [dbo].[FU_SZ_B_B_S_M_W]
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> SELECT ContactName, CompanyName
> FROM dbo.Suppliers
> WHERE CONTAINS(ContactName, 'Marie')
>
>
>
> Create function FU_SZ_B_B_S_M_W (@.idTB_JEZYK int, @.branza nvarchar(100),
> @.slowo nvarchar(100), @.miasto varchar(100), @.woj int)
> RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
> int)
> AS
> BEGIN
> DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
> int)
> INSERT @.BranzeTMP
> SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
> TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
> FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
> TB_FIRMA,
> TB_SLOWO_BRANZA,TB_SL_KLUCZOWE
> WHERE TB_BRANZA.idTB_BRANZA = TB_SLOWO_BRANZA.idTB_BRANZA
> AND TB_SLOWO_BRANZA.idSL_KLUCZOWE = TB_SL_KLUCZOWE.idSL_KLUCZOWE
> AND TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
> AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
> AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
> AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
> AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
> AND SLOWO like N'%'+@.slowo+'%'
> AND NAZWA_BRANZA = N''+LOWER(@.branza)+''
> AND TB_MIEJSCOWOSC.nazwa_miejscowosc like '%'+@.miasto+'%'
> AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
> AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
> GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
> INSERT @.Branze
> SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
> FROM @.BranzeTMP
> RETURN
> END
>
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>|||Hi
Exactly what I want is to replace "like" statement with i.e. "contains"
statement of full text query.
I prepared everything (I established full text indexed, populated and so on)
and I changed function from:
CREATE function FU_SZ_B_M_W (@.idTB_JEZYK int, @.miasto varchar(100), @.woj
int)
RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA int)
AS BEGIN
DECLARE @.miasto2 nvarchar
SET @.miasto2 = '%'+@.miasto+'%'
DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
int)
INSERT @.BranzeTMP
SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
TB_FIRMA
WHERE TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
--AND (CONTAINS(TB_MIEJSCOWOSC.nazwa_miejscowosc,@.miasto2))
AND TB_MIEJSCOWOSC.nazwa_miejscowosc Like @.miasto2
GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
INSERT @.Branze
SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
FROM @.BranzeTMP
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
##########
to function with full text:
CREATE function FU_SZ_B_M_W (@.idTB_JEZYK int, @.miasto varchar(100), @.woj
int)
RETURNS @.Branze TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA int)
AS BEGIN
DECLARE @.miasto2 nvarchar
SET @.miasto2 = miasto+'*'
DECLARE @.BranzeTMP TABLE(idTB_BRANZA int,NAZWA_BRANZA nvarchar(100), SUMA
int)
INSERT @.BranzeTMP
SELECT TB_BRANZA.idTB_BRANZA as idTB_BRANZA,NAZWA_BRANZA, count(DISTINCT
TB_FIRMA_BRANZA.idTB_FIRMA) as SUMA
FROM TB_BRANZA, TB_FIRMA_BRANZA,TB_ADRES_FIRMA,TB_ADRES,
TB_MIEJSCOWOSC,
TB_FIRMA
WHERE TB_BRANZA.idTB_BRANZA = TB_FIRMA_BRANZA.idTB_BRANZA
AND TB_FIRMA.idTB_FIRMA = TB_FIRMA_BRANZA.idTB_FIRMA
AND TB_FIRMA.idTB_FIRMA = TB_ADRES_FIRMA.idTB_FIRMA
AND TB_ADRES.idTB_ADRES = TB_ADRES_FIRMA.idTB_ADRES
AND TB_MIEJSCOWOSC.idTB_MIEJSCOWOSC = TB_ADRES.idTB_MIEJSOWOSC
AND TB_MIEJSCOWOSC.idID_TB_MIEJSCOWOSC = @.woj
AND TB_BRANZA.idTB_JEZYK = @.idTB_JEZYK
AND (CONTAINS(TB_MIEJSCOWOSC.nazwa_miejscowosc,@.miasto2))
--AND TB_MIEJSCOWOSC.nazwa_miejscowosc Like @.miasto2
GROUP BY NAZWA_BRANZA,TB_BRANZA.idTB_BRANZA
INSERT @.Branze
SELECT idTB_BRANZA,NAZWA_BRANZA, SUMA
FROM @.BranzeTMP
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The clue is: AND (CONTAINS(TB_MIEJSCOWOSC.nazwa_miejscowosc,@.miasto2))
where TB_MIEJSCOWOSC.nazwa_miejscowosc is the name of a town.
I always get the statement:
Execution of a full-text operation failed. A clause of the query contained
only ignored words.
It's strange because I pass the name of a town to function. I even delete
everything from noise.eng
I'm loosing my ming....
Best Regards
Darek T.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OGLFSqkfGHA.4464@.TK2MSFTNGP04.phx.gbl...
> exactly what is the problem? Did you want the full text query to replace
> the like statements in the function?
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Dariusz Tomon" <d.tomon@.mazars.pl> wrote in message
> news:O3JJueffGHA.408@.TK2MSFTNGP04.phx.gbl...
>