Catalog search limitations

General Catalog search limitations

• Case-insensitive searches are not permitted on the CATENTDESC long description column. This is because of potential performance degradation when using the upper() function on the columns CLOB data type.
• The CATENTDESC long description column is not intended for searching. To facilitate searching, use the short description and keyword columns. The data type of these columns is VARCHAR(254), which allows for better search performance than CLOB.

Searching for inventory quantity in a non-ATP Extended Site store

In an Extended Site setup where the same catalog asset store is shared by multiple extended site stores, the catalog search performed in non-ATP stores returns incorrect search results. This behavior occurs when the search criteria includes an inventory quantity. The criteria is enabled by setting "qtyAvailable" and "qtyAvailableOperator" in CatEntrySearchListDataBean or other beans which extend CatEntrySearchListDataBean. For example, suppose you perform a search on 'qtyAvailable > 10', your search results will include some catalog entries with quantity <= 10.

This error occurs because there is no support for inventory in the default extended site model. Each extended site store has to define its own inventory for all the catalog entries it displays including the entries which belong to the catalog asset store. Hence, if a catalog entry X, which belongs to catalog asset store, has a different inventory for each extended site store, it will have a corresponding record in STOREINV view for each of the extended site store. When the search engine generates the search query, it does not include a filter condition on the STOREINV.STORE_ID column. As a result, all the records where quantity > 10 is included in the search result because the STORE_ID column is ignored.

For example, if your search criteria includes a filter on an inventory in a non-ATP store, the search uses the STOREINV view and retrieves those catalog entries present in the view using the following default query (see the filter condition highlighted in bold) :

select distinct CATENTRY.CATENTRY_ID, CATENTDESC.NAME from CATENTRY, 
CATENTDESC, CATGPENREL, STOREINV where ((((((((((upper(CATENTDESC.NAME) like '%INFOPRINT%'))  
or  ((upper(CATENTDESC.SHORTDESCRIPTION) like '%INFOPRINT%')))  
and  CATGPENREL.CATALOG_ID = 10201)  and  STOREINV.STOREQUANTITY > 12)))  
and  (((CATENTRY.BUYABLE = 1  and  CATENTRY.MARKFORDELETE <> 1  
and  CATENTDESC.PUBLISHED = 1  and  CATENTDESC.LANGUAGE_ID = -1)))  
and  ((CATENTRY.CATENTTYPE_ID = 'ProductBean')  or  
(CATENTRY.CATENTTYPE_ID = 'PackageBean')  or  (CATENTRY.CATENTTYPE_ID = 'BundleBean'))))  
and  (CATENTRY.CATENTRY_ID = (CATGPENREL.CATENTRY_ID)  and  CATENTRY.CATENTRY_ID = (CATENTDESC.CATENTRY_ID)  
and  STOREINV.CATENTRY_ID = (CATENTRY.CATENTRY_ID))) order by CATENTDESC.NAME asc 

The preceding query may not work properly in an extended site setup where multiple extended site stores extend the same catalog asset store because inventory is not supported. Hence, each extended site store should have it's own inventory for a given catalog entry so that the same catalog entry can have different quantity for different extended site stores in the STOREINV view. Also, there is a filter on STOREINV.STOREQUANTITY and a join on STOREINV.CATENTRY_ID. Suppose an extended site store A has zero inventory and an extended site store B has 10 inventory for a CatalogEntry X, if you perform a search in extended site store A on inventory > 0, you still see CatalogEntry X since there is no filter by STOREINV.STORE_ID.

To workaround this limitation, customers need to customize the Catalog search to include additional filter condition (see the filter condition highlighted in bold) :

select distinct CATENTRY.CATENTRY_ID, CATENTDESC.NAME from CATENTRY, CATENTDESC, CATGPENREL, 
STOREINV where ((((((((((upper(CATENTDESC.NAME) like '%INFOPRINT%'))  or  ((upper(CATENTDESC.SHORTDESCRIPTION) like '%INFOPRINT%')))  
and  CATGPENREL.CATALOG_ID = 10201)  and  STOREINV.STOREQUANTITY > 12)  and  STOREINV.STORE_ID = 10302))  
and  (((CATENTRY.BUYABLE = 1  and  CATENTRY.MARKFORDELETE <> 1  and  CATENTDESC.PUBLISHED = 1  and  CATENTDESC.LANGUAGE_ID = -1)))  
and  ((CATENTRY.CATENTTYPE_ID = 'ProductBean')  or  (CATENTRY.CATENTTYPE_ID = 'ItemBean')  or  (CATENTRY.CATENTTYPE_ID = 'PackageBean')  
or  (CATENTRY.CATENTTYPE_ID = 'BundleBean'))))  and  (CATENTRY.CATENTRY_ID = (CATGPENREL.CATENTRY_ID)  
and  CATENTRY.CATENTRY_ID = (CATENTDESC.CATENTRY_ID)  and  STOREINV.CATENTRY_ID = (CATENTRY.CATENTRY_ID))) order by CATENTDESC.NAME asc