SAP HANA SQL vs Oracle SQL
SQL statements created in Oracle wont work with SAP HANA it will return unexpected results, here are some transaction difference between Oracle and SAP HANA in SQL Queries.
| Oracle SQL | SAP HANA SQL | |
| Dummy query table | DUMMY | DUAL |
| Condition function | DECODE | MAP |
| Check for NULL value | NVL | IFNULL |
| Limit on result set | WHERE ROWNUM <= <num_records> | SELECT TOP <num_records> |
| Limit on result set with sorting | SELECT ... FROM ( SELECT ... FROM ... WHERE ... ORDER BY ... ) WHERE ROWNUM <= <num_records> | SELECT TOP <num_records> FROM xxx WHERE xxx ORDER BY |
| Analytical function for calculating ratio | RATIO_TO_REPORT | not available; can be reproduced using analytical SUM function |
| Analytical function for linking strings | LISTAGG (>= 11.2) | STRING_AGG (>= rev. 72) |
| Multilevel queries | CONNECT BY | no direct implementation, SAP Note 2073410 describes alternative |
| TO_DATE function | Result can contain date and time (depending on NLS_DATE_FORMAT) | Result is only the date; time is 00:00:00; use TO_TIMESTAMP. |
| Result is only the date; time is 00:00:00; use TO_TIMESTAMP. | DUMP(<value>) | DUMP(<value>) |
| Deadlocks | Termination of an involved DML operation, continuation of current transaction | Termination of transaction |
| Behavior for long-running exclusive lock waits | Long wait for release of lock | Termination for timeout (parameter lock_wait_timeout, default: 30 minutes) |
| Table with Explain information | PLAN_TABLE | EXPLAIN_PLAN_TABLE |
| Define default schema for queries | ALTER SESSION SET CURRENT_SCHEMA = <schema_name> | SET SCHEMA <schema_name> |
| Rounding | If required, the system rounds the value (INSERT of 1.9 in DECIMAL (5,0) columns -> SELECT returns 2) | If required, the system truncates the value (INSERT of 1.9 in DECIMAL (5,0) columns -> SELECT returns 1) |
| Accuracy of INTEGER calculations | High accuracy (SELECT 1 / 10000000 * 10000000 results in 1) | 6 decimal places (SELECT 1 / 10000000 * 10000000 results in 0) |
| Unique ID | ROWID | Internal $rowid$ column (column store), no equivalent in the row store |
| Behavior for an incorrect database hint | Hint is (usually) ignored | Error (468 : hint error: invalid hint) |
| Copying a table | DBMS_METADATA.GET_DDL or CREATE TABLE ... AS SELECT | CREATE TABLE xxx LIKE xxx [WITH DATA] |
| Determination of object definitions | DBMS_METADATA.GET_DDL('<object_type>', '<object_name>', '<object_owner>') | GET_OBJECT_DEFINITION( '<object_owner>', '<object_name>', '<object_type>') |
| SAP HANA deletes trailing blanks | DECODE(' ', '', 'empty string', 'blanks') | MAP(' ', '', 'empty string', 'blanks') |
| Empty string and NULL | DECODE('', NULL, 'NULL value', 'empty string') | MAP('', NULL, 'NULL value', 'empty string') |
| Concatenating a string with NULL results in NULL on SAP HANA | NVL('abc' || NULL, 'NULL value') | IFNULL('abc' || NULL, 'NULL value') |