Forum Stats

  • 3,853,567 Users
  • 2,264,240 Discussions
  • 7,905,393 Comments

Discussions

Building search 2 different table

Newbie_apex07
Newbie_apex07 Member Posts: 361 Blue Ribbon

Hello All gurus-

I am using Jaro_winkler_Similarity and edit_distance to fetch my search results.

I am running into a snag where it only returns the data if it begin with that word but if its in the middle or at the end it does not recognize it.



SELECT
      vcat_id
    , name
    , nationality_country
    , sc
    , sc1
    , CASE
              WHEN :p6_nationality_country IS NULL
                  OR nationality_country IS NULL THEN
                    sc
              ELSE
                    round ((sc * 2 + sc1) / 2)
        END score
  FROM
      (
            SELECT
                  entities.entity_id                                                                                                       vcat_id
                , entities.entity_name                                                                                                     name
                , 'Entity'                                                                                                                 "Type"
                , entities.country                                                                                                         nationality_country
                , utl_match.jaro_winkler_similarity (lower (entities.entity_name), regexp_replace (lower (:p6_search), '[[:space:]]',     '')) sc
                , utl_match.edit_distance (:p6_nationality_country, entities.country)                                                      sc1
              FROM
                  entities
            UNION ALL
            SELECT
                  individuals.individuals_id                                                                AS vcat_id
                , individuals.first_name
                    || ' '
                    || nvl2 (individuals.middle_name, individuals.middle_name || ' ', '')
                    || individuals.last_name
                    || nvl2 (individuals.second_last_name, ' '
                                                           || individuals.second_last_name
                                                           || ' ', '')                                                                               name
                , 'Individuals'                                                                             "Type"
                , individuals.nationality                                                                   nationality_country
                , utl_match.jaro_winkler_similarity (lower (individuals.first_name
                                                              || individuals.middle_name
                                                              || individuals.last_name
                                                              || individuals.second_last_name), regexp_replace (lower (:p6_search), '[[:space:]]',
                                                              '')) sc
                , utl_match.edit_distance ((:p6_nationality_country), individuals.nationality)              sc1
              FROM
                  individuals
      )
 WHERE
            CASE
                  WHEN :p6_nationality_country IS NULL
                      OR nationality_country IS NULL THEN
                        sc
                  ELSE
                        round ((sc * 2 + sc1) / 2)
            END >= 75
         AND instr (':'
                    || nvl (:p6_nationality_country, nationality_country)
                    || ':', ':'
                            || nationality_country
                            || ':') > 0
 ORDER BY
      score DESC;

sample data

Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (13429,'Nova Scotia Inv. Ltda',null,89,-1,89,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (23091,'Nova Scotia Inversiones LTDA',null,87,-1,87,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (23386,'Nova Leasing',null,82,-1,82,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (126810,'Nova Cidade De deus','BR',82,-1,82,'Individuals');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (13267,'NOA','RU',81,-1,81,'Entity');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (120373,'Nobuaki Sato','JP',80,-1,80,'Individuals');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (42662,'Novica Radovic','ME',80,-1,80,'Individuals');
Insert into EXPORT_TABLE (VCAT_ID,NAME,NATIONALITY_COUNTRY,SC,SC1,SCORE,"Type") values (20092,'Bank of Nova Scotia','CA',81,-1,81,'Entity');


If Search on Nova Scotia, my query doesn't return the row Bank of Nova scotia.

Kindly Suggest.

Answers

  • Newbie_apex07
    Newbie_apex07 Member Posts: 361 Blue Ribbon
    edited Jan 19, 2022 4:38PM


    Looking for suggestion and some guidance.


    Can use a view to create and then common index on NAME column ( which will comprise of columns from 2 table)


    for example this way

    create table queries (
       query_id   number,
       query_string varchar2(80)
      );
    
      insert into queries values (1, 'oracle');
      insert into queries values (2, 'larry or ellison');
      insert into queries values (3, 'oracle and text');
      insert into queries values (4, 'market share');
    
      create index queryx on queries(query_string)
       indextype is ctxsys.ctxrule;
    
      select * from queries
       where matches(query_string, 
              'Oracle announced that its market share in databases 
              increased over the last year.')>0
    

    Please suggest.

  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond

    Hi @Roger-DFW , @BluShadow

    sorry for tagging.


    Don't do that, this is a public forum and people have their own jobs to do; they're not sitting around waiting to be called upon to help anyone who wants it; it's very rude to tag people who aren't in your discussion, so only tag people if they're already part of your discussion.

  • Newbie_apex07
    Newbie_apex07 Member Posts: 361 Blue Ribbon

    I solved this using NDATA .

    Here is my solution for reference.

    DROP VIEW vcat_search;
    
    
    CREATE MATERIALIZED VIEW vcat_search
    NOLOGGING CACHE
          BUILD IMMEDIATE
    AS
          SELECT
                vcat_id
              , name
              , "Type"
              
            FROM
                (
                      SELECT
                            entities.entity_id    AS vcat_id
                          , entities.entity_name  name
                          , 'Entity'              "Type"
                          
                        FROM
                            entities
                      UNION ALL
                      SELECT
                            individuals.individuals_id              AS vcat_id
                          , individuals.first_name
                              || ' '
                              || nvl2 (individuals.middle_name, individuals.middle_name || ' ', '')
                              || individuals.last_name
                              || nvl2 (individuals.second_last_name, ' '
                                                                     || individuals.second_last_name
                                                                     || ' ', ''                  name
                          , 'Individuals'                           "Type"
                          
                        FROM
                            individuals
                );
    
    BEGIN
          ctx_ddl.create_preference ('VCAT_search_lexer', 'BASIC_LEXER');
          ctx_ddl.set_attribute ('VCAT_search_lexer', 'SKIPJOINS', '.+/\*$#&!`~=_|?,-''');
          ctx_ddl.set_attribute ('VCAT_search_lexer', 'WHITESPACE', '@');
    END;
    /
    
    
    BEGIN
          ctx_ddl.create_preference ('VCAT_search_mcds', 'MULTI_COLUMN_DATASTORE');
          ctx_ddl.set_attribute ('VCAT_search_mcds', 'COLUMNS', 'name, REPLACE (REPLACE (name, ''  '', ''''), '' '', '''') no_spaces');
    END;
    /
    
    
    BEGIN
          ctx_ddl.create_section_group ('namegroup', 'BASIC_SECTION_GROUP');
          ctx_ddl.add_ndata_section ('namegroup', 'name', 'name');
          ctx_ddl.add_ndata_section ('namegroup', 'no_spaces', 'no_spaces');
    END;
    /
          CREATE INDEX search_index ON
                vcat_search (  name  )
                      INDEXTYPE IS ctxsys.context PARALLEL
                      PARAMETERS ('LEXER        VCAT_search_lexer
                                                 sync        (on commit)
                                                 STOPLIST    CTXSYS.EMPTY_STOPLIST
                                                 DATASTORE    VCAT_search_mcds
                                                 SECTION GROUP    namegroup');
    /
    
    
    SELECT token_text FROM dr$search_index$i;
    
    
    SELECT
          name , "type" t
        , score (1) score
      FROM
          vcat_search
     WHERE
          contains (name, 'NDATA (name, James) OR NDATA (no_spaces, James)', 1) > 0
     ORDER BY
          score (1) desc;
          
    SELECT
          vcat_search.name
    
    
        , score (1) score
        , vcat_search."Type"
      FROM
          vcat_search
     WHERE
          contains (vcat_search.name, 'NDATA (name, '|| :p ||') OR NDATA (no_spaces, '|| :p||')', 1) > 0
     ORDER BY
          score DESC