Overriding generated parametric search SQL

There may be cases where you want to override the generated SQL for parametric search queries. For example, if a certain parametric search is not performing well, your database administrator may suggest another way to write the SQL. In this case, you can completely override the SQL used for the parametric search query.

Procedure

  1. If you haven't created one yet, create a custom query template file:
    1. Create a WC\config\com.ibm.commerce.servicemodule-ext folder, if one does not already exist.
    2. Right-click the WC\config\com.ibm.commerce.servicemodule-ext folder. You must create the folder in the -ext directory. Do not modify WebSphere Commerce query templates directly.
    3. Click New > Other > Simple > File > Next
    4. Name the custom query template file, using some unique string (your company name) to differentiate it, for example: wc-query-MyCompanyCatalogEntry-get.tpl.
    5. Click Finish.
  2. Define a new XPATH_TO_SQL_STATEMENT block in your custom template file. The name of the query should be the XPath key, except with the search parameters specified explicitly.
    For example, if our XPath expression was:
    /CatalogEntry[search(contains(Description/ShortDescription, "Polo shirt")
and starts-with(CatalogEntryIdentifier/ExternalIdentifier/PartNumber, "FU01"))]
    Typically, the XPath key name is:
    name=/CatalogEntry[search()]

    However, to indicate that we want to override the generated SQL, explicitly include the search parameters, and override the SQL in your custom template file as shown in the following sample query:

    BEGIN_XPATH_TO_SQL_STATEMENT 
name=/CatalogEntry[search(contains(Description/ShortDescription,) and starts-with(CatalogEntryIdentifier/ExternalIdentifier/PartNumber,))] 

base_table=CATENTRY 
sql= 
	SELECT CATENTRY.CATENTRY_ID 
	FROM 
		CATENTRY, CATENTDESC IBM_1 
	WHERE 
		CATENTRY.MARKFORDELETE = 0 AND CATENTRY.PARTNUMBER LIKE '?CatalogEntryIdentifier/ExternalIdentifier/PartNumber?%' AND 
			(CATENTRY.CATENTRY_ID= IBM_1.CATENTRY_ID AND 
			IBM_1.SHORTDESCRIPTION LIKE '%?Description/ShortDescription?%') 
		ORDER BY 
			CATENTRY.CATENTRY_ID 
END_XPATH_TO_SQL_STATEMENT