Calculated Fields and User Views

A calculated field (also called a computed or derived column) is produced from other columns or expressions at query time. The cleanest way to expose these results to end users is through a user view—a named SELECT that centralizes formulas and presents a task-focused result set.

• Single Source of Truth: Put business formulas (totals, discounts, statuses) in one place so every report/app uses the same logic.
• No Redundant Storage: Values are computed on demand rather than stored and maintained in multiple tables.
• Simpler UX & Security: Views expose only the columns users need, hiding joins and sensitive base attributes.

Arithmetic (e.g., extended price):

CREATE OR REPLACE VIEW order_lines_v AS
SELECT ol.order_id,
       ol.product_id,
       ol.qty * ol.unit_price AS line_total
FROM   order_lines ol;

Conditional Labels (e.g., status buckets):

CREATE OR REPLACE VIEW invoice_status_v AS
SELECT i.invoice_id,
       CASE
         WHEN i.paid_at IS NOT NULL THEN 'PAID'
         WHEN CURRENT_DATE > i.due_date THEN 'OVERDUE'
         ELSE 'OPEN'
       END AS status_label
FROM   invoices i;

Aggregates (e.g., order summary):

CREATE OR REPLACE VIEW order_summary_v AS
SELECT o.order_id,
       SUM(oi.qty * oi.unit_price) AS order_total
FROM   orders o
JOIN   order_items oi ON oi.order_id = o.order_id
GROUP  BY o.order_id;

• Oracle 19c/23ai: WITH CHECK OPTION to enforce predicates on DML; CREATE FORCE VIEW for forward-declared views; materialized views for cached results; virtual (generated) columns; function-based indexes.
• SQL Server: Indexed views (materialized), persisted computed columns (indexable), WITH SCHEMABINDING for stable definitions.
• PostgreSQL: Materialized views with REFRESH MATERIALIZED VIEW; generated columns; powerful expression indexes for view predicates; views updatable in many cases.
• MySQL: Views (non-materialized); generated columns (VIRTUAL/STORED, indexable when STORED); no native materialized views—use ETL or triggers if caching is required.

• Generated/Virtual Columns (table level): If a calculation is intrinsic to a single row, define a generated/computed column. Many platforms allow indexing or persistence for performance.
• Materialized / Indexed Views: For expensive cross-table computations or reporting snapshots, cache results (Oracle materialized views, SQL Server indexed views, PostgreSQL materialized views).
• ETL Pipelines: For heavyweight transforms or historical snapshots, load to reporting tables on a schedule.

• Favor sargable expressions so the optimizer can use indexes.
• Index base columns or create expression/functional indexes where supported.
• Avoid non-deterministic functions in filtering predicates used frequently.

Calculated fields belong in views when you want centralized, reusable logic without storing redundant data. Use generated columns for row-local formulas, and materialized/indexed views for heavy, cross-table computations. The right choice keeps data accurate, queries fast, and application code simple.

Check your understanding of calculated fields and when to use views:
Calculated Fields – Exercise

Calculated field: A value derived from other columns or expressions at query time.

Virtual table (view): A stored SELECT definition that returns rows from base tables; only the definition is stored, not the result.