Deprecated feature

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