Standard price search SQL statement

The SQL statement for the standard price search is stored in com.ibm.commerce.search.base.SearchQueryResources.properties.

You can customize this query provided you follow these rules:

• The query must be named CATENTSTRDPRC.

• The query must return CATENTRY_ID, CURRENCY, and PRICE.

• The query must be stored in the com.ibm.commerce.search.base.SearchQueryResources.properties file.

The following SQL statements are those found in com.ibm.commerce.search.base.SearchQueryResources.properties. The parts of the query in bold are those that cannot be changed when customizing the query.

(DB2)

STD_PRICE_QUERY_DB2= (SELECT OFFER.
CATENTRY_ID, OFFERPRICE.
CURRENCY, MIN(OFFERPRICE.PRICE) AS
 PRICE 
FROM OFFER, OFFERPRICE, TRADEPOSCN, 
        (SELECT CATENTRY_ID, MAX(PRECEDENCE) AS MAXPREC 
        FROM OFFER 
        GROUP BY CATENTRY_ID) AS OFFERPREC 
WHERE (OFFER.MAXIMUMQUANTITY IS NULL OR OFFER.MAXIMUMQUANTITY
>=1) 
AND (OFFER.MINIMUMQUANTITY IS NULL OR OFFER.MINIMUMQUANTITY <=1)

AND (OFFER.STARTDATE <= CURRENT_TIMESTAMP OR OFFER.STARTDATE IS
NULL) 
AND (OFFER.ENDDATE > CURRENT_TIMESTAMP OR OFFER.ENDDATE IS NULL)

AND OFFER.PUBLISHED = 1 
AND OFFERPRICE.OFFER_ID = OFFER.OFFER_ID 
AND OFFER.TRADEPOSCN_ID = TRADEPOSCN.TRADEPOSCN_ID 
AND TRADEPOSCN.TYPE = 'S' 
AND OFFER.CATENTRY_ID = OFFERPREC.CATENTRY_ID 
AND OFFER.PRECEDENCE = OFFERPREC.MAXPREC 
GROUP BY OFFER.CATENTRY_ID, OFFERPRICE.CURRENCY) 
AS CATENTSTDPRC

(Oracle)

STD_PRICE_QUERY_ORACLE=WITH OFFERPREC AS 
        (SELECT CATENTRY_ID, MAX(PRECEDENCE) AS MAXPREC 
        FROM OFFER 
        GROUP BY CATENTRY_ID), 
        
CATENTSTDPRC AS
        (SELECT OFFER.CATENTRY_ID AS 
CATENTRY_ID, OFFERPRICE.CURRENCY AS 
CURRENCY, MIN(OFFERPRICE.PRICE) AS 
PRICE 
        FROM OFFER, OFFERPRICE, TRADEPOSCN, OFFERPREC 
        WHERE (OFFER.MAXIMUMQUANTITY IS NULL OR
OFFER.MAXIMUMQUANTITY >=1) 
        AND (OFFER.MINIMUMQUANTITY IS NULL OR OFFER.MINIMUMQUANTITY
<=1) 
        AND (OFFER.STARTDATE <= CURRENT_TIMESTAMP OR
OFFER.STARTDATE IS NULL) 
        AND (OFFER.ENDDATE > CURRENT_TIMESTAMP OR OFFER.ENDDATE
IS NULL) 
        AND OFFER.PUBLISHED = 1 
        AND OFFERPRICE.OFFER_ID = OFFER.OFFER_ID 
        AND OFFER.TRADEPOSCN_ID = TRADEPOSCN.TRADEPOSCN_ID 
        AND TRADEPOSCN.TYPE = 'S' 
        AND OFFER.CATENTRY_ID = OFFERPREC.CATENTRY_ID 
        AND OFFER.PRECEDENCE = OFFERPREC.MAXPREC 
        GROUP BY OFFER.CATENTRY_ID, OFFERPRICE.CURRENCY )

During development, it is useful to enable tracing on the component WC_SEARCH. By looking at the trace log, you can see the generated SQL.

Related concepts

Catalog search methods
Simple catalog search
Advanced catalog search
Catalog search features

Related tasks

Add catalog search to your site

Related reference

Catalog search limitations