SQLScript in DWC

SQL Expressions for columns in graphical views

Even subselects are allowed in the Expression Fields!

SQL-Views (SQL)

SQL-Views (SQL)

• HANA SQL
• One single statement
• Nested Sub-Queries are allowed

• Identifiers are case sensitive, even without double quotes!!!

SQL-Views (SQLScript)

SQL-Views (SQLScript)

• Language SQLScript with all possible language extents

  • Table variables

  • MAP_MERGE operators

  • Libraries (e.g. STRING)

  • Imperative Logic in SQLScript, e.g. FOR-Loop or IF/ELSE

  • The identifier in standard HANA SQL manner

• Limitations

  • Standard tables of SAP HANA are not available, e.g. DUMMY or M_TIME_DIMENSION

     

Example: Transpose Data

Example: Transpose Data

lt_fiscper3 = select lpad(element_number, 3, '0') as fiscper3 from series_generate_integer(1,1,13);
lt_tmp = select *,
                fiscyear || fiscper3 as fiscper,
                CASE fiscper3
                  when '001' then "AMOUNT_01"
                  when '002' then "AMOUNT_02"
                  when '003' then "AMOUNT_03"
                  when '004' then "AMOUNT_04"
                  when '005' then "AMOUNT_05"
                  when '006' then "AMOUNT_06"
                  when '007' then "AMOUNT_07"
                  when '008' then "AMOUNT_08"
                  when '009' then "AMOUNT_09"
                  when '010' then "AMOUNT_10"
                  when '011' then "AMOUNT_11"
                  when '012' then "AMOUNT_12"
                END as  amount
           from "PCA_PLAN"
           cross join :lt_fiscper3;

return select  "ACCOUNT",
               "CURR",
               "COMPCODE",
                "FISCPER",
               "AMOUNT"
       from :lt_tmp;

Open SQL Schema 

Open SQL Schema 

Open SQL Schema 

• Read Access to 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