Importance of SQL in SAP HANA Modeling
SQL plays an important role in SAP HANA modeling and can be implemented in different SAP HANA modeling objects, such as the following:
SQL expressions in calculation views
SQL-based analytic privileges
SQL is used to implement custom logic in calculation views where standard features of the graphical calculation view are not enough.
SAP HANA Cloud modelers should acquire skills in SQL so that they can be fully effective in projects. As well are writing custom logic, they need to be able to understand the behavior of calculation views at run-time and to use the debugging tools which display the generated SQL.
Launching the SQL Console
The SQL Console, which is available in the Database Explorer, is used to write and execute ad-hoc SQL statements.
Although the SQL Console is ideal for executing ad-hoc, one-time instructions, such as creating a temporary table or executing a test query, it is not recommended for generating persistent development objects such as functions, procedure or tables that will become part of the application. For this, we recommend you create a source file type that corresponds to the object you wish to create. For example:
Table - always use source file type .hdbtable.
Function - always use source file type .hdbfunction.
When you deploy the source file, the corresponding run-time database object is generated.
Nevertheless, the SQL Console is an important tool for testing and executing one-time statements, so let's take a look at it.
The SQL Console is launched from the Database Explorer, which in turn can be launched from Business Application Studio. Each time you launch the SQL Console a new tab appears where you write your code. You can open as many tabs as you wish.
When you launch an SQL Console you need to be aware of which database connection you are using because the executed SQL will run against that connection. Also the design-time errors, for example a table you specify can't be found, will be based on the connection you are using. The database connection for your console can be selected in a number of ways:
Highlight an entry in the database connection list and then press Open SQL Console button in the toolbar.
Right-click an entry in the database connection list and then choose Open SQL Console in the context menu.
Expand a database connection and locate a database object, then right-click the object and choose an SQL expression from the context menu such as Generate SELECT Statement.
You can clearly see which database connection you are using because this is displayed at the top of the screen.
Regardless of which connection you choose, you can easily swap the connection by using the button in the toolbar Connect this SQL console to a different database.
HDI or Classic Database Connections
There are two main types of database connection:
SAP HANA database (also known as catalog)
When executing statements against an HDI Container, you do not need to specify the schema. The schema is already known to the container you are running the statement against.
However, if you are executing statements against a connection that is an SAP HANA Databasecatalog type, then you must specify the schema in your statements.