HANA Built-In Functions for ICM Work

TL;DR

HANA functions fall into categories: string (SUBSTRING, UPPER, TRIM), date (ADD_DAYS, ADD_MONTHS, EXTRACT), numeric (ROUND, FLOOR, ABS), and NULL handling (IFNULL, COALESCE). Most mirror standard SQL with HANA-specific extensions.

Real ICM work relies on date functions (period calculations), numeric functions (quota percentages, attainment %), and string functions (name formatting for reports).

CASE expressions replace IF/ELSE for conditional logic in SQL. Use CASE WHEN for tiering (e.g., performance brackets), status overrides, and derivations.

SAP HANA has a comprehensive function library that mirrors ANSI SQL but with extensions tailored for business analytics. In SuccessFactors IM work, you'll use a small set of functions repeatedly: date arithmetic for period logic, numeric functions for percentage calculations, string functions for report formatting. This lesson covers the 80% you'll use 100% of the time.

String Functions

String functions manipulate text. Common use cases in SuccessFactors IM: extracting name components, formatting participant IDs, cleaning text for reports.

Essential String Functions

Function	Syntax	Use Case
SUBSTRING	SUBSTRING(str, start, length)	Extract part of a string (e.g., first 3 chars of code)
UPPER	UPPER(str)	Convert to uppercase (e.g., normalize names)
LOWER	LOWER(str)	Convert to lowercase (e.g., emails)
TRIM	TRIM(str)	Remove leading/trailing whitespace
LENGTH	LENGTH(str)	String length (useful for validation)
CONCAT	CONCAT(str1, str2, ...)	Concatenate multiple strings
REPLACE	REPLACE(str, find, replace)	Find and replace substring
LEFT / RIGHT	LEFT(str, n) / RIGHT(str, n)	Extract first/last n characters

String Function Examples for SuccessFactors IM

-- Normalize participant names for reporting
SELECT
  UPPER(TRIM(NAME)) AS NAME_NORMALIZED,
  LENGTH(NAME) AS NAME_LENGTH
FROM   CSC_PARTICIPANT
WHERE  LENGTH(TRIM(NAME)) > 0;

-- Extract sales region from participant code (first 2 chars)
SELECT
  ID,
  NAME,
  LEFT(ID, 2) AS REGION_CODE
FROM   CSC_PARTICIPANT;

-- Clean order numbers: remove leading zeros for matching
SELECT
  REPLACE(ORDER_ID, '0', '') AS ORDER_ID_CLEAN
FROM   CSC_RESULTS;

-- Build display name: LAST, FIRST format
SELECT
  CONCAT(LAST_NAME, ', ', FIRST_NAME) AS DISPLAY_NAME
FROM   CSC_PARTICIPANT;

Date and Time Functions

Date functions are critical in SuccessFactors IM. Commission periods, result dates, and cutoff dates drive all reporting logic.

Essential Date Functions

Function	Syntax	Use Case
CURRENT_DATE	CURRENT_DATE	Today's date
CURRENT_TIMESTAMP	CURRENT_TIMESTAMP	Current date and time
ADD_DAYS	ADD_DAYS(date, n)	Add/subtract days
ADD_MONTHS	ADD_MONTHS(date, n)	Add/subtract months
DAYS_BETWEEN	DAYS_BETWEEN(date1, date2)	Days between two dates
EXTRACT	EXTRACT(YEAR\|MONTH\|DAY FROM date)	Extract date component
YEAR / MONTH / DAY	YEAR(date) / MONTH(date) / DAY(date)	Extract component (shorthand)
TO_DATE	TO_DATE(str, format)	Parse string to date
TO_VARCHAR	TO_VARCHAR(date, format)	Format date as string

Date Function Examples for SuccessFactors IM

-- Calculate days since transaction for aging results
SELECT
  RESULT_ID,
  RESULT_DATE,
  DAYS_BETWEEN(RESULT_DATE, CURRENT_DATE) AS DAYS_AGO
FROM   CSC_RESULTS
WHERE  DAYS_BETWEEN(RESULT_DATE, CURRENT_DATE) > 30;

-- Period end date: Add days to period start
SELECT
  ID,
  START_DATE,
  ADD_DAYS(START_DATE, 90) AS QUARTER_END
FROM   CSC_PERIOD
WHERE  PERIOD_TYPE = 'QUARTER';

-- Next fiscal year start (add 12 months to current)
SELECT
  ADD_MONTHS(CURRENT_DATE, 12) AS FISCAL_YEAR_START;

