Skip to Content

Query with the SQL Console in SAP HANA Database Explorer

test
0 %
Query with the SQL Console in SAP HANA Database Explorer
Details

Query with the SQL Console in SAP HANA Database Explorer

November 23, 2020
Created by
October 20, 2020
Explore features of the SQL console and see how it facilitates querying an SAP HANA database.

You will learn

  • How to run SQL queries using the SQL console and add filters to the results
  • How to use different features of the SQL console including keyboard shortcuts, autocomplete, statement help, and the statement library

Prerequisites

  • An SAP HANA database such as SAP HANA Cloud trial or the SAP HANA, express edition that includes the SAP HANA database explorer
  • You have completed the first 3 tutorials in this group.


Step 1: Execute SQL
  1. Select a connection and open the SQL console.

    open 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.

    Connected SQL console
  3. Enter the SQL statements below.

    SELECT * FROM HOTEL.HOTEL;
    INSERT INTO HOTEL.HOTEL VALUES(27, 'River Boat', '79872', '788 MAIN STREET');  --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.

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

    result

    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.

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

    History Tab
  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.

    After reloading

    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.

    Run As Background Activity
  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.

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

    Run Against Multiple Databases
  12. The below dialog enables multiple databases to be selected.

    Database Groups
  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.

    Multiple Databases Result
  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;
    
    JSON Query Results

    Choose View data to open a viewer.

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

    Download Table Results

Log on to answer question
Step 2: Shortcuts
  1. Keyboard shortcuts are available to provide alternate methods of completing frequently performed tasks. Right-click in the SQL console and choose Keyboard Shortcuts.

    Open Keyboard Shortcuts

    The list of shortcuts displays.

    Keyboard Shortcuts
  2. Some commonly-used keyboard shortcuts are highlighted in the table below:

    Action Shortcut
    Comment/Uncomment Line Ctrl+/
    Add Comment Block Ctrl+Shift+/
    Format Code Ctrl+B
    Switch tabs Ctrl+Alt+Pageup Ctrl+Alt+Pagedown
    Close Window Ctrl+W
    Run All F8
    Run Statement F9
    Increase/Decrease Font Size Crtl+Shift+Up Ctrl+Shift+Down
    Go to Next Error Alt+E
    Go to Previous Error Alt+Shift+E
  3. Shortcuts can be configured in Global Preferences under Keyboard Shortcuts.

    Open Keyboard Shortcuts Settings
  4. A SQL console can be toggled to enter full-screen mode. Double-click a SQL console tab to enter this mode.

    Full Screen

    To exit full screen, double-click the SQL console tab again.

  5. When the Run button is pressed with a portion of the statements selected, only the selected statements are run. In the example below, only the SELECT statement was run.

    run selected
Log on to answer question
Step 3: Autocomplete
  1. Once a statement is started, press Ctrl+Space to see a list of possible statements based on what you have typed.

    Code Completion Options
  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.

    Code Completion Statement
  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
    
    Code Completion Table
Log on to answer question
Step 4: Statement help panel
  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.

    Help Panel
  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;
    
    Statement Syntax Helper

    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.

    Statement Help Document
  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.

    Table and Views Referenced

    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;
    
    Procedures and Functions Referenced
  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;
    
    SQL Function Referenced

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

Log on to answer question
Step 5: SQL console preferences
  1. Select SQL Console or SQL Code Completion from the Global Preferences to see the configurable preference settings for the SQL console.

    Error Handling Settings

    When changing these preferences, remember to press the Save button.

  2. Notice that there is a preference to limit the number of rows to display as well as the maximum number of open results. The following shows the results of these being set to 50 and 2.

    Limit results
  3. The default behavior when an error is encountered is to show the following dialog.

    SQL Error Prompt

    This behavior can be changed using the On error dropdown menu in the SQL console preference settings.

    On Error Settings
  4. There is also a syntax checker in the SAP HANA database explorer.

    Syntax Checker On

    It can be enabled or disabled using the Indicate Potential SQL Errors checkbox.

    Syntax Checker Settings

For additional details, consult SQL Console Preferences.

Log on to answer question
Step 6: Statement library

The statement library contains a mix of pre-populated statements, to help with monitoring, 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;
    
    Add to Statement Library
  2. A dialog will appear where you can name the query to be saved.

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

    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 Future check-ins, while system statements are already created and added to the library. System statements are often used for monitoring and diagnostic purposes.

    Statement Library

    Some of the system statements come from SAP Note 1969700 - SQL Statement Collection for SAP HANA. These statements can be used to help monitor the database. These statements are primarily intended for SAP HANA 2.0. Another source of diagnostic information are the SAP HANA monitoring views.

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

    Execute Statement

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

    Import and Export to File

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

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

Which of the following statements are true?
×

Next Steps

Back to top