Configuring the Data Import Handler mapping

In this lesson, you configure the Data Import Handler mapping to extract data from the relational table. You configure the mapping for data extraction with predefined SQL query lines that extract HCL Commerce data.

To extend the extraction scope, this lesson appends the columns and tables for cost price to your search queries. The cost price itself is not indexed because you do not directly use it during this tutorial. You define the query to calculate the profit margin that is based on offer price and cost price. You then map the column to index field in the configuration file.

Note: During this task, you add fields for your master catalog. As an example, this tutorial uses a master catalog with an ID of "10001" and uses the locale "en_US". Change the values to reflect the values for your environment. If you want to add the fields for more catalogs or languages, you must repeat the following process for those catalogs and languages.

About this task

Procedure

1. In a File Manager utility, go to the workspace_dir\WC\xml\search\dataImport\v3\dbtype2 directory.

2. Open the file wc-dataimport-preprocess-x-finalbuild.xml for editing.

a. Add the following code to the SELECT clause:

CASE 
   WHEN REGEXP_SUBSTR(VI_OP.PRICE, 'price_USD:([0-9.]+)(\|\||$)', 1, 1, 'c',1) &lt;&gt; 0 
      THEN 
         (REGEXP_SUBSTR(VI_OP.PRICE, 'price_USD:([0-9.]+)(\|\||$)', 1, 1, 'c',1) - COSTPRICE.PRICE) / 
          REGEXP_SUBSTR(VI_OP.PRICE, 'price_USD:([0-9.]+)(\|\||$)', 1, 1, 'c',1) * 100 
      ELSE NULL 
END AS PROFIT_MARGIN

Add the following code to the FROM clause:

LEFT OUTER JOIN VI_OFFERPRICE_#INDEX_SCOPE_TAG# VI_OP ON (CATENTRY.CATENTRY_ID=VI_OP.CATENTRY_ID)
LEFT OUTER JOIN 
    (SELECT OFFER.CATENTRY_ID, OFFERPRICE.PRICE
    FROM OFFER
    INNER JOIN OFFERPRICE ON (OFFER.OFFER_ID = OFFERPRICE.OFFER_ID AND OFFERPRICE.CURRENCY = 'USD')
    INNER JOIN TRADEPOSCN ON (OFFER.TRADEPOSCN_ID = TRADEPOSCN.TRADEPOSCN_ID AND TRADEPOSCN.NAME = 'My Company Cost Price' )
    ) COSTPRICE
ON (VI_OP.CATENTRY_ID = COSTPRICE.CATENTRY_ID)

The final SQL should look something like the following snippet.

"CREATE VIEW X_VI_CE_#INDEX_SCOPE_TAG#_#lang_tag# AS(
SELECT 
	CATENTRY.CATENTRY_ID PK,
	CATENTRY.FIELD1 X_FIELD1_I,
	CATENTRY.FIELD2 X_FIELD2_I,
	CATENTRY.FIELD3 X_FIELD3_D,
	CATENTRY.FIELD4 X_FIELD4_Q,
	CATENTRY.FIELD5 X_FIELD5_SM,
	CATENTDESCOVR.FIELD1 X_FIELD1_NL_I,
	CATENTDESCOVR.FIELD2 X_FIELD2_NL_Q,
	CASE 
		WHEN REGEXP_SUBSTR(VI_OP.PRICE, 'price_USD:([0-9.]+)(\|\||$)', 1, 1, 'c',1) &lt;&gt; 0 
		THEN 
			(REGEXP_SUBSTR(VI_OP.PRICE, 'price_USD:([0-9.]+)(\|\||$)', 1, 1, 'c',1) - COSTPRICE.PRICE) / 
			REGEXP_SUBSTR(VI_OP.PRICE, 'price_USD:([0-9.]+)(\|\||$)', 1, 1, 'c',1) * 100 
		ELSE NULL 
	END AS PROFIT_MARGIN,
	CATENTDESCOVR.FIELD3 X_FIELD3_NL_S
FROM CATENTRY
	INNER JOIN TI_CATENTRY_#INDEX_SCOPE_TAG# CE ON (CATENTRY.CATENTRY_ID=CE.CATENTRY_ID)
	LEFT OUTER JOIN CATENTDESCOVR ON (CATENTRY.CATENTRY_ID=CATENTDESCOVR.CATENTRY_ID)
	LEFT OUTER JOIN VI_OFFERPRICE_#INDEX_SCOPE_TAG# VI_OP ON (CATENTRY.CATENTRY_ID=VI_OP.CATENTRY_ID)
	LEFT OUTER JOIN 
		(SELECT OFFER.CATENTRY_ID, OFFERPRICE.PRICE
		FROM OFFER
		INNER JOIN OFFERPRICE ON (OFFER.OFFER_ID = OFFERPRICE.OFFER_ID AND OFFERPRICE.CURRENCY = 'USD')
		INNER JOIN TRADEPOSCN ON (OFFER.TRADEPOSCN_ID = TRADEPOSCN.TRADEPOSCN_ID AND TRADEPOSCN.NAME = 'My Company Cost Price' )
		) COSTPRICE
	ON (VI_OP.CATENTRY_ID = COSTPRICE.CATENTRY_ID))"	name="X_VI_CE_#INDEX_SCOPE_TAG#_#lang_tag#"

Save and close the file.

Change to the search-config-ext\index\managed-solr\config\v3\CatalogEntry directory, and open the x-data-config.xml file for editing. Add the following field declaration mappings to map the field from the database to the index field that defined in the x-data-config.xml file.
```
<field column="PROFIT_MARGIN" name="profitMargin"/>
```
Save and close the file.