Skip to Content

Choose Your Favorite Food and Create View (SAP TechEd)

test
0 %
Choose Your Favorite Food and Create View (SAP TechEd)
Details
// Explore More Tutorials

Choose Your Favorite Food and Create View (SAP TechEd)

09/03/2019

Choose your favorite foods in the database and create a database view.

You will learn

  • How to create a database view
  • How to use fuzzy search on a text column

This mission can only be completed at SAP TechEd.


Step 1: Explore the data

The project you have just cloned is using tables in a shared HDI container, which you can access through synonyms.

Right-click on the db folder and choose Open HDI Container.

Explore data

You should see your own HDI container. Click Synonyms. Choose the table COMM_FOODS and then click Generate SELECT Statement.

Explore data

Scroll all the way down to the FROM statement to answer the validation below.

What does the name of the schema look like?
×
Step 2: Choose your favorite food

Press F8 or click run to execute the statement. You will see some of the foods included in the table.

Think of your favorite food, replace the placeholder below and introduce the following into fuzzy search as a where clause to look for it.

where contains(product_name, '<<YOUR FAVORITE FOOD HERE>>', fuzzy(0.8,'textsearch=compare'))
AND to_nvarchar("PURCHASE_PLACES") <> 'NULL';

Run run the statement and make sure you get results.

Explore data

If you do not get any results, try a different food item or use the same example. Leave this SQL tab and results open.

Note how the ingredients are a long text separated by commas.

Explore data

What is going on?

You can see the fuzzy search in action. This is part of the definition of the table you are querying:

table definition

Notice the FUZZY SEARCH ON addition to the text field that allows you to search for a word and get similar results regardless of typos and case.

Log on to answer question
Step 3: Create a view

Go back into the development view. Right-click db/src and choose New > Database Artifact.

Explore data

Copy and paste the following name to create the design-time artifact for a view using SQL DDL:

data/myFood

Choose hdbview.

Click Create to create both a folder and a file to define your view.

Explore data

The goal of this view is to get a limited list of foods containing your search term or a similar word.

Paste the following content into the file

view "myFood" as
	SELECT top 10
		TO_INT("ID") as "ID",
    "PRODUCT_NAME",
  	"GENERIC_NAME",
  	"QUANTITY",
  	"CITIES",
  	"CITIES_TAGS",
  	"PURCHASE_PLACES",
  	"STORES",
  	"COUNTRIES",
  	"INGREDIENTS_TEXT",
  	"ALLERGENS",
  	"ALLERGENS_TAGS",
  	"TRACES",
  	"TRACES_TAGS",
  	"SERVING_SIZE",
  	"SERVING_QUANTITY",
  	"NO_NUTRIMENTS",
  	"NUTRITION_GRADE_FR",
  	"MAIN_CATEGORY"
  FROM "COMM_FOODS"
  WHERE ...

Complete the where clause with the SQL statement you executed in the previous step.

Explore data

Save the view.

Log on to answer question
Step 4: Build the view

It is now time to physically create the view in the database. Right-click on the definition of the view and choose Build.

Explore data

Make sure the build has been successful

Log on to answer question
Step 5: Check the view

Go back into the Database Explorer. Right-click your view and choose Open Data.

Explore data

Click SQL and copy the SELECT statement into the validation box below.

Explore data
Paste the full generated SQL statement
×

Next Steps

Back to top