Use Oracle SQL functions in IFS reporting queries

Explore this learning path

Documentation-based guide draft · Reviewed against sources on 2026-09-27 · Not executed in an IFS environment. IFS references use 26R1 unless stated otherwise. Confirm available APIs, components, and permissions in your installed release.

Build and verify an Oracle reporting query using a fixed three-row fixture before replacing it with an approved IFS view. The exercise demonstrates null handling, date formatting, division safety, and aggregation without creating any database objects.

Before you start

Use an authorized Oracle SQL worksheet. Start with the exact query below; it reads only DUAL. Do not test against a large production report until the calculation is correct and its access/performance have been reviewed.

1. Run the fixture query

WITH sample_lines (line_no, ordered_qty, delivered_qty, due_date) AS (
  SELECT 1, 10, 4, DATE '2026-09-20' FROM dual UNION ALL
  SELECT 2,  5, CAST(NULL AS NUMBER), DATE '2026-10-02' FROM dual UNION ALL
  SELECT 3,  0, 0, CAST(NULL AS DATE) FROM dual
)
SELECT line_no,
       ordered_qty - NVL(delivered_qty, 0) AS remaining_qty,
       ROUND(100 * NVL(delivered_qty, 0) / NULLIF(ordered_qty, 0), 1)
         AS delivered_percent,
       CASE
         WHEN due_date IS NULL THEN 'UNSCHEDULED'
         WHEN due_date < DATE '2026-09-27' THEN 'OVERDUE'
         ELSE 'OPEN'
       END AS due_state,
       TO_CHAR(due_date, 'YYYY-MM-DD') AS due_date_text
FROM sample_lines
ORDER BY line_no;

Expected values:

Line	Remaining	Delivered %	Due state	Date text
1	6	40	OVERDUE	2026-09-20
2	5	0	OPEN	2026-10-02
3	0	null	UNSCHEDULED	null

The percentage for a zero ordered quantity is intentionally unknown rather than an invented zero. NULLIF prevents division by zero; decide with the report owner whether a different display convention is appropriate.

2. Check the meaning of nulls

NVL(delivered_qty, 0) is correct only if a missing delivered quantity means none delivered in your data contract. If null means unknown or not loaded, turning it into zero misstates the business result.

Change line 2’s delivered quantity to 5 and rerun: remaining should become zero and percentage 100. Change it to 7: remaining becomes negative. Decide whether over-delivery should be shown or capped; do not silently hide it with GREATEST(...,0) without approval.

3. Aggregate at the intended grain

For a total percentage, divide total delivered quantity by total ordered quantity. Do not average line percentages when line sizes differ. Reuse the same CTE and replace its final SELECT with:

SELECT SUM(ordered_qty) AS ordered_total,
       SUM(NVL(delivered_qty, 0)) AS delivered_total,
       ROUND(100 * SUM(NVL(delivered_qty, 0)) /
             NULLIF(SUM(ordered_qty), 0), 1) AS delivered_percent
FROM sample_lines;

For the original fixture the totals are 15 ordered, 4 delivered, and 26.7 percent. Restore the original fixture after experimenting before checking this result.

4. Replace the fixture with an approved view

Identify a supported reporting view and its documented key/columns. Select only the fields needed for the calculation and apply company/site/date restrictions appropriate to the report. Compare the row count and unique business keys before and after adding joins.

A one-to-many join can multiply quantities before aggregation. Inspect a single business document with known lines and prove the join returns each intended line once. Correct the grain before changing SUM expressions.

5. Preserve date and number types until presentation

Keep dates as dates for filters and numbers as numbers for sorting and totals. For a one-day date range, use a half-open interval:

WHERE due_date >= :start_date
  AND due_date < :start_date + 1

Bind :start_date as the agreed day boundary, not a locale-dependent date string. If the real column includes time-zone semantics, convert the boundary deliberately before applying the filter. Formatting a date with TO_CHAR is a presentation operation, not a reliable chronological comparison strategy.

6. Diagnose correctness and performance separately

Symptom	Check
ORA-01476	A denominator lacks an intentional zero-handling rule
Invalid number	Implicit conversion or text stored where a numeric value is expected
Totals too large	Join duplication and grouping grain
Same-date rows missing	Time component and interval boundaries
Slow query after adding a function	Execution plan and per-row function cost

Use a small representative selection first, then review the plan with the database/reporting owner. Do not add a custom PL/SQL function around every row merely to shorten the SELECT. Save the query, expected fixture results, and actual environment results together. No cleanup is required for the CTE-only exercise.

Related development guides

Use SQL correctness and performance for nulls, ties, UDF and deterministic declarations, and a complete pipelined-function exercise.

Sources and scope

• Oracle SELECT and WITH declarations

Older IFS Applications examples are historical context, not evidence of Cloud compatibility. No third-party code, screenshots, or downloadable workflows are reproduced.