Skip to Content
Technical Articles
Author's profile photo Yogananda Muthaiah

HANA – Most Common SQL Statements & Functions

Finding index and primary keys of tables

SELECT IFNULL(CONSTRAINT,'NUNIQUE'),
INDEX_NAME,COLUMN_NAME 
FROM INDEX_COLUMNS WHERE SCHEMA_NAME = '%s'
AND TABLE_NAME = '%s' 
ORDER BY INDEX_OID,POSITION

Show details of users that have been logged-in

select * from "SYS"."USERS"
where "LAST_SUCCESSFUL_CONNECT" is not null
order by 9 desc;

To get all Libraries

select to_char(definition) from public.libraries 
where schema_name='EXT';

To get all DDL

call get_object_definition('<SCHEMA>','<TABLENAME>');
ALTER TABLE schema.table ADD PRIMARY KEY (column1,column2)

Check invalid custom DB views

select * from "SYS"."VIEWS"
where schema_name not like 'SAP%' and is_valid = 'FALSE';

Check inactive custom DB objects

select * from "_SYS_REPO"."INACTIVE_OBJECT"
where "PACKAGE_ID" not like 'sap%';

Check which SAP language settings are being used by current user

select session_context('LOCALE_SAP'),
session_context('LOCALE') 
from dummy;

## Search executed SQL statements, 
e.g. to find out who deleted a table
select * from "SYS"."M_EXECUTED_STATEMENTS" 
where "STATEMENT_STRING" LIKE 'DROP TABLE%';

Show custom settings within global.ini and indexserver.ini

