When an Ellucian Spec API written for Oracle calls scalar functions directly from its SELECT list, migrating the query to PostgreSQL can break because those Oracle functions are not available in the PostgreSQL implementation. The SQL still needs to calculate the same value for each Banner record, so the function logic has to be reproduced in a form that can reference the current row. In the pattern covered here, PostgreSQL CROSS JOIN LATERAL provides that replacement by moving each scalar calculation into a correlated lateral subquery.

Each lateral subquery references e.pebempl_pidm from the current pebempl row and calculates the value that the corresponding Oracle function previously returned. The main SELECT then reads those results through aliases such as lcd.last_check_date and lpd.last_paid_date.

This article continues our Ellucian Data Connect tips and implementation patterns series with a Spec API migration pattern focused on preserving Oracle SQL behavior when the query is moved to PostgreSQL.

Why Oracle scalar function calls need to change

The Oracle version of the Spec API calls two scalar functions directly from the SELECT list:

SELECT
    e.pebempl_pidm,
    e.pebempl_ecls_code,
    f_get_last_check_date(e.pebempl_pidm) AS last_check_date,
    f_get_last_paid_date(e.pebempl_pidm)  AS last_paid_date
FROM pebempl e
WHERE e.pebempl_ecls_code IN ('::ecls::')

For the current pebempl row, each function receives e.pebempl_pidm and returns one calculated value. The query therefore produces the Banner employee data together with last_check_date and last_paid_date.

When this Spec API is migrated to PostgreSQL, those Oracle functions are not available in the PostgreSQL implementation. Removing the calls would also remove the values they calculate, so the migration has to reproduce the underlying calculation in PostgreSQL SQL.

The important requirement is to preserve the behavior of the calculation, not the Oracle function call itself.

Replace the functions with CROSS JOIN LATERAL

The PostgreSQL implementation moves each calculation into a lateral subquery:

SELECT
    e.pebempl_pidm,
    e.pebempl_ecls_code,
    lcd.last_check_date,
    lpd.last_paid_date
FROM pebempl e

CROSS JOIN LATERAL (
    SELECT MAX(phrhist_check_date) AS last_check_date
    FROM phrhist
    WHERE phrhist_pidm = e.pebempl_pidm
) lcd

CROSS JOIN LATERAL (
    SELECT MAX(phrhist_paid_date) AS last_paid_date
    FROM phrhist
    WHERE phrhist_pidm = e.pebempl_pidm
      AND phrhist_paid_amt > 0
) lpd

WHERE e.pebempl_ecls_code = '::ecls::'
  %%AND_FILTER%%
ORDER BY e.pebempl_pidm

The key part is the reference to the outer row:

phrhist_pidm = e.pebempl_pidm

LATERAL allows the subquery to use columns from a preceding FROM item. Here, each lateral calculation can therefore use the pidm from the relevant pebempl row.

The first lateral subquery calculates last_check_date and exposes the result through the lcd alias:

SELECT MAX(phrhist_check_date) AS last_check_date
FROM phrhist
WHERE phrhist_pidm = e.pebempl_pidm

The second reproduces the last paid date calculation and retains the additional phrhist_paid_amt > 0 condition:

SELECT MAX(phrhist_paid_date) AS last_paid_date
FROM phrhist
WHERE phrhist_pidm = e.pebempl_pidm
  AND phrhist_paid_amt > 0

The outer SELECT can then use:

lcd.last_check_date,
lpd.last_paid_date

For each relevant outer row, the lateral subqueries are evaluated using values from that row. This preserves the per row relationship that the original Oracle function calls provided without requiring those functions to exist in the PostgreSQL implementation.

Keep the replacement aligned with the original function logic

CROSS JOIN LATERAL does not automatically reproduce what an Oracle function did. The SQL inside each lateral subquery still has to represent the calculation that the corresponding function provided.

In this implementation, the first calculation finds the maximum phrhist_check_date for the current pidm. The second finds the maximum phrhist_paid_date for that same pidm, but only where phrhist_paid_amt > 0.

The migration is semantically equivalent to the original scalar calls in this implementation because the lateral subqueries reproduce the required calculations and provide one result row for each outer row.

Both subqueries use an aggregate without GROUP BY. If no matching phrhist rows exist, the aggregate query still produces one result row, with MAX(…) returning NULL. In this pattern, the CROSS JOIN LATERAL therefore does not remove the corresponding pebempl row simply because no matching history record exists.

The reusable principle is to translate the behavior of each source function into the lateral query rather than applying the same SQL mechanically to every Oracle function.

Keep Spec API filtering separate from the lateral logic

The PostgreSQL example also contains both:

'::ecls::'

and:

%%AND_FILTER%%

Those mechanisms handle Spec API parameterization and runtime criteria. They are separate from the lateral calculations used to replace the Oracle scalar functions.

For the distinction between static ::param:: substitution and runtime %%AND_FILTER%% criteria, see How to choose between ::param:: and %%AND_FILTER%% in Ellucian Spec API SQL.

Key implementation details

  • Replace an Oracle scalar function call only after identifying the calculation that its PostgreSQL replacement must preserve.
  • Use LATERAL when the replacement subquery needs a value such as e.pebempl_pidm from the current outer row.
  • Keep separate calculations explicit. In this implementation, last_check_date and last_paid_date use separate lateral subqueries.
  • Select the resulting lateral aliases, such as lcd.last_check_date and lpd.last_paid_date, from the outer query.
  • An aggregate such as MAX() without GROUP BY returns one aggregate result row even when no source rows match, with the aggregate value becoming NULL.
  • Keep Spec API parameter and criteria handling separate from the SQL used to reproduce the Oracle function logic.

Related Data Connect posts

Need help migrating Oracle based Ellucian Spec APIs to PostgreSQL?

ABCloudz can help analyze and migrate Ellucian Spec API SQL, preserve Banner query logic, replace Oracle specific calculations with PostgreSQL equivalents, and maintain existing integration behavior during database migration.

Need help modernizing your Spec API SQL?