Using SQLScript in the DWC (DataSphere)

Jörg Brandeis

(C) Brandeis Consulting.

Approaches for SQL in DWC

  • SQL Expressions for columns in graphical views
  • SQL-View - Language SQL (Standard Query)
  • SQL-View - Language SQLScript (Table functions)
  • Open SQL Schema - A special schema for a space
  • Existing HDI Containers - Connection request via Ticket…
(C) Brandeis Consulting.

Schema Names

Each Space has a corresponding DB-Schema.

Each Database User has a corresponding schema.

(C) Brandeis Consulting.

SQL Expressions for calculated Columns

Many Features of scalar SQL Expressions are available e.g.

  • Operator Expressions
  • SQL-Functions
  • CASE Expressions
  • Window Functions - a little buggy in the preview when a WF is used as an expression in other expressions e.g. caluclations
  • Sub-SELECTs

Identifiers are Case Sensitive, even when you omit the double quotes.

Not (yet) supported

  • Call User Defined Functions (UDF) from OpenSQL DB-Schema
(C) Brandeis Consulting.

SQL-View - Language SQL (Standard Query)

Full SELECT Features available. Even Sub-Selects in the WHERE-Clause

Identifiers are Case Sensitive, even when you omit the double quotes.

Access to UDFs of the OpenSQL Schema is not possible.

(C) Brandeis Consulting.

SQL-View - Language SQLScript (Table functions)

Full Features of SQLScript.

Access to UDFs of the Open SQL -Schemas requires granting the respective privileges:

grant execute on SCHEMA SQLSCRIPT_TRAINING#DB_USER to SQLSCRIPT_TRAINING

Identifieres are written in the normal SQLScript manner:

  • Simple notation without doublequotes: will be converted to Upper Case. Example: EntenQuark will become ENTENQUARK
  • Special notation with double quotes: exactly as entered (Case Sensitive). Example: "Gütertaufen" will remain Gütertaufen
(C) Brandeis Consulting.

Open SQL Schema - A special schema for a space

Features

  • Read Access to exposed Objects in the DWC Schema:
  • Objects are reachable from the DWC Schema
  • Full SQL Access with
    • Integration Scenarios
    • Procedures and Functions
    • Tables and Views

But: no Repository

(C) Brandeis Consulting.

Existing HDI Containers

  • Connections must be requested via Support Ticket
  • Read Access from and to the DWC Schema
  • All Features from the BTP usable
(C) Brandeis Consulting.