-- Extract year for filtering by fiscal year
SELECT
  PARTICIPANT_ID,
  SUM(RESULT_AMOUNT) AS TOTAL_EARNED
FROM   CSC_RESULTS
WHERE  YEAR(RESULT_DATE) = 2026
GROUP BY PARTICIPANT_ID;

-- Parse period string to date for comparisons
SELECT
  TO_DATE('2026-Q1-START', 'YYYY-QX-TEXT') AS PARSED_DATE;

-- Format date for display in reports
SELECT
  TO_VARCHAR(RESULT_DATE, 'YYYY-MM-DD') AS RESULT_DATE_STR
FROM   CSC_RESULTS;

Numeric Functions

Numeric functions handle calculations: rounding percentages, flooring/ceiling for quota adjustments, absolute values for variance analysis.

Essential Numeric Functions

Function	Syntax	Use Case
ROUND	ROUND(number, decimals)	Round to N decimal places (percentages, currency)
FLOOR	FLOOR(number)	Round down to nearest integer
CEIL	CEIL(number)	Round up to nearest integer
ABS	ABS(number)	Absolute value (variance analysis)
MOD	MOD(a, b)	Modulo / remainder
POWER	POWER(base, exponent)	Exponentiation

Numeric Function Examples for SuccessFactors IM

-- Calculate attainment percentage (rounded to 2 decimals)
SELECT
  PARTICIPANT_ID,
  PERIOD_ID,
  SUM(RESULT_AMOUNT) AS EARNED,
  SUM(QUOTA_AMOUNT) AS QUOTA,
  ROUND(
    SUM(RESULT_AMOUNT) / NULLIF(SUM(QUOTA_AMOUNT), 0) * 100,
    2
  ) AS ATTAINMENT_PCT
FROM   CSC_RESULTS
GROUP BY PARTICIPANT_ID, PERIOD_ID;

-- Variance analysis: absolute difference
SELECT
  PARTICIPANT_ID,
  ABS(SUM(RESULT_AMOUNT) - SUM(FORECAST_AMOUNT)) AS FORECAST_VARIANCE
FROM   CSC_RESULTS
GROUP BY PARTICIPANT_ID;

-- Quota enforcement: even distribution across months
SELECT
  PARTICIPANT_ID,
  PERIOD_ID,
  CEIL(QUOTA_AMOUNT / 3) AS MONTHLY_QUOTA
FROM   CSC_QUOTA
WHERE  PERIOD_TYPE = 'QUARTER';

-- Tier multiplier (e.g., square the attainment % for accelerated plans)
SELECT
  PARTICIPANT_ID,
  ROUND(
    POWER(ATTAINMENT_PCT / 100, 2) * 100,
    2
  ) AS ACCELERATED_PAYOUT_PCT
FROM   CSC_RESULTS;

NULL Handling and NULLIF

IFNULL handles missing values. NULLIF does the opposite: it converts a specific value to NULL — useful when you want to treat zero as missing (e.g., avoid division by zero).

NULLIF: Convert a Value to NULL

-- Prevent division by zero: NULLIF(x, 0) returns NULL if x is 0
SELECT
  PARTICIPANT_ID,
  RESULT_AMOUNT,
  QUOTA_AMOUNT,
  ROUND(
    RESULT_AMOUNT / NULLIF(QUOTA_AMOUNT, 0) * 100,
    2
  ) AS ATTAINMENT_PCT
FROM   CSC_RESULTS
WHERE  QUOTA_AMOUNT IS NOT NULL;

-- Treat zero bonus as NULL for reporting purposes
SELECT
  PARTICIPANT_ID,
  IFNULL(NULLIF(BONUS_AMOUNT, 0), 0) AS BONUS
FROM   CSC_PAYMENT;

Type Conversion Functions

Convert between data types explicitly. Useful when joining on mismatched types, comparing strings vs numbers, or formatting output.

Type Conversion

-- CAST function: explicit type conversion
SELECT
  CAST(PARTICIPANT_ID AS NVARCHAR(20)) AS ID_STR,
  CAST(RESULT_AMOUNT AS DECIMAL(18,2)) AS AMOUNT_DECIMAL,
  CAST(CURRENT_DATE AS NVARCHAR(10)) AS DATE_STR;

-- TO_NVARCHAR: Convert number to string
SELECT
  TO_NVARCHAR(RESULT_AMOUNT, '999,999.99') AS AMOUNT_FORMATTED
FROM   CSC_RESULTS;

-- TO_DECIMAL: Convert string to number (with error handling)
SELECT
  TO_DECIMAL('1234.56', 18, 2) AS PARSED_AMOUNT;

