HANA SQL Basics: Key Differences from Standard SQL

If you're migrating from Callidus Commissions on Oracle Database to SAP SuccessFactors IM on HANA, your SQL knowledge transfers — but not directly. HANA SQL is standards-compliant but has deliberate departures from Oracle syntax. These differences are not bugs; they're design choices. Understanding them is the foundation for writing correct, efficient HANA queries.

The Core Syntax Differences

Oracle developers most often stumble on these HANA SQL features:

These aren't cosmetic. An Oracle script that runs without errors on HANA will produce incorrect results if you don't address these differences.

LIMIT vs ROWNUM: The Top-N Query

Oracle uses ROWNUM to restrict result sets. HANA uses LIMIT (or OFFSET...LIMIT for pagination).

Oracle: Top 10 Results by Amount

-- Oracle: ROWNUM is a pseudo-column assigned during query execution
SELECT PARTICIPANT_ID, RESULT_AMOUNT
FROM   (
  SELECT PARTICIPANT_ID, RESULT_AMOUNT
  FROM   CSC_RESULTS
  WHERE  PERIOD_ID = '2026-Q1'
  ORDER BY RESULT_AMOUNT DESC
)
WHERE  ROWNUM <= 10;

Oracle requires a subquery because ROWNUM is evaluated before ORDER BY. Without the subquery, you'd get 10 arbitrary rows, not the 10 largest amounts.

HANA: Simpler Approach with LIMIT

-- HANA: LIMIT is applied after ORDER BY
SELECT PARTICIPANT_ID, RESULT_AMOUNT
FROM   CSC_RESULTS
WHERE  PERIOD_ID = '2026-Q1'
ORDER BY RESULT_AMOUNT DESC
LIMIT 10;

HANA evaluates ORDER BY first, then applies LIMIT. No subquery needed. This is cleaner and more intuitive.

Pagination with OFFSET

To skip rows (pagination), use OFFSET:

-- Get results 11-20 (skip first 10)
SELECT PARTICIPANT_ID, RESULT_AMOUNT
FROM   CSC_RESULTS
WHERE  PERIOD_ID = '2026-Q1'
ORDER BY RESULT_AMOUNT DESC
LIMIT 10 OFFSET 10;

Date Functions: CURRENT_DATE and CURRENT_TIMESTAMP

Oracle's SYSDATE doesn't exist on HANA. Instead, use CURRENT_DATE or CURRENT_TIMESTAMP.

Common HANA Date Patterns

-- Current date and time
SELECT
  CURRENT_DATE AS TODAY,
  CURRENT_TIMESTAMP AS NOW;

-- Add/subtract days
SELECT
  CURRENT_DATE + 30 AS THIRTY_DAYS_FROM_NOW,
  CURRENT_DATE - 7 AS LAST_WEEK;

-- Add/subtract months (HANA-specific function)
SELECT
  ADD_MONTHS(CURRENT_DATE, 3) AS THREE_MONTHS_AHEAD,
  ADD_MONTHS(CURRENT_DATE, -1) AS LAST_MONTH;

-- Extract year, month, day
SELECT
  YEAR(CURRENT_DATE) AS CURRENT_YEAR,
  MONTH(CURRENT_DATE) AS CURRENT_MONTH,
  EXTRACT(YEAR FROM CURRENT_DATE) AS YEAR_VIA_EXTRACT;

Note the HANA-specific functions: ADD_MONTHS, ADD_DAYS, DAYS_BETWEEN. These are more intuitive than Oracle's date arithmetic.

NULL Handling: IFNULL vs NVL

Oracle's NVL doesn't exist on HANA. Use IFNULL (HANA-specific) or COALESCE (ANSI standard, works on both).

-- IFNULL: HANA-native, simple two-argument syntax
SELECT
  PARTICIPANT_ID,
  IFNULL(RESULT_AMOUNT, 0) AS RESULT_AMT,
  IFNULL(COMMISSION_RATE, 0.05) AS RATE
FROM   CSC_RESULTS;

-- COALESCE: ANSI standard, works on multiple arguments
SELECT
  PARTICIPANT_ID,
  COALESCE(BONUS, ADJUSTMENT, 0) AS FINAL_AMOUNT
FROM   CSC_RESULTS;

-- Checking for NULL: IS NULL works as expected
SELECT PARTICIPANT_ID
FROM   CSC_RESULTS
WHERE  RESULT_AMOUNT IS NULL;

Prefer IFNULL in SuccessFactors IM work — it's clearer and consistent with SAP's style. Use COALESCE only when you need multiple fallback values.

The DUMMY Table (or Lack Thereof)

Oracle requires FROM DUAL for queries without a real table. HANA has FROM DUMMY, but you can often omit FROM entirely.

-- Approach 1: Omit FROM entirely (preferred on HANA)
SELECT CURRENT_DATE, 1 + 1 AS MATH;

-- Approach 2: Explicitly use FROM DUMMY
SELECT CURRENT_DATE, 1 + 1 AS MATH FROM DUMMY;

