SQL Syntax Translation: Oracle to HANA

TL;DR

Most Oracle SQL translates directly to HANA. The exceptions are common and repeatable: ROWNUM → LIMIT, DUAL → DUMMY, NVL → IFNULL, DECODE → CASE, CONNECT BY → recursive CTE (covered in lesson 3).

Keep this page bookmarked. When you encounter unfamiliar syntax during migration, flip back here and you'll find the pattern.

Watch for three gotchas: DUAL doesn't exist in HANA (use FROM DUMMY or omit FROM entirely), character sets differ (VARCHAR2 vs NVARCHAR), and date arithmetic works differently.

The Translation Table

This is your primary reference. Use it for every Callidus SQL query you're translating.

Operation	Oracle Database Syntax	SAP HANA Database Syntax	Notes
Top N rows	`WHERE ROWNUM <= 10`	`LIMIT 10`	ROWNUM is a pseudo-column in Oracle. HANA uses standard LIMIT or ROW_NUMBER() window function.
Dummy table	`FROM DUAL`	`FROM DUMMY` or omit	Oracle has DUAL (1-row dummy table). HANA has DUMMY. Or: `SELECT 1 AS val;` works without FROM.
Null replacement	`NVL(column, default)`	`IFNULL(column, default)` or `COALESCE(column, default)`	COALESCE works on both databases — safer choice for migrations.
Null replacement (2-arg)	`NVL2(col, if_not_null, if_null)`	`CASE WHEN col IS NOT NULL THEN if_not_null ELSE if_null END`	HANA doesn't have NVL2. Use CASE WHEN.
Conditional value	`DECODE(col, val1, result1, val2, result2, default)`	`CASE WHEN col=val1 THEN result1 WHEN col=val2 THEN result2 ELSE default END`	DECODE is Oracle-specific shorthand for CASE. Always use CASE in HANA.
Current date	`SYSDATE`	`CURRENT_DATE`	SYSDATE returns DATE (with time in Oracle). CURRENT_DATE returns DATE (without time).
Current timestamp	`SYSTIMESTAMP`	`CURRENT_TIMESTAMP`	For datetime values, use CURRENT_TIMESTAMP in HANA.
Days between dates	`date1 - date2`	`DAYS_BETWEEN(date1, date2)`	Oracle date arithmetic is implicit. HANA requires explicit function.
Date to string	`TO_CHAR(date_col, 'YYYY-MM-DD')`	`TO_VARCHAR(date_col, 'YYYY-MM-DD')`	HANA also supports CAST(date_col AS VARCHAR), but TO_VARCHAR is more explicit.
String to date	`TO_DATE('2026-01-01', 'YYYY-MM-DD')`	`TO_DATE('2026-01-01', 'YYYY-MM-DD')`	Syntax is identical. Good news!
String concatenation	`col1 \|\| col2` or `CONCAT(col1, col2)`	`col1 \|\| col2` or `CONCAT(col1, col2)`	Both syntaxes work in both databases.
String aggregation	`LISTAGG(col, ',') WITHIN GROUP (ORDER BY col)`	`STRING_AGG(col, ',' ORDER BY col)`	Completely different syntax. Easy to miss in migration.
Outer join (old syntax)	`a.id = b.id(+)`	Not supported — use `LEFT JOIN`	HANA doesn't support Oracle's old outer join syntax. Use standard ANSI syntax.
Right join (old syntax)	`a.id(+) = b.id`	`RIGHT JOIN`	Use standard ANSI RIGHT JOIN instead.
Substring	`SUBSTR(col, 1, 5)`	`SUBSTR(col, 1, 5)` or `LEFT(col, 5)`	Both SUBSTR and LEFT work in HANA. Use SUBSTR for consistency.
String length	`LENGTH(col)`	`LENGTH(col)` or `CHAR_LENGTH(col)`	Both work. CHAR_LENGTH is standard SQL.
Uppercase	`UPPER(col)`	`UPPER(col)`	Identical.
Lowercase	`LOWER(col)`	`LOWER(col)`	Identical.
Trim whitespace	`TRIM(col)`	`TRIM(col)`	Identical.
Absolute value	`ABS(num)`	`ABS(num)`	Identical.
Round	`ROUND(num, 2)`	`ROUND(num, 2)`	Identical.
Row number	`ROW_NUMBER() OVER (ORDER BY col)`	`ROW_NUMBER() OVER (ORDER BY col)`	Windowing functions work identically in both.
Rank	`RANK() OVER (ORDER BY col)`	`RANK() OVER (ORDER BY col)`	Identical.
Dense rank	`DENSE_RANK() OVER (ORDER BY col)`	`DENSE_RANK() OVER (ORDER BY col)`	Identical.
Lag/Lead	`LAG(col) OVER (ORDER BY col)`	`LAG(col) OVER (ORDER BY col)`	Identical.
Case-insensitive search	`UPPER(col) LIKE UPPER('search%')`	`UPPER(col) LIKE UPPER('search%')`	Same pattern works, but HANA collation settings may differ.