select * from "SYS"."M_INIFILE_CONTENTS"
where ("LAYER_NAME" = 'SYSTEM' or "HOST" <> ") 
and ("FILE_NAME" = 'global.ini' 
or "FILE_NAME" = 'indexserver.ini');

Show assigned user roles

select * from "SYS"."GRANTED_ROLES"
where "GRANTEE_TYPE" = 'USER';

Show assigned repository privileges

select * from "SYS"."GRANTED_PRIVILEGES"
where object_type = 'REPO';

Show objects owned by non-system users

select * from "SYS"."OWNERSHIP"
where owner_name not like 'SAP%' and owner_name not like '%SYS%'
order by 1,2;

Analyze expensive statement trace

select
to_varchar("STATEMENT_START_TIME",'DD.MM.YYYY') "EXEC_DATE",
to_varchar("STATEMENT_START_TIME",'HH24:MI:SS') "EXEC_TIME",
to_int("DURATION_MICROSEC"/1000000) "DURATION_S",
to_decimal("MEMORY_SIZE"/1073741824,10,1) "MEM_GB",
"RECORDS",
"DB_USER",
"APP_USER",
"APPLICATION_NAME",
"STATEMENT_STRING",
length("STATEMENT_STRING") "SQL_LENGTH",
OCCURRENCES_REGEXPR('JOIN' FLAG 'i' IN "STATEMENT_STRING") "JOIN",
OCCURRENCES_REGEXPR('CASE' FLAG 'i' IN "STATEMENT_STRING") "DISTINCT",
"ERROR_TEXT",
"PARAMETERS"
from "SYS"."M_EXPENSIVE_STATEMENTS"
where "OPERATION" in 
('INSERT','SELECT','AGGREGATED_EXECUTION') –exclude background activity
and "RECORDS" > 0
and to_varchar("STATEMENT_START_TIME", 'YYYYMMDD') = current_date
and to_int(to_varchar("STATEMENT_START_TIME",'HH24′)) 
between 8 and 17 –business hours
order by 3 desc;

Most Common Functions used in Implementation

SELECT SESSION_USER "session user" FROM DUMMY;

SELECT TO_DATE('2010-01-12', 'YYYY-MM-DD') "to date" 
FROM DUMMY;

SELECT TRIM ('a' FROM 'aaa123456789aa') "trim both" FROM DUMMY;

SELECT CURRENT_DATE "current date" FROM DUMMY;

SELECT 
DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'),
TO_DATE('2010-01-05', 'YYYY-MM-DD')) "days between" 
FROM DUMMY;

SELECT UPPER ('Ant') "uppercase" 
FROM DUMMY;

SELECT CONCAT ('C', 'at') "concat" FROM DUMMY;

SELECT FLOOR (14.5) "floor" FROM DUMMY;

SELECT TO_DECIMAL(7654321.888888, 10, 3) "to decimal"
FROM DUMMY;

SELECT REPLACE ('DOWNGRADE DOWNWARD','DOWN', 'UP') "replace" 
FROM DUMMY;

SELECT RTRIM ('endabAabbabab','ab') "rtrim" FROM DUMMY;

SELECT RIGHT('HI0123456789', 20) "right" FROM DUMMY;

SELECT  WEEK ('2017-01-02') FROM DUMMY;

SELECT LENGTH ('length in char') "length" FROM DUMMY;

SELECT SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY;

SELECT WEEKDAY (TO_DATE ('2010-12-31', 'YYYY-MM-DD')) "week day" 
FROM DUMMY;

SELECT TO_VARCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to varchar" 
FROM DUMMY;

SELECT 
YEARS_BETWEEN(TO_DATE('2001-01-01'), 
TO_DATE('2003-03-14')) "years_between" 
FROM DUMMY;

SELECT YEAR (TO_DATE ('2011-05-30', 'YYYY-MM-DD')) "year" 
FROM DUMMY;

SELECT MONTH ('2011-05-30') "month" FROM DUMMY;

SELECT NOW () "now" FROM DUMMY;

SELECT LAST_DAY (TO_DATE('2010-01-04', 'YYYY-MM-DD')) "last day" 
FROM DUMMY;


SELECT DAYOFYEAR ('2021-05-30') "dayofyear" 
FROM DUMMY;
SET SCHEMA "SAPABC"; 
CREATE TABLE MY_DATES (FCID NVARCHAR(2), STARTDATE DATE, ENDDATE DATE); 
INSERT INTO MY_DATES VALUES ('01', '2014-01-01', '2014-02-14'); 
INSERT INTO MY_DATES VALUES ('01', '2014-04-01', '2014-05-14'); 
INSERT INTO MY_DATES VALUES ('01', '2014-07-01', '2014-08-05'); 
INSERT INTO MY_DATES VALUES ('01', '2014-10-01', '2014-10-30'); 
SELECT WORKDAYS_BETWEEN(FCID, STARTDATE, ENDDATE) "production duration" 
FROM MY_DATES;


Reference

HANA SQL Functions

https://help.sap.com/viewer/7c78579ce9b14a669c1f3295b0d8ca16/Cloud/en-US/20a61f29751910149f99f0300dd95cd9.html

Keep watching here and comment if you like to add on top of it

Assigned tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      This list of statements looks rather arbitrary to me.

      Why would you always use the SYS views/tables when there are PUBLIC synonyms/versions for it?

      And why would one use the column number in the ORDER BY clause? That’s just lazy writing (as Deadpool would call it)...

      Rather than dropping SQL statements why don’t do write about how you used some of those statements to find something out in your HANA system? Something interesting and relevant for your situation. That’ll be interesting to read then as well.

       

       

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Hi Lars Breddemann,

      Thanks for reading the article!

      Thanks for providing your feedback for making the suggestion.  The above SQL statements are at a high level for Consultants implementing the SAP Commission project since I have tagged to global HANA.  I welcome to make the changes to your points mentioned.

      Author's profile photo Teja Thogaru
      Teja Thogaru

      Very helpful   article Yoga (Harvey) 🙂

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks Mike ( Teja Thogaru )!

      Keep sharing this to your friends.

      Author's profile photo Vijaya Rayapudi
      Vijaya Rayapudi

      Thank you! Yoga for sharing. Very helpful. 🙂

      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah
      Blog Post Author

      Thanks Vijaya Rayapudi !

      Keep sharing to your friends

      Author's profile photo Fabrice MAFOUANA-DUMOND
      Fabrice MAFOUANA-DUMOND

      Yoga, your sharing is very helpfull.

      Thank you 🙂