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

In a file manager utility, go to the following directory.
- solrhome\MC_masterCatalogID\locale\CatalogEntry\conf
The MC_masterCatalogID folder includes the configurations files for each language.
For example, solrhome\MC_10001\en_US\CatalogEntry\conf
Open the solrcore.properties file for editing.
Uncomment the dataImporter.ext.querySelect property and set the value for the property to be the following SQL statement.
```
CASE WHEN TI_OFFERPRICE.PRICE_USD !=0 
THEN (TI_OFFERPRICE.PRICE_USD - COSTPRICE.PRICE) / TI_OFFERPRICE.PRICE_USD * 100 
ELSE NULL END AS PROFIT_MARGIN,
```
Ensure that you append a comma to the query statement. This statement is appended to the original query statement that is within the base wc-data-config.xml file.

Uncomment the dataImporter.ext.queryFrom property and set the value for the property to be the following value.


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 List' )) 
COSTPRICE ON (TI_OFFERPRICE.CATENTRY_ID = COSTPRICE.CATENTRY_ID)

This value is appended to the origianl query that is defined in the wc-data-config.xml file.

For example, the content of your properties file can resemble the following code snippet. The SQL statements are included across multiple lines for readability.


# solrcore.properties
dataImporter.ext.productQuerySelect=CASE WHEN TI_OFFERPRICE.PRICE_USD !=0 
 THEN (TI_OFFERPRICE.PRICE_USD - COSTPRICE.PRICE) / TI_OFFERPRICE.PRICE_USD * 100 
 ELSE NULL END AS PROFIT_MARGIN,
dataImporter.ext.productQueryFrom=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 List' )) 
 COSTPRICE ON (TI_OFFERPRICE.CATENTRY_ID = COSTPRICE.CATENTRY_ID)

Save and close the file.
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 you defined in the x-schema.xml file.
```
<field column="PROFIT_MARGIN" name="profitMargin"/>
```
Where
- PROFIT_MARGIN is the column alias in the preceding select SQL statement.
- profitMargin is the index field that you defined within the x-schema.xml file.
Save and close the file.