1 Reply Latest reply: Jan 22, 2013 8:45 PM by Deborahcrm-Oracle RSS

    Query to display the exact match product at top or increase the nearness

    tpmuhammedaslam
      Hello All,

      I am using the iStore search and it is returning results for Part Number and Description search. If I am searching a particular Part Number , it is displaying the particular product and its related products. I need the exact matched product in the first row (topmost).
      When I checked the back-end query, It is using contains for powerful search.
      How can I display the exact match product at top or increase the nearness in search.

      Please help.

      I have seen a similar thread in the forum.
      Contains: exactly match


      Thanks in advance,
      Muhammed Aslam

      Edited by: tpmuhammedaslam on Jan 10, 2013 3:48 AM
        • 1. Re: Query to display the exact match product at top or increase the nearness
          Deborahcrm-Oracle
          Hello Muhammed Aslam,

          If fuzzy search is enabled, then cannot search by part number.

          The seeded query for iStore product search is based on the description as follows:


          Product Search (Section Search - R12)

          select * from
          (select T.*, RowNum as row_num
          from (select inv_item_id INVENTORY_ITEM_ID
          ,description DESCRIPTION
          ,0 CATEGORY_ID
          ,nearness NEARNESS
          ,concatenated_segments CONCATENATED_SEGMENTS
          ,primary_uom_code PRIMARY_UOM_CODE
          ,primary_unit_of_measure PRIMARY_UNIT_OF_MEASURE
          from (SELECT * FROM (select /*+ FIRST_ROWS leading(i) INDEX (x,IBE_SECTION_SEARCH_U1) */
          distinct i.inventory_item_id, i.description, 0
          ,x.inventory_item_id inv_item_id, score(100) nearness
          ,mtl_system_items_b_kfv.concatenated_segments,
          mtl_system_items_b_kfv.primary_uom_code,
          mtl_system_items_b_kfv.primary_unit_of_measure
          from ibe_ct_imedia_search i,ibe_section_search x
          ,mtl_system_items_b_kfv
          where contains (i.indexed_search, :1 , 100) > 0
          and i.language = userenv('LANG')
          and i.organization_id = :2
          and i.web_status='PUBLISHED'
          and x.inventory_item_id = i.inventory_item_id
          and i.inventory_item_id = mtl_system_items_b_kfv.inventory_item_id
          and i.organization_id = mtl_system_items_b_kfv.organization_id
          and x.minisite_id = :4
          and x.organization_id =:5
          order by nearness desc ) WHERE rownum < :6
          ))T
          where rownum where row_num >= :8


          Product Search (Category Search - R12)

          select * from
          (select T.*, RowNum as row_num
          from (select inventory_item_id INVENTORY_ITEM_ID
          ,description DESCRIPTION
          ,category_id CATEGORY_ID
          ,nearness NEARNESS
          ,concatenated_segments CONCATENATED_SEGMENTS
          ,primary_uom_code PRIMARY_UOM_CODE
          ,primary_unit_of_measure PRIMARY_UNIT_OF_MEASURE
          from (select i.inventory_item_id, i.description, i.category_id
          ,score(100) nearness
          ,mtl_system_items_b_kfv.concatenated_segments
          ,mtl_system_items_b_kfv.primary_uom_code
          ,mtl_system_items_b_kfv.primary_unit_of_measure
          from ibe_ct_imedia_search i,
          mtl_system_items_b_kfv
          where contains (i.indexed_search, :1 , 100) > 0
          and i.language = userenv('LANG')
          and i.web_status='PUBLISHED'
          and i.organization_id = :3
          and exists (select 1
          from ibe_dsp_section_items s,ibe_dsp_msite_sct_items b
          where s.section_item_id = b.section_item_id
          and b.mini_site_id = :4
          and s.inventory_item_id = i.inventory_item_id
          and (s.end_date_active > sysdate or s.end_date_active is null)
          and s.start_date_active < sysdate)
          and rownum < :5
          and i.inventory_item_id = mtl_system_items_b_kfv.inventory_item_id
          and i.organization_id = mtl_system_items_b_kfv.organization_id
          order by nearness desc ))T
          where rownum where row_num >= :7



          Regards,
          Debbie