HANA CDS Views Essential Cheat Sheet

// UI annotations
define view ZMY_VIEW
  @UI.headerInfo: { typeName: 'Material', typeNamePlural: 'Materials' }
as
  select from MARA {
    key matnr,
        mtart,
        meins
  };

// Metadata and labels
@EndUserText.label: 'Material Master View'
@EndUserText.description: 'Complete material information'
define view ZMY_MATERIAL as
  select from MARA {
    key matnr : mara.matnr
      @EndUserText.label: 'Material Number',
        mtart : mara.mtart
      @EndUserText.label: 'Material Type',
        meins : mara.meins
      @EndUserText.label: 'Unit of Measure'
  };

// Semantic annotations
@Semantics.systemDate.lastChangedAt: true
define view ZCUSTOMER_WITH_META as
  select from KNA1 {
    key kunnr,
        name1,
        laeda as changed_date : date
  };

// Text association
define view ZCUSTOMER_VIEW as
  select from KNA1 {
    key kunnr,
        name1 : KNA1.name1
          @Semantics.text: true,
        land1
  };

// Currency/Quantity annotations
define view ZORDER_AMOUNTS as
  select from VBAK {
    key vbeln,
        waerk : vbak.waerk
          @Semantics.currencyCode: true,
        netwr : vbak.netwr
          @Semantics.amount.currencyCode: 'waerk'
  };

// Access control annotation
@AccessControl.authorizationCheck: #CHECK
define view ZSENSITIVE_DATA as
  select from VBAK {
    key vbeln,
        kunnr,
        netwr
  };

// Searchable and sortable
define view ZPRODUCT_VIEW as
  select from MARA {
    key matnr,
        mtart : mara.mtart
          @Search.defaultSearchElement: true
          @EndUserText.label: 'Type',
        maktx
          @Search.defaultSearchElement: true
  };

// Nested select with join
define view ZCOMPLEX_VIEW as
  select from MARA as m
    left outer join MAKT as t
    on m.matnr = t.matnr
    and t.spras = 'E'
  left outer join MARC as c
    on m.matnr = c.matnr
    and c.werks = '1000'
  {
    key m.matnr,
        m.mtart,
        t.maktx as description,
        c.labst as stock
  };

// Subquery in SELECT
define view ZMAT_WITH_ORDERS as
  select from MARA as m {
    key m.matnr,
        m.mtart,
        
        (select count(*) from VBAP as v 
         where v.matnr = m.matnr) 
         as order_count : int
  };

// UNION with different structures
define view ZCOMBINED_DOCS as
  select from VBAK {
    vbeln as doc_id,
    'ORDER' as doc_type,
    netwr as amount
  }
  union
  select from VBRK {
    vbeln as doc_id,
    'INVOICE' as doc_type,
    netwr as amount
  };

// Window functions (SAP HANA 2.0+)
define view ZRANK_MATERIALS as
  select from VBAP {
    key vbeln,
        key posnr,
        matnr,
        kwmeng,
        
        rank() over (
          partition by vbeln 
          order by kwmeng desc
        ) as rank : int
  };

// ROW_NUMBER
define view ZROW_NUMBERED as
  select from MARA {
    key matnr,
        mtart,
        
        row_number() over (
          order by matnr
        ) as row_num : int
  };

// Cross join (cartesian product)
define view ZCROSS_MATERIALS as
  select from MARA as m
    cross join MARC as c
    on m.matnr = c.matnr
  {
    key m.matnr,
        c.werks as plant
  }
  where m.mtart = 'FERT';

// Text types
define view ZTYPE_DEMO as
  select from MARA {
    key matnr : abap.char(18),        // Character
        maktx : abap.string,           // String
        meins : abap.unit              // Unit
  };

// Numeric types
define view ZCALC_TYPES as
  select from VBAP {
    key vbeln : abap.char(10),
        kwmeng : abap.quan(13,3),      // Quantity
        netpr : abap.curr(13,2),       // Currency
        percentage : abap.dec(5,2),    // Decimal
        counter : abap.int4            // Integer
  };

// Date and Time
define view ZDATE_DEMO as
  select from VBAK {
    key vbeln,
        erdat : abap.dats,             // Date
        erzet : abap.tims,             // Time
        created : abap.utclong          // Timestamp
  };

// Boolean and flags
define view ZFLAG_DEMO as
  select from MARA {
    key matnr,
        case 
          when mtart = 'FERT' then 'X'
          else ''
        end as is_finished : abap.char(1)
  };

// Currency and quantity codes
@Semantics.currencyCode: true
define view ZCURR_DEMO as
  select from VBAK {
    key vbeln,
        waerk : abap.cuky,             // Currency code
        netwr : abap.curr(13,2)
  };

// Casting examples
define view ZCAST_DEMO as
  select from MARA {
    key matnr,
        cast(matnr as abap.string) as str_matnr : 
          abap.string,
        cast(1000.50 as abap.dec(13,2)) as price : 
          abap.dec(13,2),
        cast('20240118' as abap.dats) as date_field : 
          abap.dats
  };

// Test view with parameters
select * from Z_MATERIAL_V(p_mtart => 'FERT')
  where rownum <= 10;

// Check compilation errors
// HANA Studio → SQLScript → Right-click view → Check syntax

// Debug view definition
// SELECT * FROM SYS.VIEWS 
// WHERE VIEW_NAME = 'Z_MATERIAL_V';

// Check association navigation
select from Z_ORDER_HIERARCHY_V {
  vbeln,
  kunnr,
  items.posnr,        // Navigate association
  items.matnr
} where vbeln = '0000123456';

// Monitor performance
// HANA Studio → SQL Analyzer → Paste query → Analyze

// Check execution plan
EXPLAIN SELECT * FROM Z_MATERIAL_V
  WHERE mtart = 'FERT';

// View execution statistics
// HANA Cockpit → SQL Statements → Find view query

// Debug type issues
// Check field types at definition point
// Hover in Studio to see inferred types

// Test expressions
// Create test view with calculation
define view Z_TEST_CALC as
  select from MARA {
    key matnr,
        cast(1000 as decimal(10,2)) * 1.19 as result
  };

// Enable debug output (development only)
set option connection_context = 'DEBUG_MODE=ON';

// View dependencies
// HANA Studio → Repository → View dependencies graph

// Check for circular references
// HANA Studio → Editors → Validate CDS model