This discussion is archived
1 Reply Latest reply: Jan 22, 2013 6:45 PM by DeborahCRM-Oracle RSS

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

tpmuhammedaslam Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points