SAP HANA Tutorial, Material and Certification Guide

Text Join is used in order to get language-specific data.

Before understanding the text join, it is important that we understand Text Tables.

Text Tables:

Table A is a text table of table B if the key of A comprises the key of B and an additional language key field (field of data type LANG). Table A may therefore contain explanatory text in several languages for each key entry of B.

Text Join in SAP HANA:

Text Join is used to fetch the description based on user's session language. Once we implement the text join in SAP HANA, it automatically find out user's language and give description in that language.

Implementation of Text Join in SAP HANA:

Example:

Suppose there is a table called "PRODUCT" which contains Product ID and Product Name.

There is also a text table of "PRODUCT", which is called "PRODUCT_TEXT_TABLE". The text table contains the description of products in different language. For example "Cotton Shirts" in English and "Baumwoll-Shirts" in German.

Purpose:

Create a calculation view which will give Product Name and Product Description. The Product Description should be only in user's session language.

Implementation:

1. Open HANA Studio. Right click on your schema and open SQL Editor. Copy the below SQL script to create 2 tables - "PRODUCT" and "PRODUCT_TEXT_TABLE".

-- REPLACE WITH YOUR ACTUAL SCHEMA NAME

-- PRODUCT table

CREATE COLUMN TABLE ""."PRODUCT"(

"PRODUCT_ID" INTEGER ,

"PRODUCT_NAME" VARCHAR(20) ,

primary key ("PRODUCT_ID"));

-- PRODUCT text table

CREATE COLUMN TABLE ""."PRODUCT_TEXT_TABLE"(

"PRODUCT_ID" INTEGER ,

"LANGUAGE" VARCHAR(1),

"PRODUCT_DESCRIPTION" VARCHAR(50) ,

primary key ("PRODUCT_ID", "LANGUAGE"));

insert into ""."PRODUCT" values(1,'Shirts');

insert into ""."PRODUCT" values(2,'Jackets');

insert into ""."PRODUCT" values(3,'Trousers');

insert into ""."PRODUCT" values(4,'Coats');

insert into ""."PRODUCT" values(5,'Purse');

insert into ""."PRODUCT_TEXT_TABLE" values(1,'E', 'Cotton Shirts');

insert into ""."PRODUCT_TEXT_TABLE" values(1,'D', 'Baumwoll-Shirts');

insert into ""."PRODUCT_TEXT_TABLE" values(2,'E', 'Leather jacket');

insert into ""."PRODUCT_TEXT_TABLE" values(2,'D', 'Lederjacke');

insert into ""."PRODUCT_TEXT_TABLE" values(3,'E', 'Trousers and Pants');

insert into ""."PRODUCT_TEXT_TABLE" values(3,'D', 'Hosen und Hosen');

insert into ""."PRODUCT_TEXT_TABLE" values(4,'E', 'Coats and Blazers');

insert into ""."PRODUCT_TEXT_TABLE" values(4,'D', 'Muntel und Jacken');

insert into ""."PRODUCT_TEXT_TABLE" values(5,'E', 'Purse and Handbags');

insert into ""."PRODUCT_TEXT_TABLE" values(5,'D', 'Geldburse und Handtaschen');

Note: The Language code for English is "E" and for German is "D"

2. Right click on your schema and refresh to see the 2 tables created.

3. Go to "Content" and right click on your package. Select Calculation view.

4. Give Name and Description of the Calculation View. Select Data Category as "Dimension".

5. Add a "Join" block and add both the tables "PRODUCT" and "PRODUCT_TEXT_TABLE".

6. Make a join on the column "PRODUCT_ID". Specify the Join Type as "Text Join". Click on the Language option and select "Language" column. Add "PRODUCT_ID", "PRODUCT_NAME" and "PRODUCT_DESCRIPTION" to output.