Advanced Techniques

Master advanced SuiteQL features including CASE expressions, Window Functions, CTEs, and UNION operations.

---

CASE Expressions

Conditional logic within your SQL queries.

Simple CASE

Match specific values:

SELECT
    id,
    tranid,
    status,
    CASE status
        WHEN 'A' THEN 'Pending Approval'
        WHEN 'B' THEN 'Pending Fulfillment'
        WHEN 'C' THEN 'Closed'
        WHEN 'D' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_name
FROM transaction
WHERE type = 'SalesOrd'
LIMIT 20

Searched CASE

Evaluate conditions:

SELECT
    id,
    companyname,
    balance,
    CASE
        WHEN balance >= 100000 THEN 'Platinum'
        WHEN balance >= 50000 THEN 'Gold'
        WHEN balance >= 10000 THEN 'Silver'
        WHEN balance > 0 THEN 'Bronze'
        ELSE 'No Balance'
    END AS tier
FROM customer
WHERE isinactive = 'F'
ORDER BY balance DESC

Sample Result:

id	companyname	balance	tier
101	Enterprise Corp	150,000	Platinum
102	Global Tech	75,000	Gold
103	Small Biz	5,000	Bronze

CASE in Aggregation

Conditional counting and summing:

-- Count orders by status
SELECT
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN status = 'B' THEN 1 ELSE 0 END) AS approved,
    SUM(CASE WHEN status = 'C' THEN 1 ELSE 0 END) AS closed,
    SUM(CASE WHEN status = 'D' THEN 1 ELSE 0 END) AS cancelled
FROM transaction
WHERE type = 'SalesOrd'
  AND trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')

Test Case: Aging Report with CASE

-- AR Aging buckets
SELECT
    c.companyname,
    SUM(t.amountremaining) AS total_ar,
    SUM(CASE WHEN SYSDATE - t.duedate <= 0 THEN t.amountremaining ELSE 0 END) AS current_amt,
    SUM(CASE WHEN SYSDATE - t.duedate BETWEEN 1 AND 30 THEN t.amountremaining ELSE 0 END) AS days_1_30,
    SUM(CASE WHEN SYSDATE - t.duedate BETWEEN 31 AND 60 THEN t.amountremaining ELSE 0 END) AS days_31_60,
    SUM(CASE WHEN SYSDATE - t.duedate BETWEEN 61 AND 90 THEN t.amountremaining ELSE 0 END) AS days_61_90,
    SUM(CASE WHEN SYSDATE - t.duedate > 90 THEN t.amountremaining ELSE 0 END) AS over_90
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'CustInvc'
  AND t.amountremaining > 0
GROUP BY c.id, c.companyname
ORDER BY total_ar DESC

Nested CASE

SELECT
    id,
    companyname,
    CASE
        WHEN isinactive = 'T' THEN 'Inactive'
        WHEN balance > 0 THEN
            CASE
                WHEN creditlimit > 0 AND balance > creditlimit THEN 'Over Credit'
                ELSE 'Active - Good'
            END
        ELSE 'Active - No Balance'
    END AS customer_status
FROM customer

---

Window Functions

Perform calculations across rows related to the current row without grouping.

Illustration

ROW_NUMBER

Assign sequential numbers to rows:

-- Number all invoices by date
SELECT
    ROW_NUMBER() OVER (ORDER BY trandate DESC) AS row_num,
    id,
    tranid,
    trandate,
    amount
FROM transaction
WHERE type = 'CustInvc'
LIMIT 20

Sample Result:

row_num	id	tranid	trandate	amount
1	501	INV-501	2024-12-15	5,000
2	500	INV-500	2024-12-14	3,500
3	499	INV-499	2024-12-13	8,200

PARTITION BY

Number within groups:

-- Number invoices per customer (restart numbering for each customer)
SELECT
    c.companyname,
    t.tranid,
    t.trandate,
    t.amount,
    ROW_NUMBER() OVER (
        PARTITION BY t.entity
        ORDER BY t.trandate DESC
    ) AS invoice_num_for_customer
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'CustInvc'
ORDER BY c.companyname, t.trandate DESC

