containstable??

  • Hi again

    I've never use CONTAINSTABLE before, and I'm trying to use it but it seems that it needs a indexed table to work with

    this is the script that I'm testing

    CREATE PROCEDURE metodo2

    AS

    DECLARE my_cursor CURSOR

    FOR

    SELECT rfqKey, Family

    FROM ##table_metodo1

    WHERE rfqKey IN (SELECT rfqKey FROM ##table_metodo1

    WHERE pos=1

    GROUP BY rfqKey

    HAVING COUNT(1) > 1)

    ORDER BY rfqKey

    DECLARE @rfq int, @Family varchar(255), @keyword varchar(20)

    OPEN my_cursor

    FETCH NEXT FROM my_cursor INTO @rfq, @Family

    WHILE @@fetch_status = 0

    BEGIN

    SELECT rfdDescription, rfdRfq_LNK INTO #temp1

    FROM qstRfqDetail

    WHERE (rfdRfq_LNK = @rfq)

    DECLARE sec_cursor CURSOR

    FOR SELECT keyword FROM ModelTest.dbo.keywords WHERE Family = @Family

    OPEN sec_cursor

    FETCH NEXT FROM sec_cursor INTO @keyword

    WHILE @@fetch_status = 0

    BEGIN

    SELECT * FROM

    CONTAINSTABLE (#temp1, rfdDescription,

    'ISABOUT (@keyword)')

    FETCH NEXT FROM sec_cursor INTO @keyword

    END

    CLOSE sec_cursor

    DEALLOCATE sec_cursosr

    DROP TABLE #temp1

    FETCH NEXT FROM my_cursor INTO @rfq, @Family

    END

    CLOSE my_cursor

    DEALLOCATE my_cursosr

    GO

    the Idea it's inside the double loop use the containstable in order to obtain a rank.

    The general idea is, I have a table (Keywords) with 2 columns, one with a keyword and the other a Category that belongs that keyword, (It could be a lot of keywords for each Family)

    And I have a #temp1 table with text description, and what i want is to rank if each keyword is inside the text, (I could use charindex but i want more info)

    With this script I just want to see the output of containstable, that way I could think something to use it, but I don't really know how it works becuase it fails with this message

    Server: Msg 7601, Level 16, State 1, Procedure metodo2, Line 32

    Cannot use a CONTAINS or FREETEXT predicate on table '#temp1' because it is not full-text indexed.

    That means I have to create a Keywords table with an identity column??

    Well I'm just testing here, so if someone have a better picture of this please enlightme

  • First of all, you cannot FT-enable a #temp table, but your can FT-enable the Tempdb database and then FT-Index and run a Full Population on tempdb..temp1. This assumes that tempdb..temp1 has a single column, non-nullable unique key (usually an identity column) that is used in the creation of the FT Index. Note, the difference in how the temp table is referenced: tempdb..temp1 vs. #temp1, the former will work, the latter will not.

    Perhaps, I could help more, if I knew more about what you are trying to achieve. What is the goal of your stored proc? If you could provide an explaination of what problem you are trying to solve, we could work towards a solution from that problem description.

    Thanks,

    John

    SQL Full Text Search Blog

    http://spaces.msn.com/members/jtkane/


    John T. Kane

  • Well, hi there and thx for the replie

    I will explain what I want to do

    I have a table ##table_metodo1 with the ranking of Families for each rfqKey (id), but some of them are tied in the first place, so with the my_cursor I fetch the rfqKey and the tied Families one by one to be able to get a second rank to untie it.

    Well after open my_cursor I fetch the rows corresponding to the description of the rfqKey and I put them into a temp table #temp1.

    Then with sec_cursor I fetch keywords related to the family. The Keywords table it's a table with two columns, one with Families and the other with keywords related to the Femilies, it have no ids ( in example I put the keyword 'electr' with the Electronic, Illumination and Industrial Electricity families), so I select only the keywords for the Family fetched with the my_cursor.

    Then I want to have a sum of ranks obtained with containstable for the keyword in all the description rows. With this I'll be able to untie the families, and select a max.

    (In the last step I have to create a table with rfqKey, Family, rank) then I'll select the top 3 for each rfqKey, and returns a finall table.

    That would be everything, I hope you can help me with this.

    Meanwhile I will try to use charindex to obtain a rank, or try to read more carefully containstable in order to use it successfully

    thx

  • I begin to understand what you're looking to accomplish. Effectively, you want to "break the tie" between Rank values returned from a CONTAINSTABLE for your table Families based upon supplied keywords and categories of keywords associated with the Keywords table. Correct?

    If so, then the cursor processing you've described is not the best approach. What you're looking for is often called "best bets" or an means to influence the Rank formula to *improve* or raise the Rank value of one row over another. One approach to this problem is to use another table that contains duplicate data of rows from your table Families that you want to have a higher Rank than what would be normally returned. You can see how this is done at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_fulltextsearch.asp. However, IMHO, this method of achieving a "best bet" solution is not scalable and not very flexible, but give it a try and see if it comes close to what you are looking to achieve.

    Thanks,

    John

    SQL Full Text Search Blog

    http://spaces.msn.com/members/jtkane/


    John T. Kane

  • It would work if there is a way that i can index keyword with family, from the information in the db, but there isn't.

    The real problem is I have to give the best Family for each rfq, and I already use an algorithm that gives me a very good results, but in some cases, there are a ties with more than 3 results, and that is inaceptable. So in order to deal with that, I select the items of the rfq (description of the rfq) it could be a hammer, nail or anything ( the rfqs are quotations), and my keywords would be pencil with Office Materials, or table with Office Material, and it can't be obtained with automatization.

    Well I will try to continue with my aproximation but with charindex

    very gratefull

    Fei

  • Ok, I think I know what you're trying to do and if my assumptions are correct, it is possible to do this without using cursors, or duplicating 40% - 50% of your data in another table in order to achieve a "best bet" result, i.e. break your ties.  However, in order to be certain that I understand your problem, could you post the all the involved table structures via a "CREATE TABLE" statement with a few sample INSERT statements for each table with sample or dummy data?

    If I correctly understand what you're trying to accomplish, I may be able to provide a possible solution using MS SQL Server Full Text Search CONTAINSTABLE or FREETEXTTABLE and the influence Rank values that are returned on a per query basis.

    Thanks,

    John

    SQL Full Text Search Blog

    http://spaces.msn.com/members/jtkane/


    John T. Kane

  • grrrr I lost my post :@ ...

    again ... U_U

    My principal table

    CREATE TABLE ##table_metodo1

    (rfqKey int, rfqName varchar (255), Family varchar(255),

    Repet float, Freq float, total float, pos int )

    INSERT INTO ##table_metodo1

    VALUES (383710,'MATERIAL DE DIV. TENIENTE', 'Válvulas', 7.0447760820388794, 7.4790049420381806, 94.194028973579407, 1)

    INSERT INTO ##table_metodo1

    VALUES (383710, 'MATERIAL DE DIV. TENIENTE', 'Filtros y Elementos Filtrantes', 5.8059701323509216, 6.163840952147237, 94.194028973579407, 2)

    INSERT INTO ##table_metodo1

    VALUES (383710, 'MATERIAL DE DIV. TENIENTE', 'Electrónica e Instrumentación', 5.134328305721283, 5.4508001851809702, 94.194028973579407, 3)

    INSERT INTO ##table_metodo1

    VALUES (396660, 'REP TOMA FUERZA', 'Filtros y Elementos Filtrantes', 0.86567163467407227, 10.0, 8.6567163467407227, 1)

    INSERT INTO ##table_metodo1

    VALUES (396660, 'REP TOMA FUERZA', 'Hidráulica, Neumática y Mangueras Industriales', 0.86567163467407227, 10.0, 8.6567163467407227, 1)

    INSERT INTO ##table_metodo1

    VALUES (396660, 'REP TOMA FUERZA', 'Martillos Neumáticos e Hidráulicos', 0.86567163467407227, 10.0, 8.6567163467407227, 1)

    INSERT INTO ##table_metodo1

    VALUES (396660, 'REP TOMA FUERZA', 'Materiales Eléctricos', 0.86567163467407227, 10.0, 8.6567163467407227, 1)

    INSERT INTO ##table_metodo1

    VALUES (396660, 'REP TOMA FUERZA', 'Motores', 0.86567163467407227, 10.0, 8.6567163467407227, 1)

    And the Keyword table its obtained by bulk insert with this script and file

    create table Keywords

    (

    Family varchar(255),

    keyword varchar(20)

    )

    BULK INSERT Keywords

    FROM 'c:\keywords.csv'

    WITH (FORMATFILE = 'c:\kw.fmt' )

    file:

    Materiales Eléctricos;corriente

    Materiales Eléctricos;corriente

    Materiales Eléctricos;CIRCUIT

    Materiales Eléctricos;CLAVIJA

    Materiales Eléctricos;BOBINA

    Materiales Eléctricos;WIRE

    Materiales Eléctricos;ALAMBRE

    Materiales Eléctricos;OHMS

    Materiales Eléctricos;CABLE

    Materiales Eléctricos;WAGO

    Materiales Eléctricos;BORNE

    Materiales Eléctricos;LAMP

    Materiales Eléctricos;ENCHUFE

    Motores;RODAMIENTO

    Motores;TRUCK

    Motores;WABCO

    Motores;MOTOR

    Hidráulica, Neumática y Mangueras Industriales;HIDRA

    Hidráulica, Neumática y Mangueras Industriales;NEUM

    Hidráulica, Neumática y Mangueras Industriales;MANGUERA

    Hidráulica, Neumática y Mangueras Industriales;FLANGES

    format file:

    8.0

    2

    1 SQLCHAR 0 255 ";" 1 Family Latin1_General_CS_AS

    2 SQLCHAR 0 20 "\r\n" 2 keyword Latin1_General_CI_AS

    Well thx for the concern, and I already have a solution using charindex and the criteria

    if value returned by charindex > 0

    then is a match and sum a point to the actual family.

    This should be everything you need, and the other temp tables used by my proc in the first post are created in the same proc.

    Thx again

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply