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.
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
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.