Sample Result:

companyname	tranid	trandate	amount	invoice_num_for_customer
Acme Corp	INV-501	2024-12-15	5,000	1
Acme Corp	INV-485	2024-11-20	3,200	2
Acme Corp	INV-460	2024-10-05	4,100	3
Beta Inc	INV-498	2024-12-10	2,500	1
Beta Inc	INV-472	2024-11-01	1,800	2

RANK and DENSE_RANK

Handle ties differently:

-- Rank customers by sales volume
SELECT
    c.companyname,
    SUM(t.amount) AS total_sales,
    RANK() OVER (ORDER BY SUM(t.amount) DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY SUM(t.amount) DESC) AS dense_rank
FROM transaction t
INNER JOIN customer c ON t.entity = c.id
WHERE t.type = 'SalesOrd'
GROUP BY c.id, c.companyname
ORDER BY total_sales DESC
LIMIT 10

companyname	total_sales	rank	dense_rank
Acme Corp	100,000	1	1
Beta Inc	100,000	1	1
Gamma Ltd	85,000	3	2
Delta Co	70,000	4	3

Running Totals with SUM OVER

-- Running total of sales by date
SELECT
    trandate,
    amount,
    SUM(amount) OVER (ORDER BY trandate) AS running_total,
    SUM(amount) OVER (
        ORDER BY trandate
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7_day
FROM transaction
WHERE type = 'SalesOrd'
  AND trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
ORDER BY trandate

LAG and LEAD

Access previous/next row values:

-- Compare each order to previous order
SELECT
    tranid,
    trandate,
    amount,
    LAG(amount, 1) OVER (ORDER BY trandate) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY trandate) AS difference,
    LEAD(amount, 1) OVER (ORDER BY trandate) AS next_amount
FROM transaction
WHERE type = 'SalesOrd'
  AND entity = 123  -- Specific customer
ORDER BY trandate

Test Case: Month-over-Month Comparison

-- Monthly sales with MoM comparison
WITH MonthlySales AS (
    SELECT
        TO_CHAR(trandate, 'YYYY-MM') AS month,
        SUM(amount) AS sales
    FROM transaction
    WHERE type = 'SalesOrd'
      AND trandate >= ADD_MONTHS(SYSDATE, -12)
    GROUP BY TO_CHAR(trandate, 'YYYY-MM')
)
SELECT
    month,
    sales,
    LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales,
    ROUND(
        (sales - LAG(sales, 1) OVER (ORDER BY month)) /
        LAG(sales, 1) OVER (ORDER BY month) * 100,
        2
    ) AS mom_growth_pct
FROM MonthlySales
ORDER BY month

Test Case: Top N per Group

-- Top 3 items per category by sales
SELECT *
FROM (
    SELECT
        i.class AS category,
        i.itemid,
        i.displayname,
        SUM(tl.amount) AS total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY i.class
            ORDER BY SUM(tl.amount) DESC
        ) AS rank_in_category
    FROM item i
    INNER JOIN transactionline tl ON i.id = tl.item
    INNER JOIN transaction t ON tl.transaction = t.id
    WHERE t.type = 'SalesOrd'
    GROUP BY i.id, i.class, i.itemid, i.displayname
) ranked
WHERE rank_in_category <= 3
ORDER BY category, rank_in_category

---

Common Table Expressions (CTEs)

Named subqueries that make complex queries more readable.

Basic CTE Syntax

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name

Test Case: Customer Summary with CTE