CASE Expressions: Conditional Logic in SQL

CASE replaces IF/ELSE. Use it for conditional values, tiering, status overrides, or data derivations.

CASE WHEN Syntax and Examples

-- Basic CASE WHEN: Performance tier based on attainment
SELECT
  PARTICIPANT_ID,
  ATTAINMENT_PCT,
  CASE
    WHEN ATTAINMENT_PCT >= 100 THEN 'QUOTA_ATTAINED'
    WHEN ATTAINMENT_PCT >= 75 THEN 'ON_TRACK'
    WHEN ATTAINMENT_PCT >= 50 THEN 'AT_RISK'
    ELSE 'CRITICAL'
  END AS PERFORMANCE_TIER
FROM   CSC_RESULTS;

-- Payout multiplier: accelerated commission for high attainment
SELECT
  PARTICIPANT_ID,
  BASE_COMMISSION,
  CASE
    WHEN ATTAINMENT_PCT >= 150 THEN BASE_COMMISSION * 1.5
    WHEN ATTAINMENT_PCT >= 100 THEN BASE_COMMISSION * 1.25
    WHEN ATTAINMENT_PCT >= 75 THEN BASE_COMMISSION
    ELSE 0
  END AS FINAL_PAYOUT
FROM   CSC_RESULTS;

-- Simple expression: CASE without WHEN (like ternary operator)
SELECT
  PARTICIPANT_ID,
  STATUS,
  CASE WHEN STATUS = 'ACTIVE' THEN 1 ELSE 0 END AS IS_ACTIVE_FLAG
FROM   CSC_PARTICIPANT;

💡CASE tip: CASE evaluates conditions in order and stops at the first match. Order your WHEN clauses from most specific to most general. The ELSE clause is optional but recommended for clarity.

Putting It Together: A Real SuccessFactors IM Query

Here's a query that combines multiple functions for a commission report:

-- Monthly attainment and commission projection
SELECT
  UPPER(TRIM(p.NAME)) AS PARTICIPANT_NAME,
  r.PERIOD_ID,
  ROUND(SUM(r.RESULT_AMOUNT), 2) AS EARNED,
  ROUND(SUM(q.QUOTA_AMOUNT), 2) AS QUOTA,
  ROUND(
    SUM(r.RESULT_AMOUNT) /
    NULLIF(SUM(q.QUOTA_AMOUNT), 0) * 100,
    2
  ) AS ATTAINMENT_PCT,
  CASE
    WHEN SUM(r.RESULT_AMOUNT) /
         NULLIF(SUM(q.QUOTA_AMOUNT), 0) >= 1.0 THEN 'QUOTA_MET'
    WHEN SUM(r.RESULT_AMOUNT) /
         NULLIF(SUM(q.QUOTA_AMOUNT), 0) >= 0.75 THEN 'ON_TRACK'
    ELSE 'AT_RISK'
  END AS STATUS
FROM   CSC_RESULTS r
JOIN   CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
LEFT JOIN CSC_QUOTA q
           ON q.PARTICIPANT_ID = r.PARTICIPANT_ID
           AND q.PERIOD_ID = r.PERIOD_ID
WHERE  r.PERIOD_ID = '2026-Q1'
  AND  p.STATUS = 'ACTIVE'
GROUP BY p.NAME, r.PERIOD_ID
ORDER BY ATTAINMENT_PCT DESC;

This query demonstrates:

String function: UPPER(TRIM(NAME)) for normalized names
Numeric functions: ROUND() for clean percentages, NULLIF() for safe division
Date context: filtering by PERIOD_ID
CASE expression: tiering based on attainment
Aggregation: SUM with GROUP BY for period-level totals

Key Takeaways

String functions clean and format text: TRIM, UPPER, LOWER, SUBSTRING, CONCAT.
Date functions handle period logic: ADD_DAYS, ADD_MONTHS, DAYS_BETWEEN, EXTRACT.
Numeric functions calculate percentages and adjustments: ROUND, FLOOR, CEIL, ABS.
NULLIF prevents division by zero. IFNULL provides defaults for missing values.
CASE expressions replace IF/ELSE for conditional logic: tiering, status overrides, derivations.
Combine functions strategically: ROUND(SUM(...) / NULLIF(..., 0)) for safe percentage calculation.
Always specify decimal places with ROUND() to avoid floating-point issues in financial data.

Next: Hierarchical Queries →

Lesson 4 covers recursive CTEs for building org trees and calculating rollup totals.

Go to Lesson 4 →