Develop > Business logic layer > Work with the data service layer > Create a query

Ovveride generated parametric search SQL

There may be cases where to override the generated SQL for parametric search queries. For example, if a certain parametric search is not performing well, the 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 the 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 the 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 
   

Related concepts

Parametric search support

+

Search Tips   |   Advanced Search