Results 1 to 3 of 3

Thread: Linking

  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Unhappy Unanswered: Linking

    i am trying to take a take the sentence stored in Input_Text in the table Input_t and compare
    it to the varray Topic_Keyword in the table Top_Key to look for matches. Then whenever a
    match is found the Key_Topic_ID and Topic_Keyword_Counter from table Top_Key are to be stored
    in the table Top_Res under the coresponding values of Topic_Result_ID for Key_Topic_ID and
    Topic_Result_Counter for Topic_Keyword_Counter. my code at the moment looks like this :

    Select Key_Topic_ID, Topic_Keyword_Counter, Topic_Keyword, Input_Text
    FROM Top_Key , Input_t
    WHERE Topic_Keyword = Input_Text; --SAME AS IN ANY OF THE NEWLY CREATED varray called
    INPUT_TEXT

    insert into Top_Res(Topic_Result_ID = Key_Topic_ID, Topic_Result_Counter =
    Topic_Keyword_Counter); --values
    --('who is rangers manager');
    -- INSERT matching Topic_Keyword, Key_Topic_ID and Topic_Keyword_Counter IN Top_Res under
    Topic_Result_ID for Key_Topic_ID AND Topic_Keyword_Counter under Topic_Result_Counter


    but i know it's way off

    all help would be greatfully received

  2. #2
    Join Date
    Feb 2004
    Posts
    16

    Re: Linking

    My table look like :

    -- 1. Drop all tables and types
    -- note the use of cascade constraints for tables that are
    -- referenced by other tables through foreign keys


    drop table Output cascade constraints;
    drop table Sel_Sub cascade constraints;
    drop table Sub_Res cascade constraints;
    drop table Sub_Key cascade constraints;
    drop table Sel_Top cascade constraints;
    drop table Top_Res cascade constraints;
    drop table Top_Key cascade constraints;
    drop table Input_t cascade constraints;

    drop type Top_Key_Type;
    drop type Sub_Key_Type;


    drop synonym ot;
    drop synonym sst;
    drop synonym srt;
    drop synonym skt;
    drop synonym stt;
    drop synonym trt;
    drop synonym tkt;
    drop synonym it;

    -- 2. create the Input_t table

    create table Input_t(
    Input_Text varchar2(200)
    );

    -- 3. create the Topic_Keywords table

    CREATE TYPE Top_Key_Type AS OBJECT
    ( option1 VARCHAR2(80),
    option2 VARCHAR2(80),
    option3 VARCHAR2(80),
    option4 VARCHAR2(80),
    option5 VARCHAR2(80),
    option6 VARCHAR2(80)
    );
    /

    create table Top_Key(
    Key_Topic_ID varchar2(50) primary key, -- Key_Topic_ID is the primary key
    Topic_Keyword Top_Key_Type,
    Topic_Keyword_Counter number
    );

    -- 4. create the Topic_Results table

    create table Top_Res(
    Topic_Result_ID varchar2(50) primary key, -- Topic_Result_ID is the primary key
    Topic_Result_Counter number
    );


    delete from Input_t;
    delete from Top_Key;
    delete from Top_Res;
    delete from Sel_Top;
    delete from Sub_Key;
    delete from Sub_Res;
    delete from Sel_Sub;
    delete from Output;

    -- 1. Input it

    insert into it(Input_Text) values
    ('who is rangers manager');

    -- 2. Input tkt

    insert into
    tkt
    values (
    't-motherwell',
    Top_Key_Type('motherwell', 'administration', 'lanark', 'butcher', 'fir' , 'fadden'),
    2
    );

    insert into
    tkt
    values (
    't-rangers',
    Top_Key_Type('ranger', 'blue', 'glasgow', 'leish', 'ibrox', 'boer'),
    2
    );

    insert into
    tkt
    values (
    't-celtic',
    Top_Key_Type('celtic', 'bhoys', 'hoops', 'larsson', 'neil','champ'),
    2
    );

    insert into
    tkt
    values (
    't-aberdeen',
    Top_Key_Type('aberdeen', 'don', 'red', 'paterson', 'Pittodrie', 'milne'),
    2
    );

    insert into
    tkt
    values (
    't-hearts',
    Top_Key_Type('heart', 'pressley', 'tynecastle', 'murray', 'levin', 'robinson'),
    2
    );

    insert into
    tkt
    values (
    't-hibernian',
    Top_Key_Type('hibernian', 'edinburgh', 'easter', 'williamson', 'conner', 'tynecastle'),
    2
    );

    insert into
    tkt
    values (
    't-partick',
    Top_Key_Type('partick', 'thistle', 'firhill', 'britton', 'whyte', 'releg'),
    2
    );

    insert into
    tkt
    values (
    't-dundee',
    Top_Key_Type('dundee', 'administration', 'duffy', 'novo', 'bankrupt', 'den'),
    2
    );

    insert into
    tkt
    values (
    't-kilmarnock',
    Top_Key_Type('kilmarnock', 'killie', 'jeffries', 'rugby', 'canero', 'swegan'),
    2
    );

    insert into
    tkt
    values (
    't-united',
    Top_Key_Type('dundee', 'united', 'arabs', 'call', 'tanadice', 'dodds'),
    2
    );

    insert into
    tkt
    values (
    't-livingstone',
    Top_Key_Type('livi', 'almondvale', 'preston', 'keane', 'makel', 'andrew'),
    2
    );

    insert into
    tkt
    values (
    't-dunfermline',
    Top_Key_Type('dunfermline', 'par', 'east', 'calderwood', 'crawford', 'brewster'),
    2
    );

    insert into
    tkt
    values (
    't-league',
    Top_Key_Type('league', 'spl', 'premier', 'top', 'division', 'twelve'),
    0
    );

    insert into
    tkt
    values (
    't-national',
    Top_Key_Type('national', 'scotland', 'berti', 'tarten', 'bagpipe', 'burn'),
    2
    );

    insert into
    tkt
    values (
    't-cups',
    Top_Key_Type('cup', 'tennent', 'ovd', 'final', 'scottish', 'cis'),

  3. #3
    Join Date
    Feb 2004
    Posts
    16

    Re: Linking

    insert into
    tkt
    values (
    't-leagueRules',
    Top_Key_Type('rule', 'law', 'disipline', 'police', 'stewards', 'conduct'),
    0
    );

    insert into
    tkt
    values (
    't-injuries',
    Top_Key_Type('injury', 'injuries', 'crock', 'cruciate', 'break', 'damadge'),
    0
    );

    insert into
    tkt
    values (
    't-formation',
    Top_Key_Type('formation', 'setup', 'position', 'sweeper', 'counter', 'line'),
    0
    );

    insert into
    tkt
    values (
    't-sponsor',
    Top_Key_Type('sponsor', 'back', 'bank', 'fund', 'advert', 'logo'),
    0
    );

    what i amtrying to do is to is to go throught my Input_Text varchar2 in the Input_t and
    compare it to the varray for Topic_Keyword in Top_Key so as the sentence is 'who is rangers
    manager' it should come back with 't-rangers' from the tkt table then i want to put
    't-rangers' which is Key_Topic_ID from the table Top_Key in Top_Res under Topic_Result_ID and
    also take the coresponding Topic_Keyword_Counter from Top_Key and put it in the Top_Res table
    under Topic_Result_Counter.

    sorry it took so long but i finished the first message then went for lunch then when i got
    back discovered only half of it had been sent.

    i hope this helps you in understanding my problem

    i have created the synonym's i just didn't show you that bit of code the problem is i don't
    know if what i am trying to do at the end of my last querie is possible in SQL but if it is i
    was wondering how you would go about it ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •