When an Ellucian Spec API SQL query needs parameter values or criteria based filtering, choosing between ::param:: and %%AND_FILTER%% determines what kind of SQL substitution is required. ::param:: supplies a value at a predefined position in the SQL, while %%AND_FILTER%% represents an optional SQL condition generated from the request criteria. Using the wrong mechanism can cause the query to apply the wrong filtering behavior or fail to apply the intended criteria based condition.

The practical distinction is straightforward. Use ::param:: when the query expects a value at a fixed position in the SQL, such as a scenario value or another predefined parameter. Use %%AND_FILTER%% when the SQL needs an optional, user facing filter generated from request criteria.

This article is part of our Ellucian Data Connect tips and implementation patterns series. This entry focuses on Spec API SQL parameterization and the difference between value substitution and criteria based filtering.

Why `::param::` and `%%AND_FILTER%%` are not interchangeable

The two placeholders may look similar because both eventually affect the SQL sent for execution, but they serve different purposes.

MechanismHow it is usedIntended role
::param::Supplies a value at a predefined SQL positionFixed parameter value
%%AND_FILTER%%Supplies a condition generated from request criteriaOptional criteria filtering

The choice therefore depends on what the query needs.

If the SQL already defines where a particular value belongs, use ::param::.

If an additional condition should be generated from request, use %%AND_FILTER%%.

Confusing the two changes more than the placeholder syntax. One supplies a value at a predefined SQL position, while the other represents a criteria based SQL condition. Because this can surface as incorrect filtering rather than an obvious execution error, the placeholder choice can be easy to misdiagnose.

Use `::param::` for fixed SQL values

A ::param:: placeholder supplies a parameter value at a predefined position in the SQL.

The source implementation uses parameters such as ::scenario:: and ::bannerId:::

WHERE sgbstdn_stst_code = 'AS'
  AND 'SCENARIO_1' = NVL('::scenario::', 'SCENARIO_1')
  AND spriden_id = CASE
        WHEN '::bannerId::' IS NULL THEN spriden_id
        ELSE '::bannerId::'
      END

Here, the SQL already defines the exact expressions in which the parameter values belong. The placeholder supplies the value at that predefined location.

This makes ::param:: appropriate for values that occupy predefined positions in the query structure itself. It is not a criteria based filter and should not be treated as an alternative syntax for %%AND_FILTER%%.

Use `%%AND_FILTER%%` for criteria based filtering

%%AND_FILTER%% serves a different purpose. It represents an SQL condition generated from the request criteria.

A Spec API query can provide a stable location for that dynamic condition like this:

WHERE 1=1
  %%AND_FILTER%%
ORDER BY sgbstdn_surrogate_id

If the request contains criteria that compile to a condition such as:

AND sgbstdn_pidm = 12345

the resulting SQL is conceptually:

WHERE 1=1
  AND sgbstdn_pidm = 12345
ORDER BY sgbstdn_surrogate_id

If no criteria are supplied, %%AND_FILTER%% becomes an empty string. The query remains:

WHERE 1=1
ORDER BY sgbstdn_surrogate_id

This is why WHERE 1=1 works well with the placeholder. The base WHERE clause remains valid whether a criteria based condition is inserted or not.

The important distinction is that %%AND_FILTER%% represents an SQL condition generated from request criteria. It is not a placeholder for one predefined parameter value.

Choose the mechanism by the role of the filter

A useful decision rule is to look at where the filtering decision originates.

Use ::param:: when the SQL already knows where a specific value belongs and that value should be substituted directly into the existing expression.

Use %%AND_FILTER%% when the additional SQL condition should come from request criteria and may be absent entirely when no criteria are provided.

The two mechanisms can therefore appear in the same Spec API implementation, provided each one is used for its intended role. Choosing between them based only on their appearance as placeholders can lead to incorrect query behavior.

Key implementation details

  • ::param:: supplies a parameter value at a predefined position in the SQL.
  • Use ::param:: for values that occupy predefined positions in the SQL.
  • %%AND_FILTER%% represents an SQL condition generated from request criteria.
  • Use %%AND_FILTER%% for optional, user facing criteria filters.
  • When no criteria are provided, %%AND_FILTER%% becomes an empty string.
  • WHERE 1=1 provides a valid base clause whether the dynamic filter is present or absent.
  • Do not treat ::param:: and %%AND_FILTER%% as interchangeable parameterization mechanisms.

Related Data Connect posts

Need help with Ellucian Spec API SQL?

ABCloudz can help design and troubleshoot Ellucian Spec API queries, including SQL parameterization, criteria based filtering, Banner integration logic, and Oracle or PostgreSQL Spec API implementations.

Need help with Spec API filtering?