Detailed Examples

ROWNUM → LIMIT

ROWNUM is a pseudo-column in Oracle that assigns a sequence number to each row in a result set. It's filtered before sorting, which makes it tricky.

-- Get top 10 commission transactions by amount (Oracle way)
SELECT id, commission_amount, salesperson_id
FROM (
  SELECT id, commission_amount, salesperson_id
  FROM cs_transactions
  ORDER BY commission_amount DESC
)
WHERE ROWNUM <= 10;

-- Get top 10 commission transactions by amount (HANA way)
SELECT id, commission_amount, salesperson_id
FROM cs_transactions
ORDER BY commission_amount DESC
LIMIT 10;

⚠️ROWNUM gotcha: In Oracle, ROWNUM is assigned before ORDER BY, so you must use a subquery to sort first. In HANA, LIMIT is applied after ORDER BY, so you don't need the subquery. Cleaner syntax.

NVL → IFNULL or COALESCE

SELECT salesperson_id,
       NVL(commission_amount, 0) AS amount,
       NVL(tier, 'STANDARD') AS tier_level
FROM cs_transactions;

SELECT salesperson_id,
       IFNULL(commission_amount, 0) AS amount,
       IFNULL(tier, 'STANDARD') AS tier_level
FROM cs_transactions;

SELECT salesperson_id,
       COALESCE(commission_amount, 0) AS amount,
       COALESCE(tier, 'STANDARD') AS tier_level
FROM cs_transactions;

💡Migration tip: COALESCE works in both Oracle and HANA identically. If you use COALESCE instead of NVL, your code works on both databases during a gradual migration. This is a smart choice.

DECODE → CASE WHEN

DECODE is Oracle shorthand for nested IF/ELSE logic. It's not standard SQL and doesn't exist in HANA.

SELECT id,
       DECODE(status,
              'A', 'APPROVED',
              'R', 'REJECTED',
              'P', 'PENDING',
              'UNKNOWN') AS status_label
FROM cs_transactions;

SELECT id,
       CASE status
         WHEN 'A' THEN 'APPROVED'
         WHEN 'R' THEN 'REJECTED'
         WHEN 'P' THEN 'PENDING'
         ELSE 'UNKNOWN'
       END AS status_label
FROM cs_transactions;

Date Arithmetic: Implicit vs Explicit

-- Subtract dates to get days (implicit, Oracle-specific)
SELECT SYSDATE - created_date AS days_old
FROM cs_transactions;

-- Use DAYS_BETWEEN function (explicit, HANA-specific)
SELECT DAYS_BETWEEN(CURRENT_DATE, created_date) AS days_old
FROM cs_transactions;

⚠️Date arithmetic gotcha: Oracle allows you to add/subtract integers from DATE columns directly. HANA requires explicit functions (DAYS_BETWEEN, ADD_DAYS, ADD_MONTHS, etc.). This is one of the most common surprises in migration.

String Aggregation: LISTAGG → STRING_AGG

SELECT salesperson_id,
       LISTAGG(tier, ',') WITHIN GROUP (ORDER BY tier) AS tier_list
FROM cs_transactions
GROUP BY salesperson_id;

SELECT salesperson_id,
       STRING_AGG(tier, ',' ORDER BY tier) AS tier_list
FROM cs_transactions
GROUP BY salesperson_id;

Outer Join Syntax

Oracle supports old-style outer joins using the (+) notation. HANA only supports standard ANSI syntax.

SELECT t.id, s.name
FROM cs_transactions t, salesperson s
WHERE t.salesperson_id = s.id(+);

SELECT t.id, s.name
FROM cs_transactions t
LEFT JOIN salesperson s ON t.salesperson_id = s.id;