-- Customer analysis using CTEs
WITH CustomerOrders AS (
    SELECT
        entity,
        COUNT(*) AS order_count,
        SUM(amount) AS total_sales,
        MAX(trandate) AS last_order
    FROM transaction
    WHERE type = 'SalesOrd'
    GROUP BY entity
),
CustomerTier AS (
    SELECT
        entity,
        order_count,
        total_sales,
        last_order,
        CASE
            WHEN total_sales >= 100000 THEN 'Enterprise'
            WHEN total_sales >= 50000 THEN 'Mid-Market'
            WHEN total_sales >= 10000 THEN 'SMB'
            ELSE 'Small'
        END AS tier
    FROM CustomerOrders
)
SELECT
    c.companyname,
    c.email,
    ct.order_count,
    ct.total_sales,
    ct.last_order,
    ct.tier
FROM customer c
INNER JOIN CustomerTier ct ON c.id = ct.entity
ORDER BY ct.total_sales DESC
LIMIT 50

Multiple CTEs

-- Sales analysis with multiple CTEs
WITH
CurrentYearSales AS (
    SELECT entity, SUM(amount) AS cy_sales
    FROM transaction
    WHERE type = 'SalesOrd'
      AND EXTRACT(YEAR FROM trandate) = EXTRACT(YEAR FROM SYSDATE)
    GROUP BY entity
),
PriorYearSales AS (
    SELECT entity, SUM(amount) AS py_sales
    FROM transaction
    WHERE type = 'SalesOrd'
      AND EXTRACT(YEAR FROM trandate) = EXTRACT(YEAR FROM SYSDATE) - 1
    GROUP BY entity
),
Combined AS (
    SELECT
        COALESCE(cy.entity, py.entity) AS entity,
        NVL(cy.cy_sales, 0) AS current_year,
        NVL(py.py_sales, 0) AS prior_year
    FROM CurrentYearSales cy
    FULL OUTER JOIN PriorYearSales py ON cy.entity = py.entity
)
SELECT
    c.companyname,
    cb.current_year,
    cb.prior_year,
    cb.current_year - cb.prior_year AS yoy_change,
    ROUND(
        CASE WHEN cb.prior_year > 0
        THEN (cb.current_year - cb.prior_year) / cb.prior_year * 100
        ELSE 0 END,
        2
    ) AS yoy_pct
FROM Combined cb
INNER JOIN customer c ON cb.entity = c.id
ORDER BY cb.current_year DESC
LIMIT 50

Recursive CTE for Hierarchies

-- Employee hierarchy (org chart)
WITH RECURSIVE OrgChart AS (
    -- Base case: top-level employees (no supervisor)
    SELECT
        id,
        entityid,
        title,
        supervisor,
        1 AS level,
        entityid AS path
    FROM employee
    WHERE supervisor IS NULL
      AND isinactive = 'F'

    UNION ALL

    -- Recursive case: employees with supervisors
    SELECT
        e.id,
        e.entityid,
        e.title,
        e.supervisor,
        oc.level + 1,
        oc.path || ' > ' || e.entityid
    FROM employee e
    INNER JOIN OrgChart oc ON e.supervisor = oc.id
    WHERE e.isinactive = 'F'
)
SELECT
    level,
    LPAD(' ', (level - 1) * 4) || entityid AS employee,
    title,
    path
FROM OrgChart
ORDER BY path

---

UNION and Set Operations

Combine results from multiple queries.

UNION (Remove Duplicates)

-- All entities (customers and vendors)
SELECT id, companyname, 'Customer' AS type
FROM customer
WHERE isinactive = 'F'

UNION

SELECT id, companyname, 'Vendor' AS type
FROM vendor
WHERE isinactive = 'F'

ORDER BY companyname

UNION ALL (Keep Duplicates)

-- All transactions (faster, no duplicate removal)
SELECT tranid, trandate, amount, 'Sale' AS source
FROM transaction
WHERE type = 'SalesOrd'

UNION ALL

SELECT tranid, trandate, amount, 'Invoice' AS source
FROM transaction
WHERE type = 'CustInvc'

ORDER BY trandate DESC
LIMIT 100

UNION Performance

Operation	Behavior	Performance
UNION	Removes duplicates	Slower (requires sort)
UNION ALL	Keeps all rows	Faster

