Query with the SQL Console in SAP HANA Database Explorer

1. Select a connection and open the SQL console.

   

2. Notice that the database connection the SQL console is connected to is displayed and that toolbar items are shown after a left-click the … button. There are options to connect, disconnect, or change the connection.

   

3. Enter the SQL statements below.

   SELECT * FROM HOTEL.HOTEL;
   INSERT INTO HOTEL.HOTEL VALUES(24, 'River Boat', '788 MAIN STREET', 'New Orleans', 'LA', '79872', NEW ST_POINT('POINT(-90.076919 29.957531)', 4326)); --will cause a unique constraint error when executed
   

4. Expand the Run dropdown menu. Notice that there are multiple options along with their shortcut keys.

   

5. Choose Run to execute the SQL. The records from the HOTEL table are shown in the results tab.

   

   The unique constraint violation error is expected and is used here to demonstrate how the results of SQL execution are shown.

6. The Messages tab displays information about the executed queries as well as any errors.

   

7. The History tab displays the last 50 successfully executed queries. A previously executed query can be searched for and recalled.

   

8. Reload the page. Notice that when the database explorer re-loads, the SQL console tab and the statements it has last run are shown, minus contents of the result, messages, and history tab.

   

   This behavior can be set via the SQL console preferences which is shown in more detail in Step 5: SQL console preferences.

9. Sometimes you need to execute a SQL statement that takes a long time to run–longer than you want to keep your browser open. In that case, you can run it as a background activity. This allows you to close your browser window and come back later to see the results of the statement.

   

10. To view the results of a query that was run in the background, open the Background Activities Monitor, select the query, and then choose the Open Details button. A new SQL console will open displaying the SQL, the results, and any messages from the execution period.

    

11. The SAP HANA database explorer provides the ability to run a query against multiple databases.

    

12. The below dialog enables multiple databases to be selected.

    

13. To view the results of a query run on multiple databases, open the Background Activities Monitor, select the query to view the result of, and then choose the Open Details button.

    

14. Returned results in HTML, XML, JSON, or binary can be displayed in a separate viewer. Enter the SQL statements below and execute the query.

    SELECT
    '{ "name":"John", "age":30, "cars": { "car1":"Ford", "car2":"BMW", "car3":"Fiat" }}'
    AS JSON_EXAMPLE FROM DUMMY;
    
    SELECT * FROM HOTEL.CUSTOMER FOR JSON;
    

    

    Choose View data to open a viewer.

    

15. The results of a query can be downloaded and exported.

    

1. Once a statement is started, press Ctrl+Space to see a list of possible statements based on what you have typed.

   

2. By selecting the statement you want to use from the provided options, the SQL statement will be written into the console. Some information will need to be filled in to the generated statement. In the example below, table names need to be replaced and the columns to be used in the ON clause need to be provided.

   

3. In addition to completing SQL statements, this autocomplete shortcut can also be applied to database objects. In the example below, autocomplete provides a dropdown of all the available tables matching the first couple of letters provided.

   Copy the following SQL statement into the console and then use the autocomplete shortcut to see the available tables:

   SELECT * FROM HOTEL.RE
   

   

1. On the right-hand side of the SQL console, there is a statement help panel that provides more information about statement syntax, and any tables, functions, stored procedures, or SQL functions that have been referenced.

   

2. The first section in the help panel is Statement/Syntax. This section looks at the keywords in the current SQL statement and identifies what type of statement is written. It provides information on the proper syntax for that statement.

   Copy the following SQL into the SQL console to populate the Statement/Syntax section of the help panel:

   SELECT
     H.NAME AS HOTEL_NAME,
     R.TYPE,
     R.FREE,
     R.PRICE
   FROM
     HOTEL.ROOM AS R
     LEFT OUTER JOIN
     HOTEL.HOTEL AS H
     ON R.HNO = H.HNO;
   

   

   If you left-click the name of the statement being displayed in the Statement/Syntax dropdown (blue link), you will be redirected to an SAP document providing more information on that particular statement.

   

3. The second section shows tables or views referenced in the SQL statement, the schema it belongs to, and the columns in that table/view.

   

   The object editor can be opened by clicking on the name of the table or view.

4. The third section shows stored procedures or functions that are referenced in the SQL statement, as well any input and output parameters.

   Copy the following SQL into the SQL console to populate the Procedures and Functions section of the help panel:

   SELECT HOTEL.AVERAGE_PRICE('suite') FROM DUMMY;
   

   

5. The final section in the help panel shows any SQL functions that have been included in the SQL. For each SQL function referenced, both input and return parameters are listed.

   Copy the following SQL into the SQL console to populate the SQL Functions section of the help panel:

   SELECT TYPE, TO_DECIMAL(ROUND(sum(PRICE) / COUNT(*), 2, ROUND_HALF_UP)) as "Avg Room Price"
   FROM HOTEL.ROOM
   GROUP BY TYPE;
   

   

   Left-click the name of the SQL function in the SQL Functions section to open the SAP help showing detailed information on the function.

The statement library contains a mix of pre-populated system statements and user-saved statements.

1. Frequently used statements can be saved to the statement library for easy access. Enter the statement below into the SQL console and click the Add to Statement Library button.

   /*
   
   [DESCRIPTION]
   
   - Future guest check-ins across all hotels
   
   */
   
   SELECT * FROM HOTEL.RESERVATION
       WHERE ARRIVAL >= CURRENT_DATE;
   

   

2. A dialog will appear where you can name the query to be saved.

   

3. To view the statement library, right click a database connection and select Show Statement Library.

   

4. In the statement library there are two types of statements: user-defined and system. This is shown in the Type column. User-defined statements are statements that you have created and added, like All Future Check-ins, while system statements are already created and added to the library. System statements are often used for monitoring and diagnostic purposes.

   

   Previously, some of the included system statements came from SAP Note 1969700 - SQL Statement Collection for SAP HANA. If you wish to add these, they can be downloaded from the SAP Note referenced above and added as user statements. Another source of diagnostic information are the SQL queries contained in M_SYSTEM_INFORMATION_STATEMENTS System View.

5. To run a statement, select one from the statement library and click the Open in SQL Console button.

   

   It is also possible to export and import SQL statements directly to/from the file system.

   

   Files can then be shared using a version control system such as git.

6. User-defined statements can be edited. From the Statement library, select the desired statement to open it within the SQL console and make the desired changed. Select Add to Statement Library to overwrite the previous statement.
   

Congratulations! You have now explored selected features of the SQL console.