-- Both return the same result. Prefer the first for readability.

Omitting FROM is cleaner and more efficient on HANA. Use it for constant expressions and function calls that don't reference tables.

String and Data Types

HANA uses different type names than Oracle. This matters when creating tables, defining procedure parameters, or casting values.

String Types

-- Oracle: VARCHAR2, CHAR
-- HANA: NVARCHAR (Unicode-native, preferred)

-- NVARCHAR: Unicode string, variable length. Use this for names, text.
CREATE TABLE participant_temp (
  NAME NVARCHAR(200),
  EMAIL NVARCHAR(100)
);

-- Cast to NVARCHAR
SELECT CAST(PARTICIPANT_ID AS NVARCHAR(20)) AS ID_STR;

Numeric Types

-- Oracle: NUMBER(18,2) for currency
-- HANA: DECIMAL(18,2) for fixed-point, FLOAT/DOUBLE for floating-point

-- Use DECIMAL for money amounts (no rounding errors)
SELECT
  RESULT_AMOUNT AS AMOUNT_DECIMAL,
  CAST(RESULT_AMOUNT AS DECIMAL(18,2)) AS AMOUNT_CAST
FROM   CSC_RESULTS;

Date and Datetime Types

-- Oracle: DATE stores both date and time
-- HANA: DATE (date only), TIMESTAMP (date and time)

SELECT
  PERIOD_START_DATE,  -- DATE type: YYYY-MM-DD
  CURRENT_TIMESTAMP,  -- TIMESTAMP: includes time
  CAST(CURRENT_TIMESTAMP AS DATE) AS TODAY_DATE
FROM   CSC_PERIOD;

Your First HANA Queries on CSC_ Tables

Let's write some real queries against SAP SuccessFactors IM data. Start with these two foundational tables:

CSC_PARTICIPANT: Participant Master Data

CSC_PARTICIPANT contains all participants in the system: sales reps, managers, admins.

-- Get first 10 active participants
SELECT
  ID,
  NAME,
  EMAIL,
  STATUS,
  PARENT_ID,
  HIRE_DATE
FROM   CSC_PARTICIPANT
WHERE  STATUS = 'ACTIVE'
LIMIT 10;

-- Count active vs inactive
SELECT
  STATUS,
  COUNT(*) AS PARTICIPANT_COUNT
FROM   CSC_PARTICIPANT
GROUP BY STATUS;

CSC_RESULTS: Transaction Results

CSC_RESULTS contains every transaction (order, pipeline, deal) attributed to a participant.

-- Total results by period
SELECT
  PERIOD_ID,
  COUNT(*) AS RESULT_COUNT,
  SUM(RESULT_AMOUNT) AS TOTAL_AMOUNT,
  ROUND(AVG(RESULT_AMOUNT), 2) AS AVG_AMOUNT
FROM   CSC_RESULTS
GROUP BY PERIOD_ID
ORDER BY PERIOD_ID DESC;

-- Top 5 participants by total earnings in Q1 2026
SELECT
  p.NAME,
  SUM(r.RESULT_AMOUNT) AS TOTAL_EARNED
FROM   CSC_RESULTS r
JOIN   CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
WHERE  r.PERIOD_ID = '2026-Q1'
GROUP BY p.NAME
ORDER BY TOTAL_EARNED DESC
LIMIT 5;

Common Pitfalls and How to Avoid Them

Pitfall 1: Using ROWNUM Instead of LIMIT

Wrong: WHERE ROWNUM <= 10

Right: LIMIT 10

Pitfall 2: SELECT * in Production Queries

Wrong: SELECT * FROM CSC_RESULTS WHERE PERIOD_ID = '2026-Q1'

Right: SELECT PARTICIPANT_ID, RESULT_AMOUNT FROM CSC_RESULTS WHERE PERIOD_ID = '2026-Q1'

Pitfall 3: Using SYSDATE Instead of CURRENT_DATE

Wrong: WHERE RESULT_DATE > SYSDATE - 30

Right: WHERE RESULT_DATE > CURRENT_DATE - 30

Pitfall 4: NVL Instead of IFNULL

Wrong: NVL(COMMISSION_RATE, 0.05)

Right: IFNULL(COMMISSION_RATE, 0.05)

Pitfall 5: Forgetting Case Sensitivity is Case-Insensitive

Note: These all work identically:

All three are identical because unquoted identifiers are case-insensitive. Stick to uppercase for consistency.

Key Takeaways

• LIMIT replaces ROWNUM. No subquery needed for top-N queries.
• CURRENT_DATE and CURRENT_TIMESTAMP replace SYSDATE.
• IFNULL or COALESCE replace NVL.
• NVARCHAR replaces VARCHAR2. DECIMAL replaces NUMBER for fixed-point.
• FROM DUMMY is optional; omit it for readability.
• CSC_PARTICIPANT and CSC_RESULTS are your starting tables. Learn their structure and common joins.
• Unquoted identifiers are case-insensitive. Use uppercase consistently.