DUAL and Dummy Queries

A common Callidus pattern is to use DUAL for scalar calculations:

SELECT 123 * 456 AS product
FROM DUAL;

SELECT 123 * 456 AS product
FROM DUMMY;

SELECT 123 * 456 AS product;

💡Recommended: Omit the FROM clause entirely in HANA. It's cleaner and more standard. Both options work, but FROM DUMMY is more of a migration convenience.

Character Sets and String Types

Oracle has VARCHAR2 (byte-oriented) and NVARCHAR2 (Unicode). HANA has NVARCHAR (always Unicode) and VARCHAR (also Unicode, but treated as NVARCHAR).

Oracle Type	HANA Equivalent	Notes
`VARCHAR2(100)`	`NVARCHAR(100)`	For text data. HANA is Unicode-first.
`CHAR(10)`	`NCHAR(10)`	Fixed-length string. Rarely used in modern systems.
`NVARCHAR2(100)`	`NVARCHAR(100)`	Unicode in both. These are the same.
`CLOB`	`NCLOB`	Large character objects.

For most Callidus Commissions migrations, replace all VARCHAR2 with NVARCHAR.

Common Gotchas and Edge Cases

NULL Handling in Comparisons

This is identical in both databases, but it's easy to forget:

-- This returns NO rows, even if commission_amount is NULL
SELECT * FROM cs_transactions
WHERE commission_amount = NULL;

SELECT * FROM cs_transactions
WHERE commission_amount IS NULL;

Empty String vs NULL

Oracle treats empty strings as NULL. HANA treats them as empty strings (standard SQL behavior).

INSERT INTO t (name) VALUES ('');
SELECT * FROM t WHERE name IS NULL;
-- Returns the inserted row (Oracle treats '' as NULL)

INSERT INTO t (name) VALUES ('');
SELECT * FROM t WHERE name IS NULL;
-- Returns no rows (HANA treats '' as empty string, not NULL)

⚠️Silent data loss: If your Callidus logic relies on empty strings being NULL, you'll need to adjust WHERE clauses and business logic in HANA. Test this during migration validation.

Implicit Type Conversion

Oracle is very forgiving with type conversions. HANA is stricter.

SELECT * FROM cs_transactions
WHERE created_date = '2026-04-08';
-- Oracle converts string to DATE automatically

SELECT * FROM cs_transactions
WHERE created_date = TO_DATE('2026-04-08', 'YYYY-MM-DD');
-- Explicit conversion is safer in HANA

Migration Checklist: SQL Syntax

Grep for ROWNUM: grep -i "ROWNUM" *.sql — replace each with LIMIT or ROW_NUMBER().
Grep for DUAL: grep -i "FROM DUAL" *.sql — replace with FROM DUMMY or remove FROM clause.
Grep for NVL: grep -i "NVL(" *.sql — replace with IFNULL or COALESCE.
Grep for DECODE: grep -i "DECODE(" *.sql — replace each with CASE WHEN.
Grep for CONNECT BY: grep -i "CONNECT BY" *.sql — these need recursive CTE rewrite (lesson 3).
Grep for LISTAGG: grep -i "LISTAGG" *.sql — replace with STRING_AGG.
Check outer join syntax: Look for (+) in WHERE clauses — replace with ANSI JOIN syntax.
Check date arithmetic: Look for date_col + 1 or date_col - num — replace with ADD_DAYS, DAYS_BETWEEN.
Validate type conversions: Run queries on HANA. If you hit "cannot convert type" errors, add explicit CAST or TO_DATE/TO_VARCHAR.

Next: Hierarchical Queries →

The biggest rewrite: CONNECT BY PRIOR becomes recursive CTE. This is where Oracle and HANA truly diverge.

Lesson 03: Hierarchies →

Lesson 02: SQL Syntax Translation — Oracle to HANA

The Translation Table

Detailed Examples

ROWNUM → LIMIT

NVL → IFNULL or COALESCE

DECODE → CASE WHEN

Date Arithmetic: Implicit vs Explicit

String Aggregation: LISTAGG → STRING_AGG

Outer Join Syntax

DUAL and Dummy Queries

Character Sets and String Types

Common Gotchas and Edge Cases

NULL Handling in Comparisons

Empty String vs NULL

Implicit Type Conversion

Migration Checklist: SQL Syntax

Next: Hierarchical Queries →