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 )
Note: 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.