SQL Fundamentals: The Language Every ICM Database Professional Must Master

Before you write a single Oracle PL/SQL procedure for Callidus Commissions or a SAP HANA Database calculation view for SAP SuccessFactors Incentive Management, you need SQL. Not surface-level SQL — the kind where you can explain why a LEFT JOIN returns NULLs, when to use a CTE vs a subquery, and what a window function is actually doing to your result set. This article builds that foundation with examples drawn directly from the ICM world.

What SQL Is (and Isn't)

SQL — Structured Query Language — is the standard language for relational databases. It is not a programming language in the traditional sense. You describe what data you want, not how to retrieve it. The database engine figures out the "how" via its query optimizer.

Both Oracle Database (which underpins Callidus Commissions) and SAP HANA Database (which underpins SAP SuccessFactors Incentive Management) implement SQL with minor dialect differences. Most of what you learn here applies to both. Where they diverge, I'll call it out.

The SELECT Statement

Everything starts here. The anatomy of a SELECT:

SELECT   p.NAME        AS PARTICIPANT_NAME,
         p.POSITION_ID,
         r.PERIOD_ID,
         r.RESULT_AMOUNT
FROM     CSC_PARTICIPANT p        -- table alias: p
JOIN     CSC_RESULTS    r         -- table alias: r
         ON r.PARTICIPANT_ID = p.ID
WHERE    r.PERIOD_ID = '2026-Q1'
  AND  r.RESULT_AMOUNT > 0
ORDER BY p.NAME ASC;

The execution order matters: FROM → JOIN → WHERE → SELECT → ORDER BY. The database processes your FROM and JOINs first, then filters with WHERE, then projects the columns you asked for in SELECT. This is why you cannot reference a SELECT alias in a WHERE clause — WHERE runs before SELECT.

JOINs: The Most Misunderstood Concept

JOINs combine rows from two tables based on a matching condition. Getting them wrong produces either missing rows (when you needed a LEFT JOIN) or inflated row counts (when you have a cartesian product).

-- INNER JOIN: only participants with results returned
SELECT p.NAME, r.RESULT_AMOUNT
FROM   CSC_PARTICIPANT p
JOIN   CSC_RESULTS    r ON r.PARTICIPANT_ID = p.ID;

-- LEFT JOIN: ALL participants returned, NULL result for those with no results
SELECT p.NAME,
       COALESCE(r.RESULT_AMOUNT, 0) AS RESULT_AMOUNT
FROM   CSC_PARTICIPANT p
LEFT JOIN CSC_RESULTS r ON r.PARTICIPANT_ID = p.ID;

GROUP BY and Aggregate Functions

GROUP BY collapses multiple rows into summary rows. Every column in your SELECT must either be in GROUP BY or wrapped in an aggregate function.

SELECT
  p.NAME,
  r.PERIOD_ID,
  SUM(r.RESULT_AMOUNT)   AS TOTAL_EARNINGS,
  COUNT(r.ID)            AS TRANSACTION_COUNT,
  AVG(r.RESULT_AMOUNT)   AS AVG_EARNING,
  MAX(r.RESULT_AMOUNT)   AS HIGHEST_SINGLE_EARNING
FROM   CSC_RESULTS r
JOIN   CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
GROUP BY p.NAME, r.PERIOD_ID
HAVING SUM(r.RESULT_AMOUNT) > 10000  -- filter on aggregated result
ORDER BY TOTAL_EARNINGS DESC;

HAVING filters after aggregation. WHERE filters before. Use HAVING when your filter condition involves an aggregate function.

CTEs: Making Complex Queries Readable

A Common Table Expression (CTE) is a named temporary result set defined at the top of your query with WITH. They make complex queries readable and maintainable — critical when you're writing a 60-line ICM reporting query that three people need to understand and modify.

WITH
-- Step 1: get quota targets
quota_base AS (
  SELECT PARTICIPANT_ID, PERIOD_ID,
         SUM(QUOTA_AMOUNT) AS TOTAL_QUOTA
  FROM   CSC_QUOTA
  GROUP BY PARTICIPANT_ID, PERIOD_ID
),
-- Step 2: get actual results
results_base AS (
  SELECT PARTICIPANT_ID, PERIOD_ID,
         SUM(RESULT_AMOUNT) AS TOTAL_EARNED
  FROM   CSC_RESULTS
  GROUP BY PARTICIPANT_ID, PERIOD_ID
)
-- Step 3: join and calculate attainment
SELECT
  p.NAME,
  r.PERIOD_ID,
  r.TOTAL_EARNED,
  q.TOTAL_QUOTA,
  ROUND(r.TOTAL_EARNED / NULLIF(q.TOTAL_QUOTA,0) * 100, 2) AS ATTAINMENT_PCT
FROM   results_base r
JOIN   quota_base   q ON q.PARTICIPANT_ID = r.PARTICIPANT_ID
                      AND q.PERIOD_ID     = r.PERIOD_ID
JOIN   CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
ORDER BY ATTAINMENT_PCT DESC;

Window Functions: Analytics Without Collapsing Rows

Window functions perform calculations across a set of rows related to the current row — without GROUP BY collapsing the result. This is your primary tool for ranking, running totals, and period-over-period comparisons in ICM reporting.

SELECT
  p.NAME,
  r.PERIOD_ID,
  r.RESULT_AMOUNT,
  -- Rank within each period (1 = highest earner)
  RANK() OVER (
    PARTITION BY r.PERIOD_ID
    ORDER BY r.RESULT_AMOUNT DESC
  ) AS EARNINGS_RANK,
  -- Running total of earnings within participant across periods
  SUM(r.RESULT_AMOUNT) OVER (
    PARTITION BY p.ID
    ORDER BY r.PERIOD_ID
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS RUNNING_TOTAL
FROM   CSC_RESULTS r
JOIN   CSC_PARTICIPANT p ON p.ID = r.PARTICIPANT_ID
ORDER BY r.PERIOD_ID, EARNINGS_RANK;

Key Syntax Differences: Oracle Database vs SAP HANA Database

What Comes Next

This is your foundation. The next two articles go platform-specific: Oracle Database and PL/SQL for Callidus Commissions work (Article 02), and SAP HANA Database SQL with its columnar architecture advantages for SAP SuccessFactors IM (Article 03). Article 04 then puts both side-by-side for migration teams.