Online Tutorials & Training Materials | STechies.com
Register Login

Difference between Oracle and SAP HANA in SQL Queries

|| || 0

Difference between Oracle and SAP HANA in SQL Queries
Stechies

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')

 

 


Related Articles

0.0125 seconds.