Test Case: Combined Transaction Report

-- All AR and AP transactions
WITH AllTransactions AS (
    -- Accounts Receivable (Customer)
    SELECT
        t.id,
        t.tranid,
        t.trandate,
        c.companyname AS entity_name,
        t.amount,
        'AR' AS category
    FROM transaction t
    INNER JOIN customer c ON t.entity = c.id
    WHERE t.type IN ('CustInvc', 'CustPymt')

    UNION ALL

    -- Accounts Payable (Vendor)
    SELECT
        t.id,
        t.tranid,
        t.trandate,
        v.companyname AS entity_name,
        t.amount,
        'AP' AS category
    FROM transaction t
    INNER JOIN vendor v ON t.entity = v.id
    WHERE t.type IN ('VendBill', 'VendPymt')
)
SELECT *
FROM AllTransactions
WHERE trandate >= TO_DATE('2024-01-01', 'YYYY-MM-DD')
ORDER BY trandate DESC
LIMIT 100

INTERSECT (Common Rows)

-- Customers who are also vendors
SELECT companyname FROM customer
INTERSECT
SELECT companyname FROM vendor

MINUS/EXCEPT (Difference)

-- Customers who are NOT vendors
SELECT companyname FROM customer
MINUS
SELECT companyname FROM vendor

---

Combining Techniques

Test Case: Advanced Sales Report

-- Comprehensive sales dashboard query
WITH
MonthlySales AS (
    SELECT
        TO_CHAR(trandate, 'YYYY-MM') AS month,
        entity,
        SUM(amount) AS sales
    FROM transaction
    WHERE type = 'SalesOrd'
      AND trandate >= ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12)
    GROUP BY TO_CHAR(trandate, 'YYYY-MM'), entity
),
CustomerRanking AS (
    SELECT
        entity,
        SUM(sales) AS total_sales,
        RANK() OVER (ORDER BY SUM(sales) DESC) AS sales_rank
    FROM MonthlySales
    GROUP BY entity
),
MonthlyTrend AS (
    SELECT
        ms.month,
        ms.entity,
        ms.sales,
        LAG(ms.sales) OVER (PARTITION BY ms.entity ORDER BY ms.month) AS prev_month
    FROM MonthlySales ms
)
SELECT
    c.companyname,
    cr.sales_rank,
    cr.total_sales,
    CASE
        WHEN cr.sales_rank <= 10 THEN 'Top 10'
        WHEN cr.sales_rank <= 50 THEN 'Top 50'
        ELSE 'Other'
    END AS tier,
    mt.month AS latest_month,
    mt.sales AS latest_sales,
    mt.prev_month AS prior_month_sales,
    ROUND(
        CASE WHEN mt.prev_month > 0
        THEN (mt.sales - mt.prev_month) / mt.prev_month * 100
        ELSE 0 END,
        2
    ) AS mom_growth
FROM CustomerRanking cr
INNER JOIN customer c ON cr.entity = c.id
INNER JOIN MonthlyTrend mt ON cr.entity = mt.entity
WHERE mt.month = (SELECT MAX(month) FROM MonthlySales)
ORDER BY cr.sales_rank
LIMIT 50

---

Quick Reference

CASE Syntax

-- Simple CASE
CASE column WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default END

-- Searched CASE
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default END

Window Function Syntax

function() OVER (
    [PARTITION BY columns]
    [ORDER BY columns]
    [frame_clause]
)

CTE Syntax

WITH cte_name AS (SELECT ...),
     cte_name2 AS (SELECT ...)
SELECT * FROM cte_name JOIN cte_name2 ...

Set Operations

SELECT ... UNION SELECT ...      -- Combine, remove duplicates
SELECT ... UNION ALL SELECT ...  -- Combine, keep all
SELECT ... INTERSECT SELECT ...  -- Common rows only
SELECT ... MINUS SELECT ...      -- Rows in first, not in second